Oracle Not Picking Up Index

[lenaf7]

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

[stecal]

Selena Flowers - what a cool name. Anyway, how do you know the optimizer is not using the index?

[lenaf7]

It's doing a full table scan...

[Mr.Hanky]

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

[KenEwald]

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';

[stecal]

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.

[KenEwald]

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)

[sreddy]

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.

[stecal]

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.