Application of Statistical Information of Oracle histogram
Oracle histogram statistics describe the distribution of data in the table and are used to guide the CBO optimizer to choose the best execution plan when the data is unevenly distributed in the table. The following example illustrates this application.
Create a tabl
Create table scott.t (id number)
Create an index
Create index scott.idx_t_id on scott.t (id) compute statistics parallel
Insert data
Begin
For i in 1.. 29990 loop
Insert into scott.t values (1)
End loop
Commit
End
/
Begin
For i in 29991.. 30000 loop
Insert into scott.t values (mod (I, 7))
End loop
Commit
End
/
View data distribution
Select id
Count (*) cardinality
Sum (count (*)) over (order by id range unbounded preceding) sum_cardinality
From scott.t
Group by id
ID CARDINALITY SUM_CARDINALITY
0 1 1
1 29991 29992
2 1 29993
3 2 29995
4 2 29997
5 2 29999
6 1 30000
It can be seen that the distribution of data in the table is seriously uneven, with only 1 record with ID 0, 2 and 6, 29991 records with ID 1, and 1 record with ID 3, 4 and 5 respectively.
When the query is executed in this case, looking at the execution plan, you can see that a full table scan is used due to the poor selectivity of the predicate ID=1.
Set autot trace exp
Select * from scott.t where id=1
Carry out the plan
Plan hash value: 1601196873
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 0 | SELECT STATEMENT | | 29991 | 89973 | 15 (0) | 00:00:01 |
| | * 1 | TABLE ACCESS FULL | T | 29991 | 89973 | 15 (0) | 00:00:01 |
Predicate Information (identified by operation id):
1-filter ("ID" = 1)
The following query has the condition of high uniqueness. Because the predicate ID=0 has good selectivity, index scanning is used.
Select * from scott.t where id=0
Carry out the plan
Plan hash value: 371777749
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0) | 00:00:01 |
| | * 1 | INDEX RANGE SCAN | IDX_T_ID | 1 | 3 | 1 (0) | 00:00:01 |
Predicate Information (identified by operation id):
1-access ("ID" = 0)
Create a width-balanced histogram equal to the number of distinct_keys=7
Begin
Dbms_stats.gather_table_stats (ownname = > 'SCOTT'
Tabname = >'T'
Estimate_percent = > 100
Method_opt = > 'FOR COLUMNS SIZE 7 ID'
Degree = > 4
Cascade = > true)
End
/
Query histogram buckets data distribution information
Col owner for a10
Col table_name for a20
Col column_name for a20
Col endpoint_number for a20
Col endpoint_value for a20
Select h.owner
H.table_name
H.column_name
To_char (h.endpoint_number) endpoint_number
To_char (h.endpoint_value) endpoint_value
From dba_histograms h
Where h.owner = 'SCOTT'
And h.table_name ='T'
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
--
SCOTT T ID 1 0
SCOTT T ID 29992 1
SCOTT T ID 29993 2
SCOTT T ID 29995 3
SCOTT T ID 29997 4
SCOTT T ID 29999 5
SCOTT T ID 30000 6
If binding variables are not used on the basis of histogram statistics, predicates with low query selectivity will not go out of index.
Select * from scott.t where id=1
Carry out the plan
Plan hash value: 1601196873
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 0 | SELECT STATEMENT | | 29991 | 89973 | 15 (0) | 00:00:01 |
| | * 1 | TABLE ACCESS FULL | T | 29991 | 89973 | 15 (0) | 00:00:01 |
Predicate Information (identified by operation id):
1-filter ("ID" = 1)
But if you use binding variables, the effect will be different.
Var i number
Exec: iDrex1
Select * from scott.t where id=:i
Carry out the plan
Plan hash value: 371777749
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 0 | SELECT STATEMENT | | 4286 | 12858 | 9 (0) | 00:00:01 |
| | * 1 | INDEX RANGE SCAN | IDX_T_ID | 4286 | 12858 | 9 (0) | 00:00:01 |
Predicate Information (identified by operation id):
1-access ("ID" = TO_NUMBER (: I))
The above tests show that if the query with low selectivity is executed in the case of uneven data distribution in the table, CBO will choose index scan if there is complete and accurate histogram statistics and binding variables are used. If there is no histogram information, CBO will choose a full table scan instead of the index. The use of histogram statistics here greatly improves query performance.
