Re: dbms_stats hidden column

From: Jure Bratina <jure.bratina_at_gmail.com>
Date: Tue, 16 Dec 2014 09:04:25 +0100
Message-ID: <CAC08BHKRx3wPpKO_7v=sSkNwhObzaBVTfi0UaOFZvMWAJofUgA_at_mail.gmail.com>



Hi,

have you tried collecting statistics with cascade=>false, e.g.:

exec dbms_stats.gather_table_stats(
USER,'TAB_COL', method_opt=>'FOR COLUMNS (lower(USER_NAME))', cascade=>false);

Regards,
Jure Bratina

On Tue, Dec 16, 2014 at 8:45 AM, Petr Novak <Petr.Novak_at_trivadis.com> wrote:
>
> Hallo,
>
> table with two indexes, statistics calculated. Third index over
> lower(USER_NAME) created, this column is not inluded in first two indexes.
>
> I tried to calculate statistics for hidden column:
>
> exec dbms_stats.gather_table_stats(USER,'TAB_COL', method_opt=>'FOR
> COLUMNS SYS_NC00009$');
> exec dbms_stats.gather_table_stats(USER,'TAB_COL', method_opt=>'FOR
> COLUMNS (lower(USER_NAME))');
>
> trace for both calls shows full scan on table and full scan on third index
> (as expected), but also full scans on first two indexes (their
> last_analyzed updated after call).
> Why the other two indexes were also scanned ? Is my statement wrong or it
> is some bug ?
>
>
> Best Regards ,
> Petr--
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 16 2014 - 09:04:25 CET

Original text of this message