RE: Index based function is not being used in plan execution

From: Chitale, Hemant K <Hemant-K.Chitale_at_sc.com>
Date: Wed, 3 Dec 2014 09:46:17 +0800
Message-ID: <0BDF2A25A09ADD40908745EEFC0A0FB602BC6C1E_at_HKMGAXMB103A.zone1.scb.net>

Possibly the optimizer decided that the cost of using the index would be greater than for an execution plan that doesn't use the index.  

Hemant K Chitale    

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Eriovaldo Andrietta Sent: Wednesday, December 03, 2014 9:21 AM To: ORACLE-L
Subject: Index based function is not being used in plan execution  

Hi,  

I have a index create like this:
CREATE INDEX idx_tab ON tab (column1 ASC, 1);  

I used the the collect statistics command like this:  

BEGIN
dbms_stats.gather_table_stats(

     ownname => user, 
     tabname => 'TAB', 
     estimate_percent => 100, 
     cascade => TRUE, 
     method_opt => 'FOR ALL HIDDEN COLUMNS SIZE 1');

END;
/
 

BEGIN
   dbms_stats.gather_table_stats (

      ownname    => USER,
      tabname    => 'TAB',
      estimate_percent => 100, 
      cascade => TRUE, 
      method_opt => 'for all columns size skewonly for columns

(nvl(COLUMN1,1))'
);
END;
/
   

But the index is not used in the query.
Is this resource (index based on function) released for Oracle Standard ?
Regards
Eriovaldo

This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 03 2014 - 02:46:17 CET