Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.