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

From: <l.flatz_at_bluewin.ch>
Date: Wed, 3 Dec 2014 12:12:30 +0000 (GMT)
Message-ID: <11143583.24399.1417608750773.JavaMail.webmail_at_bluewin.ch>

oh, so it had a consequence after all!
----Ursprüngliche Nachricht----
Von : jonathan_at_jlcomp.demon.co.uk
Datum : 03/12/2014 - 12:57 (GMT)
An : l.flatz_at_bluewin.ch, ecandrietta_at_gmail.com Cc : oracle-l_at_freelists.org
Betreff : RE: Re: Index based function is not being used in plan execution It's worth mentioning that the call to dbms_stats will have created a virtual column to support the requested stats on nvl(column1,1).    

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of l.flatz_at_bluewin.ch [l.flatz_at_bluewin.ch] Sent: 03 December 2014 11:42
To: ecandrietta_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: Re: Index based function is not being used in plan execution Hi Eriovaldo,
in that case the statement :
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;
/
should have no effect. Consequntly you will not have histograms on the hidden column. BTW: 100% sample size will not be necessary. Now does the regular index ( CREATE INDEX idx_tab ON tab (column1);) work for you? Thanks
Lothar

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

Received on Wed Dec 03 2014 - 13:12:30 CET