Re: Bind Variables ignoring Index on 10.2.0.4

On 7 Apr, 21:18, Pat <pat.ca..._at_service-now.com> wrote:
> > What is the status of the index?
>
> Looks valid as far as I can tell. Am I missing something?
>
> SQL> select index_name, index_type, status, uniqueness from
> user_indexes where index_name like 'SYS_TRIGGER%';
>
> INDEX_NAME                     INDEX_TYPE                  STATUS   UNIQUENES
> ------------------------------ --------------------------- --------
> ---------
> SYS_TRIGGER_INDEX1             NORMAL                      VALID    NONUNIQUE
> SYS_TRIGGER_INDEX3             FUNCTION-BASED NORMAL       VALID    NONUNIQUE
> SYS_TRIGGER_REF6               FUNCTION-BASED NORMAL       VALID    NONUNIQUE
> SYS_TRIGGER_REF8               FUNCTION-BASED NORMAL       VALID    NONUNIQUE
> SYS_TRIGGER_SYSID              FUNCTION-BASED NORMAL   VALID    UNIQUE
No, not missing anything.

As far as I can see, in your original post the optimizer correctly identifies that this will return a single row, but chooses an access path with the greater cost ( ignoring the bind/literal difference ). So, something about the bind, or the index is throwing it off.

Maybe consider digging through a 10053 trace to check the optimizer calculations, and raise this with Oracle support.

Might be worth checking the runtime behaviour of the query by calling dbms_xplan against V$SQL_PLAN, in case this shows different runtime behaviour or costs.

When you resolve your issue, will you please post a summary to this group?

Cheers,

Mat. Received on Tue Apr 08 2008 - 05:52:12 CDT