Database Reference
In-Depth Information
EXTENSION_NAME EXTENSION
------------------------------ ---------------
SYS_STU0KSQX64#I01CKJ5FPGFK3W9 (UPPER("PAD"))
SYS_STUPS77EFBJCOTDFMHM8CHP7Q1 ("VAL2","VAL3")
As shown in the output of the next query, the hidden columns have the same name as the extensions. Also notice
how the definition of the extension is added to the column:
SQL> SELECT column_name, data_type, hidden_column, data_default
2 FROM user_tab_cols
3 WHERE table_name = 'T'
4 ORDER BY column_id;
COLUMN_NAME DATA_TYPE HIDDEN DATA_DEFAULT
------------------------------ --------- ------ -----------------------------------
ID NUMBER NO
VAL1 NUMBER NO
VAL2 NUMBER NO
VAL3 NUMBER NO
PAD VARCHAR2 NO
SYS_STU0KSQX64#I01CKJ5FPGFK3W9 VARCHAR2 YES UPPER("PAD")
SYS_STUPS77EFBJCOTDFMHM8CHP7Q1 NUMBER YES SYS_OP_COMBINED_HASH("VAL2","VAL3")
■
because the extended statistics for a group of columns are based on a hash function (
sys_op_combined_hash
),
they work only with predicates based on equality. in other words, the query optimizer can't take advantage of them
for predicates based on operators like
BETWEEN
and
<
or
>
. extended statistics for a group of columns are also used
to estimate the cardinality of
GROUP BY
clauses and, from version 11.2.0.3 onward, for the
DISTINCT
operator in
SELECT
clauses.
Caution
To drop an extension, the
dbms_stats
package provides you with the
drop_extended_stats
procedure. In the
following example, the PL/SQL block drops the two extensions previously created:
BEGIN
dbms_stats.drop_extended_stats(ownname => user,
tabname => 'T',
extension => '(upper(pad))');
dbms_stats.drop_extended_stats(ownname => user,
tabname => 'T',
extension => '(val2,val3)');
END;
