Quote Originally Posted by LKBrwn_DBA View Post
Perhaps if you could find a column (indexed) where you can join the table AKHIL_TEST2 with one of the other two tables, you would avoid the "| 7 | MERGE JOIN CARTESIAN . . ." from the plan(s) and get better performance.
MERGE JOIN CARTESIAN was unavoidable. However, I was able to bring down the CPU cost further to approx. 3200 by creating a composite function based BITMAP index on columns (EXTRACT(YEAR FROM COACH_LAST_EVENT_DT), EXTRACT(MONTH FROM COACH_LAST_EVENT_DT)). Afterwards, I flushed all the buffers in buffer cache to find the worst case execution time of query - the execution time of query is now 16 seconds which is okay, but I hope that I could reduce it further!!!!

I am not able to attach new execution plan, so I am pasting it here:

SQL> l
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')

Elapsed: 00:00:14.91

Execution Plan
----------------------------------------------------------
Plan hash value: 755460096

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 2652 | 3241 (1)| 00:00:39 |
| 1 | HASH UNIQUE | | 34 | 2652 | 3241 (1)| 00:00:39 |
|* 2 | HASH JOIN | | 34 | 2652 | 3240 (1)| 00:00:39 |
|* 3 | TABLE ACCESS BY INDEX ROWID | AKHIL_TEST1 | 8855 | 371K| 2159 (1)| 00:00:26 |
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 5 | BITMAP AND | | | | | |
|* 6 | BITMAP INDEX SINGLE VALUE | BITMAP_FN1_COACH_LAST_EVENT_DT | | | | |
| 7 | BITMAP OR | | | | | |
|* 8 | BITMAP INDEX SINGLE VALUE| BITMAP_COACH_STATUS_FLAG | | | | |
|* 9 | BITMAP INDEX SINGLE VALUE| BITMAP_COACH_STATUS_FLAG | | | | |
|* 10 | BITMAP INDEX SINGLE VALUE| BITMAP_COACH_STATUS_FLAG | | | | |
|* 11 | BITMAP INDEX SINGLE VALUE| BITMAP_COACH_STATUS_FLAG | | | | |
|* 12 | BITMAP INDEX SINGLE VALUE| BITMAP_COACH_STATUS_FLAG | | | | |
| 13 | MERGE JOIN CARTESIAN | | 17130 | 585K| 1080 (1)| 00:00:13 |
| 14 | TABLE ACCESS BY INDEX ROWID | AKHIL_TEST3 | 1 | 17 | 2 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | IDX_DEPOT_DIV_ZONE | 1 | | 1 (0)| 00:00:01 |
| 16 | BUFFER SORT | | 19159 | 336K| 1078 (1)| 00:00:13 |
|* 17 | TABLE ACCESS FULL | AKHIL_TEST2 | 19159 | 336K| 1078 (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')
6 - access(EXTRACT(YEAR FROM INTERNAL_FUNCTION("COACH_LAST_EVENT_DT"))=2012 AND EXTRACT(MONTH FROM
INTERNAL_FUNCTION("COACH_LAST_EVENT_DT"))=9)
8 - access("A"."COACH_STATUS_FLAG"='INSHOP')
9 - access("A"."COACH_STATUS_FLAG"='SHONRK')
10 - access("A"."COACH_STATUS_FLAG"='SHONRN')
11 - access("A"."COACH_STATUS_FLAG"='SHOPCH')
12 - access("A"."COACH_STATUS_FLAG"='WTSHOP')
15 - access("C"."RLY_DEPOT_CD"='BCT' AND "C"."RLY_DIV_CD"='BCT' AND "C"."RLY_ZONE_CD"='WR')
17 - filter("B"."COACH_TYP_CD"='GS')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17524 consistent gets
17481 physical reads
0 redo size
305 bytes sent via SQL*Net to client
251 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed