SQL & PL/SQL

What is the fastest way to fetch DISTINCT values from partitioned table?
1) DISTINCT/UNIQUE
2) GROUP BY
3) PARTITION BY OVER()
4) MAX(ROWID)

Table Definition
CREATE TABLE STG_SOS_SALES_FACT_STUDY
(
  CNTRY_KEY     NUMBER,
  STUDY_ID      NUMBER,
  PRD_KEY_YEAR  NUMBER,
  PRD_KEY_WEEK  NUMBER,
  DATE_FROM     DATE,
  DATE_TO       DATE,
  STR_KEY       NUMBER,
  STR_SRVC      NUMBER,
  CTG_ID        NUMBER,
  PRDC_KEY      NUMBER,
  UNITS         NUMBER,
  DOL           NUMBER(17,2),
  FL_ID         NUMBER
)

-> PARTITION BY RANGE (PRD_KEY_YEAR, PRD_KEY_WEEK)
-> SUBPARTITION BY LIST (CNTRY_KEY)

** Local Partition Indexes
1) CN_SD_CTG_PRD_PRDC_IDX = STG_SOS_SALES_FACT_STUDY (PRD_KEY_YEAR, PRD_KEY_WEEK, CNTRY_KEY, STUDY_ID, CTG_ID, PRDC_KEY)
2) CN_SD_PRD_STR_CTG_IDX = STG_SOS_SALES_FACT_STUDY (PRD_KEY_YEAR, PRD_KEY_WEEK, CNTRY_KEY, STUDY_ID, STR_KEY)

#Query:
SELECT DISTINCT PRD_KEY_WEEK, PRD_KEY_YEAR
FROM stg_sos_sales_fact_study a
WHERE CNTRY_KEY = 484
AND ( ( (A.PRD_KEY_WEEK BETWEEN 14 AND 52 AND A.PRD_KEY_YEAR = 2012)
OR (A.PRD_KEY_WEEK BETWEEN 1 AND 14 AND A.PRD_KEY_YEAR = 2013)))
AND STUDY_ID IN (22573064, 35328585, 22573064);

** Explain Plan:
Plan
SELECT STATEMENT  ALL_ROWSCost: 6,235  Bytes: 629  Cardinality: 37  					
      8 HASH UNIQUE  Cost: 6,235  Bytes: 629  Cardinality: 37  				
         7 CONCATENATION  			
              3 PARTITION RANGE ITERATOR  Cost: 1,985  Bytes: 1,031,900  Cardinality: 60,700  Partition #: 3  Partitions accessed #194 - #207		
                  2 PARTITION LIST SINGLE  Cost: 1,985  Bytes: 1,031,900  Cardinality: 60,700  Partition #: 4  Partitions determined by Key Values	
	  1 INDEX SKIP SCAN INDEX SOS_ODS.CN_SD_PRD_STR_CTG_IDX Cost: 1,985  Bytes: 1,031,900  Cardinality: 60,700  Partition #: 4  Partitions determined by Key Values
	      6 PARTITION RANGE ITERATOR  Cost: 4,243  Bytes: 2,719,371  Cardinality: 159,963  Partition #: 6  Partitions accessed #155 - #193		
	        5 PARTITION LIST SINGLE  Cost: 4,243  Bytes: 2,719,371  Cardinality: 159,963  Partition #: 7  Partitions determined by Key Values	
	         4 INDEX SKIP SCAN INDEX SOS_ODS.CN_SD_PRD_STR_CTG_IDX Cost: 4,243  Bytes: 2,719,371  Cardinality: 159,963  Partition #: 7  Partitions determined by Key Values

The above query is taking around 6-7 minutes to fetch the data.

Edited by: meet_sanc on 12 May, 2013 11:34 AM