Hi
I have a query which performs poorly when using a index like this
Code:
explain plan for
CREATE TABLE HRISHY PARALLEL 8 NOLOGGING AS
SELECT
COUNT(POS_TXN_MSTR_CUR.VISIT_NBR) a,XTRA_CARD.XTRA_CARD_NBR
FROM COMMON.V_POS_TXN_MSTR_CUR POS_TXN_MSTR_CUR, XTRA.XTRA_CARD XTRA_CARD, PVANTAGE.SE80 SE80
WHERE ( ( POS_TXN_MSTR_CUR.DATE_DT BETWEEN TO_DATE('01/16/2006','MM/DD/YYYY')
AND TO_DATE('04/16/2006','MM/DD/YYYY')
AND SE80.A2 = '0' AND SE80.A3 = 9
AND POS_TXN_MSTR_CUR.VISIT_NBR IN ( 1,1001,2001,3001,4001 ) )
AND POS_TXN_MSTR_CUR.XTRA_CARD_NBR = XTRA_CARD.XTRA_CARD_NBR
AND SE80.A1 = XTRA_CARD.XTRA_CARD_NBR )
and btch_cntl_nbr in
(Select btch_cntl_nbr from campaign_batch_control
where cmpgn_id = 988 and dv_id = 722 and proc_end_dt IS NULL
AND NVL(hold_ind,'N') ='Y')
GROUP BY XTRA_CARD.XTRA_CARD_NBR
/
Plan is like
Code:
-----------------------------------------------------------------------------------------------------------
------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart|
Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------
------------------------------------
| 0 | CREATE TABLE STATEMENT | | 1 | 59 | 23 | | |
| | |
| 1 | LOAD AS SELECT | | | | | | |
12,02 | P->S | QC (RAND) |
| 2 | SORT GROUP BY | | 1 | 59 | 23 | | |
12,02 | PCWP | |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| POS_TXN_MSTR | 1 | 23 | 5 | | |
12,02 | PCWC | |
| 4 | NESTED LOOPS | | 1 | 59 | 20 | | |
12,01 | P->P | HASH |
| 5 | NESTED LOOPS | | 1 | 36 | 18 | | |
12,01 | PCWP | |
| 6 | MERGE JOIN CARTESIAN | | 1 | 30 | 17 | | |
12,01 | PCWP | |
| 7 | SORT UNIQUE | | | | | | |
12,00 | S->P | BROADCAST |
|* 8 | TABLE ACCESS BY INDEX ROWID | CAMPAIGN_BATCH_CONTROL | 1 | 22 | 3 | | |
| | |
|* 9 | INDEX RANGE SCAN | CAMPAIGN_BATCH_CONTROL_PK | 1 | | 2 | | |
| | |
| 10 | BUFFER SORT | | 99810 | 779K| 14 | | |
12,01 | PCWP | |
|* 11 | TABLE ACCESS FULL | SE80 | 99810 | 779K| 9 | | |
12,01 | PCWP | |
|* 12 | INDEX UNIQUE SCAN | XTRA_CARD_PK | 1 | 6 | 1 | | |
12,01 | PCWP | |
| 13 | PARTITION RANGE ITERATOR | | | | | 24 |
25 | 12,01 | PCWP | |
|* 14 | INDEX RANGE SCAN | PTM_DT_STR_CRD_VST_PK | 1 | | 4 | 24 |
25 | 12,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------
------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("POS_TXN_MSTR"."BTCH_CNTL_NBR"="CAMPAIGN_BATCH_CONTROL"."BTCH_CNTL_NBR")
8 - filter("CAMPAIGN_BATCH_CONTROL"."PROC_END_DT" IS NULL AND NVL("CAMPAIGN_BATCH_CONTROL"."HOLD_IND",'N
')='Y')
9 - access("CAMPAIGN_BATCH_CONTROL"."CMPGN_ID"=988 AND "CAMPAIGN_BATCH_CONTROL"."DV_ID"=722)
11 - filter("SE80"."A2"='0' AND "SE80"."A3"=9)
12 - access("SE80"."A1"="XTRA_CARD"."XTRA_CARD_NBR")
14 - access("POS_TXN_MSTR"."DATE_DT">=TO_DATE('2006-01-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"POS_TXN_MSTR"."XTRA_CARD_NBR"="XTRA_CARD"."XTRA_CARD_NBR" AND "POS_TXN_MSTR"."DATE_DT"<=TO_D
ATE('2006-04-16 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))
filter(("POS_TXN_MSTR"."VISIT_NBR"=1 OR "POS_TXN_MSTR"."VISIT_NBR"=1001 OR "POS_TXN_MSTR"."VISIT_NBR
"=2001 OR
"POS_TXN_MSTR"."VISIT_NBR"=3001 OR "POS_TXN_MSTR"."VISIT_NBR"=4001) AND "POS_TXN_MSTR"."XTRA_
CARD_NBR"="XTRA_CARD"."XTRA_CARD_NBR")
Note: cpu costing is off
36 rows selected.
However if i use a full table hint like this the performance is much better and it completes in about 15 minutes
Code:
explain plan for
CREATE TABLE HRISHY PARALLEL 8 NOLOGGING AS
SELECT /*+ FULL (POS_TXN_MSTR_CUR ) */
COUNT(POS_TXN_MSTR_CUR.VISIT_NBR) a,XTRA_CARD.XTRA_CARD_NBR
FROM COMMON.V_POS_TXN_MSTR_CUR POS_TXN_MSTR_CUR, XTRA.XTRA_CARD XTRA_CARD, PVANTAGE.SE80 SE80
WHERE ( ( POS_TXN_MSTR_CUR.DATE_DT BETWEEN TO_DATE('01/16/2006','MM/DD/YYYY')
AND TO_DATE('04/16/2006','MM/DD/YYYY')
AND SE80.A2 = '0' AND SE80.A3 = 9
AND POS_TXN_MSTR_CUR.VISIT_NBR IN ( 1,1001,2001,3001,4001 ) )
AND POS_TXN_MSTR_CUR.XTRA_CARD_NBR = XTRA_CARD.XTRA_CARD_NBR
AND SE80.A1 = XTRA_CARD.XTRA_CARD_NBR )
and btch_cntl_nbr in
(Select btch_cntl_nbr from campaign_batch_control
where cmpgn_id = 988 and dv_id = 722 and proc_end_dt IS NULL
AND NVL(hold_ind,'N') ='Y')
GROUP BY XTRA_CARD.XTRA_CARD_NBR
/
and the plan changes to
Code:
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
---------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pst
op | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------
---------------------------------
| 0 | CREATE TABLE STATEMENT | | 1 | 59 | 2523 | |
| | | |
| 1 | LOAD AS SELECT | | | | | |
| 79,02 | P->S | QC (RAND) |
| 2 | SORT GROUP BY | | 1 | 59 | 2523 | |
| 79,02 | PCWP | |
| 3 | NESTED LOOPS | | 1 | 59 | 2519 | |
| 79,01 | P->P | HASH |
| 4 | NESTED LOOPS | | 1 | 51 | 2518 | |
| 79,01 | PCWP | |
|* 5 | HASH JOIN SEMI | | 1 | 45 | 2517 | |
| 79,01 | PCWP | |
| 6 | PARTITION RANGE ITERATOR | | | | | 24 |
25 | 79,01 | PCWP | |
|* 7 | TABLE ACCESS FULL | POS_TXN_MSTR | 51 | 1173 | 2514 | 24 |
25 | 79,01 | PCWP | |
|* 8 | TABLE ACCESS BY INDEX ROWID| CAMPAIGN_BATCH_CONTROL | 1 | 22 | 3 | |
| 79,00 | S->P | BROADCAST |
|* 9 | INDEX RANGE SCAN | CAMPAIGN_BATCH_CONTROL_PK | 30710 | | 2 | |
| | | |
|* 10 | INDEX UNIQUE SCAN | XTRA_CARD_PK | 1 | 6 | 1 | |
| 79,01 | PCWP | |
|* 11 | TABLE ACCESS BY INDEX ROWID | SE80 | 1 | 8 | 1 | |
| 79,01 | PCWP | |
|* 12 | INDEX UNIQUE SCAN | SE80I1_U | 10 | | | |
| 79,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------
---------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("POS_TXN_MSTR"."BTCH_CNTL_NBR"="CAMPAIGN_BATCH_CONTROL"."BTCH_CNTL_NBR")
7 - filter("POS_TXN_MSTR"."DATE_DT">=TO_DATE('2006-01-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"POS_TXN_MSTR"."DATE_DT"<=TO_DATE('2006-04-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND ("POS_T
XN_MSTR"."VISIT_NBR"=1 OR
"POS_TXN_MSTR"."VISIT_NBR"=1001 OR "POS_TXN_MSTR"."VISIT_NBR"=2001 OR "POS_TXN_MSTR"."VISIT_N
BR"=3001 OR "POS_TXN_MSTR"."VISIT_NBR"=4001))
8 - filter("CAMPAIGN_BATCH_CONTROL"."PROC_END_DT" IS NULL AND NVL("CAMPAIGN_BATCH_CONTROL"."HOLD_IND",'N
')='Y')
9 - access("CAMPAIGN_BATCH_CONTROL"."CMPGN_ID"=988 AND "CAMPAIGN_BATCH_CONTROL"."DV_ID"=722)
10 - access("POS_TXN_MSTR"."XTRA_CARD_NBR"="XTRA_CARD"."XTRA_CARD_NBR")
11 - filter("SE80"."A2"='0' AND "SE80"."A3"=9)
12 - access("SE80"."A1"="XTRA_CARD"."XTRA_CARD_NBR")
My question why should i hint.
Cant oracle figure it out ?
I have uploaded the 10053 trace also