DBAsupport.com Forums - Powered by vBulletin
Results 1 to 1 of 1

Thread: full table scan hint required

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828

    full table scan hint required

    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width