Oracle Foundation---Index

Fourth. Index4.1 Definitions

An index is an optional structure associated with a table. By creating indexes, you can improve the performance of data updates and retrievals. The Oracle index provides a direct access path to the data row.

You can create indexes on one or more columns of a table. After the index is created, the Oracle server automatically maintains and uses the index. Updates to the table data (such as adding new rows, updating rows, or deleting rows) are automatically propagated to all relevant indexes, which are completely transparent to the user.

Indexes can also improve performance when implementing primary KEY and unique key constraint conditions. If there is no index, the entire table (full table scan) is scanned every time a DML operation is performed on the table.

4.2 Types

There are several types of index structures that you can use as needed. Two of the most common
Types are:

4.2.1 B-Tree Index

The default index type;
Take the form of a balanced tree.
The key values of the B-tree index are stored in the balance tree (b-tree), which allows for fast binary search execution.

Structure of the B-tree index

The top level of the index is the root, which contains entries that point to the next level of the index. The next level is the branch block, which also points to the block at the lower level of the index. At the bottom is the leaf node, which contains index entries that point to the table rows. Yushang are bi-directional, which makes it easy to scan the index in ascending or descending order of key values.

Format of index leaf entries

• Entry Header: Stores the number of columns and locks information
• Key column Length/value pairs: Used to define the column size in the key, followed by the column values (the number of such length/value pairs is the maximum number of columns in the index).
• ROWID: Row ID of the row containing the key value

Attributes of Index leaf entries

In the B-tree index of a non-partitioned table:

• When multiple rows have the same key value, if the index is not compressed, the key value appears duplicated
• When a row contains all the keys that are listed as NULL, the row does not have a corresponding index entry. Therefore, a full table scan is always performed when NULL is specified in the WHERE clause
• Because all rows belong to the same segment, use restricted ROWID to point to table rows

Performance of DML operations on indexes

When a DML operation is performed on a table, the Oracle server maintains all indexes. The following shows the effect of performing DML commands on indexes:

• Performing an insert operation causes the index entry to be inserted in the corresponding block.
• Deleting a row results in a logical deletion of the index entry. The space occupied by the deleted row is not available for subsequent new leaf entries.
• Updating the key columns results in the logical deletion and insertion of the index. The Pctfree setting has no effect on the index, except when it is created. You can add a new entry to an index block even if the index block has less space than pctfree specifies.

4.3 Create

#创建索引create index Emp3_name_ix Onemp3 (emp3_name), #查看索引的信息select index_name, Index_type, TABLE_NAME, TABLE_TYPE, Uniqueness, Statusfrom user_indexeswhere table_name = ' EMP3 '; index_name index_type table_name TABLE_TYPE U          Niquenes STATUS-------------------------------------------------------------------------emp3_id_pk NORMAL EMP3 table UNIQUE validemp3_name_ix NORMAL EMP3 table Nonunique valid# View the columns corresponding to the index sql> select * FROM user_ind_columns WHERE table_name = ' EMP3 '; index_name table_name column_name C Olumn_position column_length char_length DESC-------------------------------------------------------------------- --------------------EMP3_ID_PK EMP3 emp3_id 1 0 ascemp3_na Me_ix EMP3 emp3_name 1 ascsql> select * from Emp3 where EMP   3_name = ' QA1 ';    emp3_id Emp3_name  dep_id------------------------------2 qa1 2Execution Plan------------------------------------ ----------------------Plan Hash value:215206995----------------------------------------------------------------- ---------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |--------------------------------------------------------------------------------------------| 0 |              SELECT STATEMENT |     |    1 |     33 | 1 (0) |   00:00:01 | |  1 | TABLE ACCESS by INDEX rowid|     EMP3 |    1 |     33 | 1 (0) | 00:00:01 | |   * 2 | INDEX RANGE SCAN |     Emp3_name_ix |       1 |     | 1 (0) | 00:00:01 |--------------------------------------------------------------------------------------------predicate Information (identified by Operation ID):---------------------------------------------------2-access ("emp3_name" = ' Qa1 ') Statistics----------------------------------------------------------1 Recursive calls 0 DB block gets 3 consistent gets 0 physical re          Ads 0 Redo Size 675 Bytes sent via sql*net to client 524 bytes received via sql*net from client 2 sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows process Ed

4.4 Deciding whether to use a full table scan or an index

In most cases, full table scans may result in more physical disk input and output, but full table scans can sometimes be performed faster because of the existence of a high degree of parallelism.

The overall principle of index range scanning is:

• For raw * * sorted tables * * Only queries that read fewer than the number of table records 40％ should use index range scanning.
  Conversely, queries that read more records than the number of table records 40％ should use a full table scan.

• for * * Unordered tables * * Queries that read only fewer than 7％ the number of table records should use an index range scan.
  Conversely, queries that read more records than the number of table records 7％ should use a full table scan.

