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

From: <l.flatz_at_bluewin.ch>
Date: Wed, 3 Dec 2014 08:35:16 +0000 (GMT)
Message-ID: <18206172.9185.1417595716676.JavaMail.webmail_at_bluewin.ch>



Hi Eriovaldo,
create a copy of your statement , run it and note the plan in some text document. Then use and Index hint to enforce use of the function based index. If the index is not used with the hint something is basically wrong. (e.g. implict conversion.). If the index can be used compare the cost with the cost without the index use (and without the hint). If the cost is higher without the index double check if the row estimates are correct. Maybe you need better stats on the base table. Also double check the reponse time and the buffer gets. Is the index really producing an advantage? regards
Lothar
----Ursprüngliche Nachricht----
Von : ecandrietta_at_gmail.com
Datum : 03/12/2014 - 02:20 (GMT)
An : oracle-l_at_freelists.org
Betreff : 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
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Dec 03 2014 - 09:35:16 CET

Original text of this message