Database Reference
In-Depth Information
Danach definieren wir die erweiterten Statistiken für die Spaltengruppe „A“ und „B“:
SQL> select dbms_stats.create_extended_stats(null,'T1','(a,b)') ext from dual;
EXT
--------------------------------------------------------------------------------
SYS_STUNA$6DVXJXTP05EH56DTIR0X
SQL>
SQL> select extension_name, extension from user_stat_extensions where table_name = 'T1';
EXTENSION_NAME
------------------------------
EXTENSION
--------------------------------------------------------------------------------
SYS_STUNA$6DVXJXTP05EH56DTIR0X
("A","B")
Jetzt generieren wir die erweiterten Statistiken und vergewissern uns, dass diese Statistiken
erzeugt wurden:
SQL> exec dbms_stats.gather_table_stats(user,'T1', method_opt=>'for columns "&ext" size 254',
no_invalidate=>false);
PL/SQL procedure successfully completed.
SQL> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name
= 'T1';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
SYS_STUNA$6DVXJXTP05EH56DTIR0X 201 FREQUENCY
SQL> select * from user_histograms where table_name = 'T1' and rownum <= 5;
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
ENDPOINT_ACTUAL_VALUE
--------------------------------------------------------------------------------
T1
SYS_STUNA$6DVXJXTP05EH56DTIR0X
1 32890104
Der Optimizer schätzt die Kardinalität absolut genau, weil die Anzahl der verschiedenen
Werte für die jeweilige Spaltengruppe unter 254 liegt:
Search WWH ::




Custom Search