Extended Statistics :
In releases prior to 11g , if we execute a query which includes multiple predicates in where conditions,it is impossible for the optimizer to calculate the correct selectivity of these predicates as it had no way of knowing if the columns were related or not.This makes working out the selectivity of the column group very difficult.
In Oracle Database 11g, there is a new kind of statistic, called multi-column statistics, which is a type of extended stat. Using this feature, you can actually create associations between different columns to help the optimizer make better decisions
Extended statistics
1) Multicolumn stats
2) Function-Based Stats
=====================================================================
DEMO DEMO DEMO DEMO DEMO DEMO DEMO
=====================================================================
SQL> create table scott.multicol_stats as select * from dba_objects;
Table created.
SQL> conn scott/tiger
Connected.
SQL> exec dbms_stats.gather_table_stats('SCOTT','MULTICOL_STATS');
PL/SQL procedure successfully completed.
SQL> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = 'MULTICOL_STATS';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
OWNER 28 NONE
OBJECT_NAME 41192 NONE
SUBOBJECT_NAME 120 NONE
OBJECT_ID 68851 NONE
DATA_OBJECT_ID 6632 NONE
OBJECT_TYPE 42 NONE
CREATED 1493 NONE
LAST_DDL_TIME 1552 NONE
TIMESTAMP 1647 NONE
STATUS 1 NONE
TEMPORARY 2 NONE
GENERATED 2 NONE
SECONDARY 2 NONE
NAMESPACE 20 NONE
EDITION_NAME 1 NONE
15 rows selected.
SQL> select count(1) from MULTICOL_STATS;
COUNT(1)
----------
68851
SQL> create index TEST1_IDX1 on multicol_stats (owner, object_type);
Index created.
SQL> select owner , count(1) from multicol_stats group by owner order by owner;
OWNER COUNT(1)
------------------------------ ----------
BI 8
CTXSYS 342
DBSNMP 55
EXFSYS 303
FLOWS_030000 1528
FLOWS_FILES 12
HR 34
IX 53
MDSYS 1281
OE 125
OLAPSYS 720
ORACLE_OCM 8
ORDPLUGINS 10
ORDSYS 2349
OUTLN 9
PM 26
PUBLIC 26604
SCOTT 12
SH 306
SI_INFORMTN_SCHEMA 8
SYS 29711
SYSMAN 3285
SYSTEM 516
TSMSYS 3
WKSYS 371
WK_TEST 47
WMSYS 315
XDB 810
28 rows selected.
SQL> select object_type , count(1) from multicol_stats group by object_type order by object_type;
OBJECT_TYPE COUNT(1)
------------------- ----------
CLUSTER 10
CONSUMER GROUP 14
CONTEXT 7
DIMENSION 5
DIRECTORY 9
EDITION 1
EVALUATION CONTEXT 13
FUNCTION 298
INDEX 3278
INDEX PARTITION 304
INDEXTYPE 11
JAVA CLASS 22103
JAVA DATA 305
JAVA RESOURCE 833
JOB 11
JOB CLASS 13
LIBRARY 179
LOB 785
LOB PARTITION 7
MATERIALIZED VIEW 3
OPERATOR 57
PACKAGE 1267
PACKAGE BODY 1206
PROCEDURE 133
PROGRAM 18
QUEUE 37
RESOURCE PLAN 7
RULE 1
RULE SET 21
SCHEDULE 2
SEQUENCE 233
SYNONYM 26690
TABLE 2589
TABLE PARTITION 145
TRIGGER 488
TYPE 2657
TYPE BODY 227
UNDEFINED 6
VIEW 4773
WINDOW 9
WINDOW GROUP 4
XML SCHEMA 92
42 rows selected.
SQL> select owner , object_type , count(1) from multicol_stats group by owner,object_type order by owner,object_type;
OWNER OBJECT_TYPE COUNT(1)
------------------------------ ------------------- ----------
..
..
PM LOB 17
PM TABLE 3
PM TYPE 3
PUBLIC SYNONYM 26604
SCOTT INDEX 4
SCOTT TABLE 8
SH DIMENSION 5
SH INDEX 27
SH INDEX PARTITION 196
..
..
..
277 rows selected.
SQL> select count(1) from multicol_stats where owner = 'PUBLIC' and object_type = 'SYNONYM';
COUNT(1)
----------
26604
SQL> explain plan for select * from multicol_stats where owner = 'PUBLIC' and object_type = 'SYNONYM';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 928537683
--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |
--------------------------------------------------------------------------------
--------------
| 0 | SELECT STATEMENT | | 249 | 25149 | 13 (
0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MULTICOL_STATS | 249 | 25149 | 13 (
0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST1_IDX1 | 249 | | 1 (
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SYNONYM')
14 rows selected.
SQL> explain plan for select * from multicol_stats where owner = 'PUBLIC' and object_type = 'SEQUENCE';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 928537683
--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |
--------------------------------------------------------------------------------
--------------
| 0 | SELECT STATEMENT | | 249 | 25149 | 13 (
0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MULTICOL_STATS | 249 | 25149 | 13 (
0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST1_IDX1 | 249 | | 1 (
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SEQUENCE')
14 rows selected.
SQL> exec dbms_stats.gather_table_stats('SCOTT','MULTICOL_STATS',method_opt=>'FOR ALL COLUMNS')
PL/SQL procedure successfully completed.
SQL> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = 'MULTICOL_STATS';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
OWNER 28 FREQUENCY
OBJECT_NAME 41192 HEIGHT BALANCED
SUBOBJECT_NAME 120 HEIGHT BALANCED
OBJECT_ID 68851 HEIGHT BALANCED
DATA_OBJECT_ID 6632 HEIGHT BALANCED
OBJECT_TYPE 42 FREQUENCY
CREATED 1493 HEIGHT BALANCED
LAST_DDL_TIME 1552 HEIGHT BALANCED
TIMESTAMP 1647 HEIGHT BALANCED
STATUS 1 FREQUENCY
TEMPORARY 2 FREQUENCY
GENERATED 2 FREQUENCY
SECONDARY 2 FREQUENCY
NAMESPACE 20 FREQUENCY
EDITION_NAME 1 FREQUENCY
15 rows selected.
SQL> explain plan for select * from multicol_stats where owner = 'PUBLIC' and object_type = 'SYNONYM';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2851574935
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 9794 | 966K| 285 (1)| 00:00:
04 |
|* 1 | TABLE ACCESS FULL| MULTICOL_STATS | 9794 | 966K| 285 (1)| 00:00:
04 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SYNONYM')
13 rows selected.
SQL> explain plan for select * from multicol_stats where owner = 'PUBLIC' and object_type = 'SEQUENCE';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 928537683
--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |
--------------------------------------------------------------------------------
--------------
| 0 | SELECT STATEMENT | | 74 | 7474 | 5 (
0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MULTICOL_STATS | 74 | 7474 | 5 (
0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST1_IDX1 | 74 | | 1 (
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SEQUENCE')
14 rows selected.
SQL> exec dbms_stats.gather_table_stats('SCOTT','MULTICOL_STATS',method_opt=>'FOR COLUMNS (owner,object_type)')
PL/SQL procedure successfully completed.
SQL> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = 'MULTICOL_STATS';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
OWNER 28 FREQUENCY
OBJECT_NAME 41192 HEIGHT BALANCED
SUBOBJECT_NAME 120 HEIGHT BALANCED
OBJECT_ID 68851 HEIGHT BALANCED
DATA_OBJECT_ID 6632 HEIGHT BALANCED
OBJECT_TYPE 42 FREQUENCY
CREATED 1493 HEIGHT BALANCED
LAST_DDL_TIME 1552 HEIGHT BALANCED
TIMESTAMP 1647 HEIGHT BALANCED
STATUS 1 FREQUENCY
TEMPORARY 2 FREQUENCY
GENERATED 2 FREQUENCY
SECONDARY 2 FREQUENCY
NAMESPACE 20 FREQUENCY
EDITION_NAME 1 FREQUENCY
SYS_STUXJ8K0YTS_5QD1O0PEA514IY 277 HEIGHT BALANCED
16 rows selected.
SQL> explain plan for select * from multicol_stats where owner = 'PUBLIC' and object_type = 'SYNONYM';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2851574935
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 25704 | 2635K| 285 (1)| 00:00:
04 |
|* 1 | TABLE ACCESS FULL| MULTICOL_STATS | 25704 | 2635K| 285 (1)| 00:00:
04 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SYNONYM')
13 rows selected.
SQL> explain plan for select * from multicol_stats where owner = 'PUBLIC' and object_type = 'SEQUENCE';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 928537683
--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |
--------------------------------------------------------------------------------
--------------
| 0 | SELECT STATEMENT | | 64 | 6720 | 4 (
0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MULTICOL_STATS | 64 | 6720 | 4 (
0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST1_IDX1 | 64 | | 1 (
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SEQUENCE')
14 rows selected.
SQL> select count(*) from multicol_stats where lower(object_type) = 'index';
COUNT(*)
----------
3278
SQL> explain plan for select * from multicol_stats where lower(object_type) = 'index';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2851574935
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 689 | 72345 | 285 (1)| 00:00:
04 |
|* 1 | TABLE ACCESS FULL| MULTICOL_STATS | 689 | 72345 | 285 (1)| 00:00:
04 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(LOWER("OBJECT_TYPE")='index')
13 rows selected.
SQL> exec dbms_stats.gather_table_stats('SCOTT','MULTICOL_STATS',method_opt => 'for all columns size skewonly for columns (lower(object_type))');
PL/SQL procedure successfully completed.
SQL> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = 'MULTICOL_STATS';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
OWNER 28 FREQUENCY
OBJECT_NAME 41192 HEIGHT BALANCED
SUBOBJECT_NAME 120 FREQUENCY
OBJECT_ID 68851 NONE
DATA_OBJECT_ID 6632 HEIGHT BALANCED
OBJECT_TYPE 42 FREQUENCY
CREATED 1493 HEIGHT BALANCED
LAST_DDL_TIME 1552 HEIGHT BALANCED
TIMESTAMP 1647 HEIGHT BALANCED
STATUS 1 FREQUENCY
TEMPORARY 2 FREQUENCY
GENERATED 2 FREQUENCY
SECONDARY 2 FREQUENCY
NAMESPACE 20 FREQUENCY
EDITION_NAME 1 FREQUENCY
SYS_STUXJ8K0YTS_5QD1O0PEA514IY 277 HEIGHT BALANCED
SYS_STUVJ5LMUFWDLVURW#_ROXEK1U 42 FREQUENCY
17 rows selected.
SQL> explain plan for select * from multicol_stats where lower(object_type) = 'index';
Explained.
SQL> select dbms_metadata.get_ddl('TABLE','MULTICOL_STATS') from dual;
DBMS_METADATA.GET_DDL('TABLE','MULTICOL_STATS')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."MULTICOL_STATS"
( "SYS_STUXJ8K0YTS_5QD1O0PEA514IY"
SQL> explain plan for select * from multicol_stats where lower(object_type) = 'index';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2851574935
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 3344 | 372K| 285 (1)| 00:00:
04 |
|* 1 | TABLE ACCESS FULL| MULTICOL_STATS | 3344 | 372K| 285 (1)| 00:00:
04 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MULTICOL_STATS"."SYS_STUVJ5LMUFWDLVURW#_ROXEK1U"='index')
13 rows selected.
SQL>
Multi column Statistics
Posts
Post a Comment
Thank you for visiting our site and leaving your valuable comment.