Hi Jonathan,
Thanks for your prompt reply.
We have a Goldengate(12.2.0.2) setup, that replicates these tables to
Abinitio(Using Goldengate BigDataAdapter), which further is consumed by Hadoop.
Now, when we have Hidden Columns on Goldengate trailfiles, there respective
processes fail as they are not able to match data. In case we have to stick
with DB hidden columns, then the application team need to re-write a lot of
codes to map all cols with correct data type. In particular these are SIEBEL
tables, which have huge number columns in the tables.
Best Regards,
Sourav Biswas
+91-9650017306
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
Sent: Thursday, January 17, 2019 4:08 PM
To: Oracle Mailinglist
Subject: Re: How to disable Extended Statistics in Oracle 12c
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
