Understanding B-tree indexes

Select and optimize Indexes

Understanding B-tree indexes (BANLANCE)

1. Create a table

Createtablecust (

Cust_idnumber,

Last_namevarchar2 (30 ),

First_namevarchar2 (30 ));

2. Determine that multiple SQL queries frequently use the last_name column in The where clause to create the following indexes:

Createindexindex_idx1oncust (last_name );

Insert data

Insertintocustvalues (7, 'acer', 'Scott ');

Insertintocustvalues (4, 'kan ', 'Jim ');

Insertintocustvalues (2, 'fan ', 'bob ');

Insertintocustvalues (9, 'star', 'Kim ');

...

Insertintocustvalues (117, 'acer', 'sid ');

3. Update table statistics to provide sufficient information for the query optimizer.

SQL> execdbms_stats.gather_table_stats (ownname => 'mlq', tabname => 'cust ', cascade => true );

Insert more data and query data from tables and indexes. There are three possible scenarios:

A. All data is indexed in the data block.

Index range scanning: Index quick and full scanning

Selectlast_namefromcustwherelast_name = 'acer'; range scan

Automatic Tracking:

Setautotraceon

Selectcount (last_name) fromcust;

Fast index scanning,

You can view the execution plan and statistical information to obtain more detailed results.

B. Not all information is included in the index.

Selectlast_name, first_namefromcustwherelast_name = 'acer ';

Access first_name through rownid

C. Only access table data blocks

Select * fromcust;

Full scan

The B-tree index is the default oracle index type, which is sufficient for most oltp.

It is very efficient and suitable to make queries much faster. If the index structure contains the column values to be retrieved,

You do not need to access table data blocks. To understand this principle, write an efficient index and determine the columns to create an index,

And determine whether the combined query is more efficient for some queries.

Estimate the space required by an index

Before creating an index, you can use the stored procedure to estimate the required space.

Setsererouton

Execdbms_stats.gather_table_stats (user, 'cust ');

Variableused_bytesnumber

Variablealloc_bytesnumber

Execdbms-space.create_index_cost ('createindexcust _ idx2oncust (first_name) ',: used_bytes,: alloc_bytes );)

Print: used_bytes

Print: alloc_bytes

View output results

This article is from the "mlqiang" blog, please be sure to keep this source http://162234.blog.51cto.com/152234/1302485