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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 3 Dec 2014 11:57:18 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282628B8_at_EXMBX01.thus.corp>

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 - 12:57:18 CET