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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Sourav Biswas <biswas.sourav@xxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
