Hello.
Have you any experiences with problem of automatically created extended statistics in Oracle 12?
We have problem of impossibility of changing definition of some columns because there were created virtual column belong to Extended Statistics.
Do you know if it is possible to turn off creating these statistics?
I have found only internal parametr (I don't know if it prevent creating or only using ES):
_optimizer_enable_extended_stats
Description: use extended statistics for selectivity estimation
Type:BOOL; Obsoleted: FALSE
Can ALTER SESSION: TRUE; Can ALTER SYSTEM: IMMEDIATE.
Note: This is an internal Oracle parameter. Do NOT use it unless instructed to do so by Oracle Support. Playing with this parameter may be harmful.
There are some facts here (Oracle 12.1.0.1 Win64):
Extended Statistics are two types:
- Column Group Statistics (ESCGS)
Automatic Column Group Detection, randomly added virtual columns; dropable; Oracle 12.1;
If Optimizer conclud, that some columns works together, they create ESCGS with these columns. It is what Oracle name 'Automatic Column Group Detection', I suppose that compound indexes is significant for it. But for me is randomly, because I don't know where nor when will be created new ESCGS.
http://docs.oracle.com/database/121/TGSQL/tgsql_astat.htm#TGSQL462
Expression Statistics (ESES)
For every functional index (for example: index (column1 DESC)) are virtual column created; not dropable; Oracle 11.2;
http://docs.oracle.com/database/121/TGSQL/tgsql_astat.htm#TGSQL478
--- 1. example of trouble with ESCGS ---------------------------------------------
-- rename column
ALTER TABLE zpravy RENAME COLUMN odeslal TO odeslal_old;
Error at line 1
ORA-54032: column to be renamed is used in a virtual column expression
-- extended statistics
SELECT EXTENSION_NAME, EXTENSION
FROM dba_stat_extensions
WHERE table_name='ZPRAVY' AND owner='M7ZULIMS';
EXTENSION_NAME EXTENSION
------------------------------- ------------------------------------------
SYS_STS23QG$GO$77VMC2G$1MGRSIU ("ODESLAL","TYPVZKAZU") SYS_STSM$CMAQMNWQBJAWA84XJAQLG ("ADRESAT","TYPVZKAZU")
2 rows selected.
-- virtual column
SELECT column_name, virtual_column, hidden_column
FROM user_tab_cols
WHERE table_name='ZPRAVY'
AND NOT virtual_column='NO';
COLUMN_NAME VIRTUAL_COLUMN HIDDEN_COLUMN
------------------------------ ------ --------
SYS_STS23QG$GO$77VMC2G$1MGRSIU YES YES
SYS_STSM$CMAQMNWQBJAWA84XJAQLG YES YES
-- note: These virtual columns content hash data created by SYS_OP_COMBINED_HASH()
-- drop extended statistics
exec DBMS_STATS.DROP_EXTENDED_STATS (ownname=>'M7ZULIMS', tabname=>'ZPRAVY', extension=>'("ODESLAL","TYPVZKAZU")');
exec DBMS_STATS.DROP_EXTENDED_STATS (ownname=>'M7ZULIMS', tabname=>'ZPRAVY', extension=>'("ADRESAT","TYPVZKAZU")');
(dropped correctly)
--- 2. example of trouble with ESES -------------------------------------------------------------------
CREATE TABLE test(col1 VARCHAR2(10 CHAR));
Table created.
CREATE INDEX test_i01 ON test(col1 DESC);
Index created.
ALTER TABLE test RENAME COLUMN col1 TO col1_old;
Error at line 5
ORA-00900: invalid SQL statement --(sic!)
SELECT EXTENSION_NAME, EXTENSION
FROM user_stat_extensions
WHERE table_name='TEST';
EXTENSION_NAME EXTENSION
-------------- ------------------
SYS_NC00002$ ("COL1")
1 row selected.
-- Drop of ES is impossible (user_stat_extensions.dropable='NO'):
exec DBMS_STATS.DROP_EXTENDED_STATS (ownname=>'M7ZULIMS', tabname=>'TEST', extension=>'("COL1")');
Error at line 1
ORA-20001: Invalid Extension: Not an expression
DROP TABLE test;
Table dropped.
--EOF
Zprávu upravil(a): user461852