# # #决定使用全表扫描还是使用索引 sql> Select Index_name, Index_type, table_name, uniqueness, status from User_indexes where Table_nam E = ' EMP3 '; index_name index_type table_name uniqueness STATUS------------------------------------------------   ----------emp3_id_pk normal EMP3 UNIQUE validemp3_name_ix normal EMP3 nonunique  Validsql> Select COUNT (*) from Emp3;   COUNT (*)----------19# Although there is an index, but at this time is full table scan sql> select * from Emp3 where emp3_name = ' qa8 '; emp3_id emp3_name dep_id------------------------------qa8 2Execution Plan--------------- -------------------------------------------Plan Hash value:2425169977------------------------------------------- -------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |--------------------------------------------------------------------------| 0 |      SELECT STATEMENT |     |    1 |     11 | 2 (0) | 00:00:01 | | * 1 |  TABLE ACCESS full|     EMP3 |    1 |     11 | 2 (0) | 00:00:01 |--------------------------------------------------------------------------predicate information ( identified by Operation ID):---------------------------------------------------1-filter ("emp3_name" = ' qa8 ')           Statistics----------------------------------------------------------1 Recursive calls 0 db block gets         3 consistent gets 0 physical reads 0 Redo size 671 Bytes sent via sql*net to client 524 Bytes received via sql*net from client 2 sql*net roundtrips to/from client 0 sorts (Memor Y) 0 Sorts (disk) 1 rows processed# add data to Emp3 table [[email protected] sql]# more insert_data.sql#!/bin/ Bashi=$1;while [$i-le $]dosqlplus hr/ccm%[email protected] <<eofinsert into Emp3 values ($i, ' $ $ ', $4); Commit;quit; Eoflet I=i+1doneecho "inset into Emp3 table" [[email protected] sql]#./insert_data.sql Dev 1sql> Select Max (emp3_id) from Emp3; MAX (emp3_id)------------100000sql> Analyze table Emp3 estimate statistics; Table analyzed.    Sql> Select blocks, Empty_blocks, num_rows from user_tables where table_name = ' EMP3 '; BLOCKS empty_blocks num_rows--------------------------------374 10 101081# View a data sql> select *   From Emp3 where emp3_name = ' qa33333 '; emp3_id emp3_name dep_id------------------------------33333 qa33333 2Execution Plan--------------- -------------------------------------------Plan Hash value:215206995-------------------------------------------- ------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |--------------------------------------------------------------------------------------------| 0 |              SELECT STATEMENT |     |    1 |     14 | 2 (0) |   00:00:01 | |  1 | TABLE ACCESS by INDEX rowid|   EMP3 |  1 |     14 | 2 (0) | 00:00:01 | |   * 2 | INDEX RANGE SCAN |     Emp3_name_ix |       1 |     | 1 (0) | 00:00:01 |--------------------------------------------------------------------------------------------predicate Information (identified by Operation ID):---------------------------------------------------2-access ("emp3_name" = ' qa33333 ') Statistics----------------------------------------------------------0 recursive calls 0 db b Lock gets 4 consistent gets 0 physical reads 0 Redo size 681 Bytes sent via sql*net To client 524 bytes received via sql*net from client 2 sql*net roundtrips to/from client 0 sor  TS (memory) 0 sorts (disk) 1 rows processed# non-conditional query, index scan sql> select COUNT (*) from Emp3; COUNT (*)----------100000Execution plan----------------------------------------------------------plan hash Value: 2418373429----------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU) |   Time |----------------------------------------------------------------------------| 0 |            SELECT STATEMENT |     |    1 | 70 (2) |   00:00:01 | |  1 |            SORT AGGREGATE |     |            1 |          |   ||   2 | INDEX FAST Full scan|   EMP3_ID_PK |    101k| 70 (2) | 00:00:01 |----------------------------------------------------------------------------         Statistics----------------------------------------------------------2 Recursive calls 2 db block gets         262 consistent gets physical reads 176 redo size 526 Bytes sent via sql*net to client 524 Bytes received via sql*net from client 2 sql*net roundtrips to/from client 0 sorts (Memor  Y) 0 Sorts (disk) 1 rows processed# No index created, query full table scan sql> select COUNT (*) from Emp3 where dep_id = 2; COUNT (*)----------85726Execution Plan----------------------------------------------------------plan hash value:1396384608------------ ---------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |---------------------------------------------------------------------------| 0 |      SELECT STATEMENT |     |     1 |   2 | 104 (1) |   00:00:02 | |  1 |      SORT AGGREGATE |     |     1 |            2 |          | ||   * 2 | TABLE ACCESS full| EMP3 |    50541 |   98k| 104 (1) | 00:00:02 |---------------------------------------------------------------------------predicate information ( identified by Operation ID):---------------------------------------------------2-filter ("dep_id" =2)         Statistics----------------------------------------------------------1 Recursive calls 0 db block gets   373 consistent gets 0 physical reads 0 Redo size 528 Bytes sent via sql*net to client      524 Bytes received via sql*net from client 2 sql*net roundtrips to/from client 0 sorts (memory)  0 Sorts (disk) 1 rows processedsql> Select COUNT (*) from Emp3 where emp3_name like ' qa% '; COUNT (*)----------85726Execution plan----------------------------------------------------------plan hash Value: 3884997069----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |----------------------------------------------------------------------------------| 0 |              SELECT STATEMENT |     |     1 |     8 | 2 (0) |   00:00:01 | |  1 |              SORT AGGREGATE |     |     1 |            8 |          | ||   * 2 | INDEX RANGE scan|     Emp3_name_ix |    7 |     56 | 2 (0) | 00:00:01 |----------------------------------------------------------------------------------predicate Information (identified by Operation ID):---------------------------------------------------2-access ("emp3_name" like ' qa% ') filter ("Emp3_name" like ' qa% ') statisti        CS----------------------------------------------------------0 Recursive calls 0 db block gets        395 consistent gets 0 physical reads 0 Redo size 528 Bytes sent via sql*net to client          524 Bytes received via sql*net from client 2 sql*net roundtrips to/from client 0 sorts (memory) 0 Sorts (disk) 1 rows processed

Oracle Foundation---index