SQL> ed Wrote file afiedt.buf 1 SELECT DISTINCT(B.COACH_NUM) FROM AKHIL_TEST1 A,AKHIL_TEST2 B,AKHIL_TEST3 C 2 WHERE A.COACH_ID = B.COACH_ID 3 AND A.COACH_CURR_LOCATION_ID = C.LOCATION_ID 4 AND C.RLY_ZONE_CD = 'WR' 5 AND C.RLY_DIV_CD = 'BCT' 6 AND C.RLY_DEPOT_CD = 'BCT' 7 AND EXTRACT(MONTH FROM A.COACH_LAST_EVENT_DT) = 9 8 AND EXTRACT(YEAR FROM A.COACH_LAST_EVENT_DT) = 2012 9 AND B.COACH_TYP_CD = 'GS' 10* AND A.COACH_STATUS_FLAG IN ('SHOPCH','SHONRK','WTSHOP','INSHOP','SHONRN') SQL> SQL> SQL> SQL> / Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 736967852 ------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 60 | 93245 (1)| 00:18:39 | | 1 | HASH UNIQUE | | 1 | 60 | 93245 (1)| 00:18:39 | |* 2 | HASH JOIN | | 1 | 60 | 93244 (1)| 00:18:39 | | 3 | INLIST ITERATOR | | | | | | |* 4 | TABLE ACCESS BY INDEX ROWID | AKHIL_TEST1 | 44 | 1100 | 92164 (1)| 00:18:26 | | 5 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 6 | BITMAP INDEX SINGLE VALUE | BITMAP_COACH_STATUS_FLAG | | | | | | 7 | MERGE JOIN CARTESIAN | | 17730 | 606K| 1079 (1)| 00:00:13 | | 8 | TABLE ACCESS BY INDEX ROWID | AKHIL_TEST3 | 1 | 17 | 2 (0)| 00:00:01 | |* 9 | INDEX RANGE SCAN | IDX_DEPOT_DIV_ZONE | 1 | | 1 (0)| 00:00:01 | | 10 | BUFFER SORT | | 19830 | 348K| 1077 (1)| 00:00:13 | |* 11 | TABLE ACCESS FULL | AKHIL_TEST2 | 19830 | 348K| 1077 (1)| 00:00:13 | ------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."COACH_ID"="B"."COACH_ID" AND "A"."COACH_CURR_LOCATION_ID"="C"."LOCATION_ID") 4 - filter(EXTRACT(MONTH FROM INTERNAL_FUNCTION("A"."COACH_LAST_EVENT_DT"))=9 AND EXTRACT(YEAR FROM INTERNAL_FUNCTION("A"."COACH_LAST_EVENT_DT"))=2012) 6 - access("A"."COACH_STATUS_FLAG"='INSHOP' OR "A"."COACH_STATUS_FLAG"='SHONRK' OR "A"."COACH_STATUS_FLAG"='SHONRN' OR "A"."COACH_STATUS_FLAG"='SHOPCH' OR "A"."COACH_STATUS_FLAG"='WTSHOP') 9 - access("C"."RLY_DEPOT_CD"='BCT' AND "C"."RLY_DIV_CD"='BCT' AND "C"."RLY_ZONE_CD"='WR') 11 - filter("B"."COACH_TYP_CD"='GS') --------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL> ED Wrote file afiedt.buf 1 SELECT /*+ NO_INDEX (A BITMAP_COACH_STATUS_FLAG) */ 2 DISTINCT(B.COACH_NUM) FROM AKHIL_TEST1 A,AKHIL_TEST2 B,AKHIL_TEST3 C 3 WHERE A.COACH_ID = B.COACH_ID 4 AND A.COACH_CURR_LOCATION_ID = C.LOCATION_ID 5 AND C.RLY_ZONE_CD = 'WR' 6 AND C.RLY_DIV_CD = 'BCT' 7 AND C.RLY_DEPOT_CD = 'BCT' 8 AND EXTRACT(MONTH FROM A.COACH_LAST_EVENT_DT) = 9 9 AND EXTRACT(YEAR FROM A.COACH_LAST_EVENT_DT) = 2012 10 AND B.COACH_TYP_CD = 'GS' 11* AND A.COACH_STATUS_FLAG IN ('SHOPCH','SHONRK','WTSHOP','INSHOP','SHONRN') SQL> SQL> SQL> / Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1845042423 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 60 | 383K (2)| 01:16:42 | | 1 | HASH UNIQUE | | 1 | 60 | 383K (2)| 01:16:42 | |* 2 | HASH JOIN | | 1 | 60 | 383K (2)| 01:16:42 | |* 3 | TABLE ACCESS FULL | AKHIL_TEST1 | 44 | 1100 | 382K (2)| 01:16:29 | | 4 | MERGE JOIN CARTESIAN | | 17730 | 606K| 1079 (1)| 00:00:13 | | 5 | TABLE ACCESS BY INDEX ROWID| AKHIL_TEST3 | 1 | 17 | 2 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | IDX_DEPOT_DIV_ZONE | 1 | | 1 (0)| 00:00:01 | | 7 | BUFFER SORT | | 19830 | 348K| 1077 (1)| 00:00:13 | |* 8 | TABLE ACCESS FULL | AKHIL_TEST2 | 19830 | 348K| 1077 (1)| 00:00:13 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."COACH_ID"="B"."COACH_ID" AND "A"."COACH_CURR_LOCATION_ID"="C"."LOCATION_ID") 3 - filter(("A"."COACH_STATUS_FLAG"='INSHOP' OR "A"."COACH_STATUS_FLAG"='SHONRK' OR "A"."COACH_STATUS_FLAG"='SHONRN' OR "A"."COACH_STATUS_FLAG"='SHOPCH' OR "A"."COACH_STATUS_FLAG"='WTSHOP') AND EXTRACT(MONTH FROM INTERNAL_FUNCTION("A"."COACH_LAST_EVENT_DT"))=9 AND EXTRACT(YEAR FROM INTERNAL_FUNCTION("A"."COACH_LAST_EVENT_DT"))=2012) 6 - access("C"."RLY_DEPOT_CD"='BCT' AND "C"."RLY_DIV_CD"='BCT' AND "C"."RLY_ZONE_CD"='WR') 8 - filter("B"."COACH_TYP_CD"='GS')