Re: How to disable Extended Statistics in Oracle 12c

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 17 Jan 2019 10:38:30 +0000
Message-ID: <CWXP265MB0408F9CE01C94B8E035F59B2A5830_at_CWXP265MB0408.GBRP265.PROD.OUTLOOK.COM>

When you have a descending column in an index Oracle will ALWAYS create a column that is hidden, virtual, and not user_generated. It takes no space in the table segment, only a little space in the data dictionary so that Oracle can hold its definition and gather stats on it.

I am curious to know why you think you need to get rid of it.

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Sourav Biswas <biswas.sourav_at_hotmail.com> Sent: 17 January 2019 10:28
To: Oracle Mailinglist
Subject: How to disable Extended Statistics in Oracle 12c

Hi All,

I am using 12.1.0.2.0 database. Whenever, I create a Function based index, it creates a hidden column for extended statistics.

Ex:

SQL> CREATE UNIQUE INDEX "SIEBEL"."TEMP_CTLG_CAT_U1" ON "SIEBEL"."TEMP_CTLG_CAT" ("NAME", "CTLG_ID", "EFF_END_DT" DESC, "CONFLICT_ID") TABLESPACE "SBLINDEX"; Index created.

SQL> select * from dba_stat_extensions where table_name in ('TEMP_CTLG_CAT');

OWNER      TABLE_NAME           EXTENSION_NAME                 EXTENSION                                                          CREATO DROPPABLE
---------- -------------------- ------------------------------ ------------------------------------------------------------------ ------ ----------
SIEBEL     TEMP_CTLG_CAT        SYS_NC00053$                   ("EFF_END_DT")                                                     SYSTEM NO

And when I drop this function based index, the extention name also drops:-

Ex:

SQL> drop index SIEBEL.TEMP_CTLG_CAT_U1;

Index dropped.

SQL> select * from dba_stat_extensions where table_name in ('TEMP_CTLG_CAT');

no rows selected

I tried using "_optimizer_enable_extended_stats" to FALSE, but the behaivior didn't change:-

SQL> alter system set "_optimizer_enable_extended_stats"=FALSE scope=both sid='*';

Please suggest whether there is a way in oracle 12c, to create function based indexes and avoid these extended statistics and automatic creation of hidden columns.

Best Regards,
Sourav Biswas
+91-9650017306

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

Received on Thu Jan 17 2019 - 11:38:30 CET