Results 1 to 14 of 14

Thread: Multiple Index

  1. #1
    Join Date
    Nov 2003
    Posts
    33

    Unanswered: Multiple Index

    Hi,
    I have this simple query , which is not using the index.

    select max(received_date_time)
    into v_maxprofdate
    from edi_814_account a, edi_814_service b, edi_814_meter c
    where c.service_id = b.service_id and b.transaction_id = a.transaction_id
    and load_profile_code is not null

    edi_814_service is like as associate table where there is 2 indexes on transaction_id and service_id.
    Though there is one index on transaction_id and 1 index on service_id, The optimizer is not choosing to use the index,
    If i have to force the index on the query using a HINT, can i hint to use both indexes in the query. I have used one index, in the hint, i have not used multiple indexes, Is there such facility in Oracle.

    /*+ INDEX(EDI_814_SERVICE ED1_SERV_IDX1,EDI_SERV_IDX2) */
    (Can I use the above syntax, is it correct )


    Here is the explain plan of the above query

    SELECT STATEMENT, GOAL = CHOOSE 17629 1 47
    SORT AGGREGATE 1 47
    HASH JOIN 17629 966193 45411071
    HASH JOIN 5318 966353 33822355
    TABLE ACCESS FULL AEP_EDIDB EDI_814_METER 957 966353 24158825
    TABLE ACCESS FULL AEP_EDIDB EDI_814_SERVICE 2803 2630732 26307320
    TABLE ACCESS FULL AEP_EDIDB EDI_814_ACCOUNT 10564 2630297 31563564


    I would appreciate your help very much



    Thanks
    Saratha

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > load_profile_code is not null is the culprit forcing the FTS.
    NULLS are NOT stored in indexes; therefore an index can NOT be used
    when searching for NULL or NOT NULL values.

  3. #3
    Join Date
    Nov 2003
    Posts
    33
    Originally posted by anacedent
    > load_profile_code is not null is the culprit forcing the FTS.
    NULLS are NOT stored in indexes; therefore an index can NOT be used
    when searching for NULL or NOT NULL values.

    Thanks for your reply. I removed the load_profile_code is not null, and still it would not use the index.

    This query use to run fine until a few days back. Sometime back they added a new field in one of the tables EDI_814_METER in the query. Since then there seems to be a problem in processing this query.

    IS there any specific thing that i can check with our DBAs?
    I dont even know what to ask for?

    Thanks for your help
    Saratha

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You can use multiple indexes in a hint

    /*+ INDEX(EDI_814_SERVICE ED1_SERV_IDX1)
    INDEX(EDI_814_SERVICE ED1_SERV_IDX2) */

    However... I would ensure that the statistics are up todate for the
    tables and columns... Have the dba's validate the structure of the
    indexes. If this is a heavily DML index, it may need to be rebuilt
    due to index "Browning".

    ex:
    analyze table estimate statistics sample 20 percent for all indexed columns;

    Would it be worth the effort to have a multi-column index (index with both columns in it?)

    HTH
    Gregg

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    there is a common error in your syntax.
    you need to use the table synonym, not the tablename in the hint.
    only usde the

    new hint would be:
    Code:
    /*+ INDEX(b EDI_SERV_IDX2) */
    I would pick the index that indexes both b.service_id and b.transaction_id.

    if you do not have one, then create one.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Nov 2003
    Posts
    33
    Thanks all of you.

    I shall try using hint to use the multiple indexes using the alias name of the table.

    I shall also check with the DBA as to when the table was analysed last.
    Would you mind explaining as why analysing the table is necessary? what does the estimate statistics do?

    Thanks for your help once again.
    Saratha

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

    Re: Multiple Index

    Without much in the way of conditions in your query, you are more or less selecting ALL records from edi_814_service and joining them to the other 2 tables. Therefore it makes sense for Oracle to perform a full table scan on edi_814_service. If there are indexes on the other 2 tables (edi_814_meter.sevice_id and edi_814_account.transaction_id) then Oracle could choose to use those. However, again, perhaps Oracle recognises that it will end up selecting nearly all rows from both tables, and so prefers the FTS and HASH JOIN approach.

  8. #8
    Join Date
    Nov 2003
    Posts
    33
    Do you all think it would be beneficial to create an index on the
    received_date_time on the EDI_814_ACCOUNT table and another index on load_profile_code in the EDI_814_METER table?

    Saratha

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Saratha
    Do you all think it would be beneficial to create an index on the
    received_date_time on the EDI_814_ACCOUNT table and another index on load_profile_code in the EDI_814_METER table?

    Saratha
    received_date_time - no
    load_profile_code - maybe (probably not)

    If instead of "load_profile_code is not null" you had "load_profile_code > (some value)" then Oracle might use the index - provided the majority of rows have a NULL for that column.

    Perhaps you should give us a complete description of the tables and indexes involved here, and how many rows there are in each table, for a start.

  10. #10
    Join Date
    Nov 2003
    Posts
    33
    EDI_814_METER -- 1,300,000 records
    EDI_814_SERVICE -- 2,630,000
    EDI_814_ACCOUNT -- 2,630,000

    EDI_814_METER -- index on service id
    EDI_814_SERVICE -- index on transaction_id , Index on service id
    EDI_814_ACCOUNT -- INdex on transaction Id.

    let me know if any more details woul dbe beneficial.

    thanks for the help
    Saratha

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    How many meter rows where load_profile_code is not null?

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    Andrew meant to describe the tables.

    desc EDI_814_METER
    etc.

    I would experiment with creating an index on
    EDI_814_SERVICE.service_id ,EDI_814_SERVICE.transaction_id.
    See if that helps at all.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Nov 2003
    Posts
    33
    The EDI_814_METER HAS 552513NULL load_profile_code.


    SQL> desc edi_814_service
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    TRANSACTION_ID NUMBER(10)
    SERVICE_ID NUMBER(10)
    ACTION_CODE VARCHAR2(2)
    CHANGE_DESC VARCHAR2(30)
    CHANGE_REASON VARCHAR2(6)
    CUST_AGREE_DATE DATE
    CUSTOMER_AGREEMENT VARCHAR2(2)
    DISTRIB_LOSS VARCHAR2(2)
    ELIGIBLE_DATE DATE
    ESI_ID VARCHAR2(80)
    ESI_ID_END_DATE DATE
    ESI_ID_START_DATE DATE
    ESI_PROC_HANDLING VARCHAR2(2)
    LINE_TRACK_NUM VARCHAR2(30)
    MOVE_IN_DATE DATE
    MOVE_OUT_DATE DATE
    NEXT_MET_READ_DATE DATE
    PREMISE_TYPE VARCHAR2(2)
    PRODUCT_ID VARCHAR2(2)
    SERV_END_DATE DATE
    SERV_RELATIONSHIP VARCHAR2(30)
    SERVICE2_REQ VARCHAR2(3)
    SERVICE_REQ VARCHAR2(3)
    STATUS_CODE VARCHAR2(3)
    STATUS_REASON VARCHAR2(120)
    STD_PT_LOC_CODE VARCHAR2(80)
    PRIORITY_CODE VARCHAR2(2)
    DROP_CODE VARCHAR2(2)


    SQL> desc edi_814_account
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    TRANSACTION_ID NUMBER(10)
    CLRHS_DUNS VARCHAR2(13)
    CLRHS_NAME VARCHAR2(60)
    CREATED_DATE DATE
    CREATED_DATE_TIME DATE
    DC_DUNS VARCHAR2(13)
    DC_NAME VARCHAR2(60)
    RETAIL_DUNS VARCHAR2(13)
    RETAIL_NAME VARCHAR2(60)
    ORIG_TRANS_REF_NUM VARCHAR2(30)
    RECEIVED_DATE_TIME DATE
    RESP_REQ_FLAG VARCHAR2(2)
    STATE_ID VARCHAR2(2)
    TRANS_REF_NUM VARCHAR2(30)
    TRANS_SET_ID VARCHAR2(2)
    TRANS_SUBTYPE VARCHAR2(2)
    UNIQUE_TRANS_REF VARCHAR2(44)
    Z_BATCH_FILENAME VARCHAR2(500)
    Z_BATCH_START_LINE NUMBER(10)
    Z_ITEM_CANCELLED VARCHAR2(1)
    Z_ITEM_REJECTED VARCHAR2(1)


    SQL> desc edi_814_meter
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    SERVICE_ID NUMBER(10)
    METER_ID NUMBER(10)
    DC_RATE_CLASS VARCHAR2(30)
    DC_RATE_SUBCLASS VARCHAR2(30)
    LIFE_SUPPORT VARCHAR2(1)
    LOAD_PROFILE_CODE VARCHAR2(30)
    METER_ACTION VARCHAR2(3)
    METER_CYCLE VARCHAR2(2)
    METER_CYCLE_DOM VARCHAR2(2)
    METER_NO VARCHAR2(30)
    METER_TYPE VARCHAR2(6)
    PRODUCT_TYPE VARCHAR2(2)
    UNMETER_QUANTITY NUMBER(10)
    UNMET_LUMENS NUMBER(10)
    METER_OWNER_CODE VARCHAR2(30)


    Thanks
    Saratha

  14. #14
    Join Date
    Nov 2003
    Posts
    33
    Thanks all of you.
    I used a Hint to use the Index as suggested before, It seems to work now, It takes 3 minutes to execute .

    Thanks for all of your help
    Saratha

Posting Permissions

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