SQL> create table exstat (a number, b date); SQL> select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual; select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual * ERROR at line 1: ORA-20001: Invalid Extension: Column group can contain only columns seperated by commaTrying to create extended statistics with column a and trunc(b) results in an error. What's clear from the error is that 1. Oracle was expecting a column group and 2. It must only contain columns separated by comma.
It is expecting a column group but the second portion of the extension is not recognized as a column hence the error. To overcome this create a function base index. For the above extended statistics extension following index was created
create index aidx on exstat(a,trunc(b));After which the creation of the extended statistics works.
SQL> select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual; DBMS_STATS.CREATE_EXTENDED_STATS(USER,'EXSTAT','(A,TRUNC(B))') -------------------------------------------------------------------------------- SYS_STUE4B2X1G802ME0XHTBYWFY_QSimply create the index first and then extended statistics.
Trying to drop an extended statistics extension that uses function results in the following error
SQL> exec dbms_stats.drop_extended_stats(user,'EXSTAT','(A,TRUNC(B))'); BEGIN dbms_stats.drop_extended_stats(user,'EXSTAT','(A,TRUNC(B))'); END; * ERROR at line 1: ORA-20000: extension "(A,TRUNC(B))" does not exist ORA-06512: at "SYS.DBMS_STATS", line 13055 ORA-06512: at "SYS.DBMS_STATS", line 45105 ORA-06512: at line 1Even though the extension is present the error message says "does not exist". To resolve this drop the index created for the extended statistics extension
SQL> DROP INDEX AIDX; Index dropped.After which extended statistics is dropped without any issue.
SQL> exec dbms_stats.drop_extended_stats(user,'EXSTAT','(A,TRUNC(B))'); PL/SQL procedure successfully completed.This was tested on 12.1, 11.2.0.4 and 11.2.0.3 and all exhibited the same behavior. But this test failed on 11.1.0.7. even after creating the index.
SQL> create table exstat (a number, b date); SQL> select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual; select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual * ERROR at line 1: ORA-20001: Invalid Extension: Column group can contain only columns seperated by comma SQL> create index aidx on exstat(a,trunc(b)); SQL> select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual; select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual * ERROR at line 1: ORA-20001: Invalid Extension: Column group can contain only columns seperated by commaThe index has created a hidden virtual column. Trying to add a virtual column complains of duplication
SQL> alter table exstat add (c as (trunc(b))); alter table exstat add (c as (trunc(b))) * ERROR at line 1: ORA-54015: Duplicate column expression was specifiedBut having a virtual column with the function expression didn't help either.
SQL> drop index aidx; SQL> alter table exstat add (c as (trunc(b))); SQL> select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual; select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual * ERROR at line 1: ORA-20001: Invalid Extension: Column group can contain only columns seperated by commaIt's temping to use the virtual column itself in the extended statistics column group. But "extension cannot contain a virtual column" is restriction number one!
SQL> select dbms_stats.create_extended_stats(user,'EXSTAT','(a,c)') from dual; select dbms_stats.create_extended_stats(user,'EXSTAT','(a,c)') from dual * ERROR at line 1: ORA-20001: Error when processing extension - virtual column is referenced in a column expression