Re: Bind Variables ignoring Index on 10.2.0.4

From: Dion Cho <ukja.dion_at_gmail.com>
Date: Mon, 7 Apr 2008 18:53:17 -0700 (PDT)
Message-ID: <429c0f62-257d-4f96-823c-9106a4cd8f81@v32g2000prd.googlegroups.com>


On 4월8일, 오전5시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
Bind peeking has nothing to do with this abnormality because explain plan does not utilize bind peeking.

If you're finding quick dirty solution, INDEX_RS hint(added as of 10.2.0.3) would be a solution.

If you have much more time to spend on this problem, you had bettern do a full investigation, including
- statistics change including histogram
- optimizer parameter change(including hidden parameter) between 10.2.0.3 and 10.2.0.4 Received on Mon Apr 07 2008 - 20:53:17 CDT

Original text of this message