Re: Index based function is not being used in plan execution
- From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- To: Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
- Date: Wed, 3 Dec 2014 09:55:49 +0100
Where is the query?
And why
*'for all columns size skewonly for columns (**nvl(COLUMN1,1))'*
is your function based index created on (column1, 1) or on (nvl(column1,1))
Best regards
Mohamed Houri
www.hourim.wordpress.com
2014-12-03 2:20 GMT+01:00 Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>:
> 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
>
>
--
Houri Mohamed
Oracle DBA-Developer-Performance & Tuning
Member of Oraworld-team <http://www.oraworld-team.com/>
Visit My - Blog <http://www.hourim.wordpress.com/>
Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
<https://twitter.com/MohamedHouri>
Other related posts:
