Oracle Not Picking Up Index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Oracle Not Picking Up Index

  1. #1
    Join Date
    Apr 2000
    Location
    McDonough, GA, USA
    Posts
    6

    Unhappy

    Hi all,

    I have this script that is being executed:

    UPDATE ps_vchr_acctg_line
    SET posting_process = 'ACCR'
    WHERE appl_jrnl_id = 'ACCRUAL';


    And we created this index and analyzed it:

    CREATE INDEX PS_VCHR_ACCTG_LINE_UPG ON
    PS_VCHR_ACCTG_LINE(APPL_JRNL_ID)
    TABLESPACE TMAP_VCHR_LNIDX PCTFREE 10 STORAGE(INITIAL 2097152 NEXT 4694016 PCTINCREASE 1 )
    ;


    Why is the optimizer not picking up this index. The table has 10 million rows.

    lenaf7@yahoo.com

    Selena Flowers

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Selena Flowers - what a cool name. Anyway, how do you know the optimizer is not using the index?

  3. #3
    Join Date
    Apr 2000
    Location
    McDonough, GA, USA
    Posts
    6
    It's doing a full table scan...
    Selena Flowers

  4. #4
    Join Date
    Jan 2001
    Posts
    3,131
    This is just an off the top of my flat head guess but do you need to analyze the table when you slap an index on it?

    MH
    I remember when this place was cool.

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Try using a RULE hint, if you know best how to execute the SQL...

    UPDATE /*+ RULE*/ ps_vchr_acctg_line
    SET posting_process = 'ACCR'
    WHERE appl_jrnl_id = 'ACCRUAL';
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    Have you looked at the cost? Maybe it's a lower cost to do FTS.

    The cost of using an indexed accesss can be split into two components :

    1. Cost of traversing the index (Oracle indexes are B*tree structures
    made up of one or more levels of branch blocks and leaf blocks) and

    2. Cost of performing table lookup. In certain cases, this may not
    be needed if the optimizer determines that index lookup is sufficient
    to satisfy the query requirements.

  7. #7
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    stecal,

    You're probably right. I'm from the old school of "gimme the wheel and I'll drive this thing home with a rule hint". Seems like making the cost based optimizer do what you know is right is like pushing a rope. (uphill ... both ways)
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  8. #8
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    I don't know about other installations. There are multiple isuues with 8172-4 Optimizer behaviour at my shop. Lately I spent too much time on this topic with oracle and end up confirming all issues are more less bug related which they fixed in 9i(R2) and will be fixing in v10. I mean it I have seen the bug docs which says they will fix the bugs in V10.

    Reddy,Sam

  9. #9
    Join Date
    May 2002
    Posts
    2,645
    Bugs, bugs, and more bugs. I submitted one yesterday on 9iR2 (select distinct grantee from dba_role_privs causes ORA-00600, related to a bug in release 1, but different). Oracle doesn't give you a free T-shirt anymore for finding bugs.

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