Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Unanswered: Query not using indexes

    Hi,

    I have one query which is making full table scan for all table's.
    All tables were last analyzed on 21-feb-04.

    TableName TotalNumberOfRows Rows after 21-feb-04
    -------------------------------------------------------
    RECEIPT_LINE 2540357 69030
    SUPPLIER_MASTER 147533 389
    ITEM_MASTER 2045029 14261
    FISCAL_WEEK 473
    DB_MASTER 58

    Why the query is not using indexes? Because the table were analyzed long time back?
    How often we should analyzed tables and indexes ?
    Query, Explain plan and index details are attached.

    Thanks,
    Attached Files Attached Files
    • File Type: txt t.txt (2.1 KB, 80 views)
    Pagnint
    (No need to search web before posting new question)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Query not using indexes

    Unfortunately that attachment is garbled and unreadable. Perhaps you could just cut and paste the information into another message - between PHP tags to get good formatting. Also I can't see the SQL that you are actually running.

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Sorry for that ! but for me it is opening.
    Hope this file opens.



    Explain Plan:

    Plan Table
    --------------------------------------------------------------------------------
    | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
    --------------------------------------------------------------------------------
    | SELECT STATEMENT | | 127K| 18M| 16156 | | |
    | SORT GROUP BY | | 127K| 18M| 16156 | | |
    | HASH JOIN | | 127K| 18M| 13321 | | |
    | TABLE ACCESS FULL |FISCAL_WE | 473 | 4K| 2 | | |
    | HASH JOIN | | 127K| 16M| 13314 | | |
    | TABLE ACCESS FULL |DB_MASTER | 58 | 1K| 2 | | |
    | HASH JOIN | | 127K| 14M| 13309 | | |
    | TABLE ACCESS FULL |SUPPLIER_ | 147K| 3M| 282 | | |
    | HASH JOIN | | 126K| 10M| 12741 | | |

    Plan Table
    --------------------------------------------------------------------------------
    | TABLE ACCESS FULL |RECEIPT_L | 123K| 8M| 6756 | | |
    | TABLE ACCESS FULL |ITEM_MAST | 2M| 40M| 4813 | | |
    --------------------------------------------------------------------------------


    Indexes

    RECEIPT_LINE
    INDEX_NAME COLUMN_NAME
    ------------------------------------
    I_RECEIPT_DB_SITE DB_CODE
    I_RECEIPT_DB_SITE SITE_CODE
    I_RECEIPT_ITEM ITEM_ID
    I_RECEIPT_RCPT_NB RC_NUMBER
    I_RECEIPT_SUPP SUPP_ID
    I_RECEIPT_WEEK FISCAL_WEEK
    PK_RECEIPT_LINE PO_NUMBER
    PK_RECEIPT_LINE PO_LINE
    PK_RECEIPT_LINE RC_NUMBER
    PK_RECEIPT_LINE DB_CODE

    SUPPLIER_MASTER
    INDEX_NAME COLUMN_NAME
    ------------------------------------
    IU_SUPP_CODE SUPP_CODE
    IU_SUPP_CODE DB_CODE
    PK_SUPPLIER_MASTER SUPP_ID

    ITEM_MASTER
    INDEX_NAME COLUMN_NAME
    ------------------------------------
    IU_ITEM_NUMBER ITEM_NUMBER
    IU_ITEM_NUMBER DB_CODE
    I_ITEM_DESC1 ITEM_DESC1
    I_ITEM_HPL HPL_CODE
    I_ITEM_PL HPL_CODE
    I_ITEM_SBG_CODE SBG_CODE
    PK_ITEM_MASTER ITEM_ID
    Attached Files Attached Files
    Pagnint
    (No need to search web before posting new question)

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    And the SQL statement?

  5. #5
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    SELECT
    RECEIPT_LINE.RC_DATE,
    RECEIPT_LINE.UOM,
    Sum(RECEIPT_LINE.QTY_RC*RECEIPT_LINE.UOM_CONV),
    RECEIPT_LINE.RC_NUMBER,
    ITEM_MASTER.ITEM_NUMBER,
    DB_MASTER.DB_NAME,
    FISCAL_WEEK.FISCAL_MONTH,
    SUPPLIER_MASTER.SUPP_NAME,
    SUPPLIER_MASTER.TRD_INTCO
    FROM
    datap.RECEIPT_LINE,
    datap.ITEM_MASTER,
    datap.DB_MASTER,
    datap.FISCAL_WEEK,
    datap.SUPPLIER_MASTER
    WHERE
    ( SUPPLIER_MASTER.SUPP_ID=RECEIPT_LINE.SUPP_ID )
    AND ( ITEM_MASTER.ITEM_ID=RECEIPT_LINE.ITEM_ID )
    AND ( FISCAL_WEEK.FISCAL_WEEK=RECEIPT_LINE.FISCAL_WEEK )
    AND ( DB_MASTER.DB_CODE=RECEIPT_LINE.DB_CODE )
    AND (
    RECEIPT_LINE.RC_DATE > '31-DEC-02'
    )
    GROUP BY
    RECEIPT_LINE.RC_DATE,
    RECEIPT_LINE.UOM,
    RECEIPT_LINE.RC_NUMBER,
    ITEM_MASTER.ITEM_NUMBER,
    DB_MASTER.DB_NAME,
    FISCAL_WEEK.FISCAL_MONTH,
    SUPPLIER_MASTER.SUPP_NAME,
    SUPPLIER_MASTER.TRD_INTCO;
    Pagnint
    (No need to search web before posting new question)

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What is the value of

    SELECT COUNT(*) FROM RECEIPT_LINE WHERE RECEIPT_LINE.RC_DATE > '31-DEC-02'

    ?

  7. #7
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    I can't imagine what you're going to do with a 127,000 record result set, but I think the optimizer is doing a pretty good job. In other words, I doubt using the indexes will help. If your server has the processor capacity, the following might help:

    alter table datap.ITEM_MASTER parallel;
    alter table datap.RECEIPT_LINE parallel;

    or use the parallel hint in the SELECT clause

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >RECEIPT_LINE.RC_DATE > '31-DEC-02'
    It is poor coding technique to utilize default type conversion.
    Any index on RECEIPT_LINE.RC_DATE will NOT be used because the default behavior is to convert RECEIPT_LINE.RC_DATE to a string.
    You might get better results with
    RECEIPT_LINE.RC_DATE > TO_DATE('31-Dec-02','DD-MON-RR')

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by anacedent
    >RECEIPT_LINE.RC_DATE > '31-DEC-02'
    It is poor coding technique to utilize default type conversion.
    Any index on RECEIPT_LINE.RC_DATE will NOT be used because the default behavior is to convert RECEIPT_LINE.RC_DATE to a string.
    You might get better results with
    RECEIPT_LINE.RC_DATE > TO_DATE('31-Dec-02','DD-MON-RR')
    Actually, Oracle does the opposite - it converts the string '31-Dec-02' to a date - so this will not prevent use of an index on the column. But it is still very bad practice, simply because '31-Dec-02' might not convert to the date value you intend - it could be converted to 31-Dec-0002 or 02-Dec-2031 or whatever depending on the default format mask.

  10. #10
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Thanks for the reply.
    Actually the sql query is formed by Bussiness Objects reporting tool.
    So i have no control over the date format. Even i can not used Hint as BO doesn't support Hints.

    Total Number of rows for rc_date >'31-DEC-02'
    1164382.

    How often i have to analyzed the tables . (any rule for the same).


    Thanks,
    Pagnint
    (No need to search web before posting new question)

  11. #11
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    Every Time the table undergoes huge DMLs say for bulk load , it is better to get it analyzed

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by paginit
    Total Number of rows for rc_date >'31-DEC-02'
    1164382.
    There you are: this query accesses almost half the data in that table, so a full table scan is the correct approach there. Oracle could have used nested loops and indexes for some of joins to other tables, but for such a large data set has decided that hash joins are better. It is probably right. With such large queries you need to unlearn the myth "use index = go faster".

    You should analyze tables before the stats become so out of touch with reality as to be misleading - after bulk dataloads, as MePreeti says, and on a scheduled basis for tables that are continually being updated. Apply common sense: if the optimizer thinks there are 2.2M rows in the table when there are really 2.4M, it probably doesn't matter. But if it thinks there are 2.2M rows when there are really 200K or 6M rows, the stats are way off.

  13. #13
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Thank you very much for a very good explanation! u are really very genius

    One more think i would like to ask you.
    Some of my indexes are invalid. What would be the reason?How to make it valid.

    INDEX_NAME STATUS OWNER
    ------------------------------ -------- ----------
    I_BOOKING_WEEK_2 N/A DATAP
    I_INVOICE_WEEK_2 N/A DATAP
    PK_BOOKING N/A DATAP
    PK_INVOICE N/A DATAP

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I am not familiar with the index status "N/A" - I am only aware of VALID and UNUSABLE. An index can be manually set unusable, or may become so - e.g. a global index on a partitioned table becomes unusable if one of the table partitions is truncated. Another reason would be if you direct-path duplicate data into a table with a unique index.

  15. #15
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Partitioned indexes have status N/A as you have to look at user_ind_partitions to see the status of each partition.

    Alan

Posting Permissions

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