Shulou(Shulou.com)05/31 Report--

This article focuses on "cardinality and selectivity Analysis of Oracle". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn "the cardinality and selectivity analysis of Oracle"!

Cardinal number

The number of unique keys (Distinct_keys) in a column. For a table with 100W rows, the cardinality of the gender column is 2 (select distinct gender from test) and the cardinality of the primary key column is 100W (select distinct mid from test).

Selectivity

As a percentage of cardinality / total rows, the higher the selectivity of the primary key 100%, the more advantageous it is to use the index 20: 30%, even if it is higher.

1. How to judge whether an index is created good or bad?

Just look at its cardinality and selectivity. if the cardinality is large and the selectivity is large, then it is better to use the index.

2. Does the gender column not use the index?

It depends:

From the OLTP system, it is certainly not suitable to create indexes on columns with low selectivity, while columns with high cardinality / selectivity are suitable for building B-Tree indexes.

In the OLAP system, it is possible to build bitmap indexes for columns with low cardinality according to the requirements.

3. How to check the selectivity and cardinality of the column?

Create a test test table

Create table test as select * from dba_objects

Create index idx_owner on test (owner)

Create index idx_object_name on test (object_name)

View the cardinality of owner and object_name columns

Select count (distinct owner), count (distinct object_name) from test

To see the cardinality and selectivity of columns, you can use the following script

Select count (distinct column_name), count (*) total_rows,count (distinct column_name) / count (*) * 100 selectivity from table_name

Let's look at the cardinality and selectivity of the owner of the test table

Select, count (*) total_rows,count (distinct owner) / count (*) * 100 selectivity from test

Count (distinct owner)

List as cardinality

Total_rows is listed as the total number of rows

Selectivity is listed as selective

When doing SQL optimization, do not rush to run the above SQL. You should first check how big the segment_size of the table is. If the segment_size of the table is too large (such as exceeding the buffer_cache of SGA), you should consider running the above SQL.

Whether it has an impact on your current system, it doesn't matter if it's a test environment, but if it's a production environment, be careful.

In fact, it is recommended to use the information in the statistics tables (dba_tab_col_statistics, dba_tables) to check the selectivity and cardinality. Note here that we need to collect statistics first, otherwise the returned column is null.

Select a.column_name

B.num_rows

A.num_distinct Cardinality

Round (a.num_distinct / b.num_rows * 100,2) selectivity

A.histogram

A.num_buckets

From dba_tab_col_statistics a, dba_tables b

Where a.owner = b.owner

And a.table_name = b.table_name

And a.owner = upper ('& owner')

And a.table_name = upper ('& table_name')

And a.column_name = upper ('& column_name')

4. Find out the unreasonable (low selectivity) index scripts in a user of the system.

Select a.OWNER

A.INDEX_NAME

A.TABLE_NAME

A.DISTINCT_KEYS Cardinality

A.NUM_ROWS

Round (a.DISTINCT_KEYS / NUM_ROWS * 100,2) selectivity

From dba_ind_statistics a

Where A.OWNER = upper ('& owner')

Selectivity

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.