Oracle Performance Analysis 5: Index Structure and scan mode of data access methods, oracle Index

Oracle Performance Analysis 5: Index Structure and scan mode of data access methods, oracle Index

The previous article describes full scan. This article describes the index structure and scan methods, and describes each scan method later.
When Oracle searches for the values of a specific column or multiple columns through an index, it performs an index scan. First, let's look at the data contained in the index node.

Data contained in the index Node

An index can be created on a single or multiple column in a table. The index contains the values, rowids, and other information of these columns. We only care about column values and rowids. Because the index has a column value, if your SQL statement only involves the index column, then Oracle only retrieves the column value from the index itself, without accessing the table. If the query involves columns other than the index column, Oracle needs to use rowid to access the table.
The following is an example of rowid:

AAAN0+AABAAAPIqABj

Rowid contains the file number, data block number, and row number. With the following SQL, we can break down rowid into readable information using the previously created table T2:

select t.rowid,
       (select file_name
          from dba_data_files
         where file_id =
               dbms_rowid.rowid_to_absolute_fno(t.rowid, user, 'T2')) file_name,
       dbms_rowid.rowid_block_number(t.rowid) bokc_no,
       dbms_rowid.rowid_row_number(t.rowid) row_no
  from t2 t

The execution result is as follows:

ROWID				FILE_NAME							BOKC_NO	ROW_NO
AAAN0+AABAAAPIqAAA	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61994	0
AAAN0+AABAAAPIqAAB	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61994	1
AAAN0+AABAAAPIqAAC	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61994	2
......
AAAN0+AABAAAPIqAJd	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61994	605
AAAN0+AABAAAPIqAJe	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61994	606
AAAN0+AABAAAPIqAJf	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61994	607
AAAN0+AABAAAPIqAJg	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61994	608
AAAN0+AABAAAPIqAJh	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61994	609
AAAN0+AABAAAPIqAJi	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61994	610
AAAN0+AABAAAPIqAJj	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61994	611
AAAN0+AABAAAPIrAAA	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61995	0
AAAN0+AABAAAPIrAAB	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61995	1
AAAN0+AABAAAPIrAAC	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61995	2
AAAN0+AABAAAPIrAAD	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61995	3
AAAN0+AABAAAPIrAAE	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61995	4
......
AAAN0+AABAAAPIrAJV	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61995	597
AAAN0+AABAAAPIrAJW	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61995	598
AAAN0+AABAAAPIrAJX	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61995	599
AAAN0+AABAAAPIrAJY	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61995	600
AAAN0+AABAAAPIrAJZ	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61995	601
AAAN0+AABAAAPIrAJa	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61995	602
AAAN0+AABAAAPIsAAA	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61996	0
AAAN0+AABAAAPIsAAB	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61996	1
AAAN0+AABAAAPIsAAC	E:\ORACLE\ORADATA\LY\SYSTEM01.DBF	61996	2
......

The location of all rows in Table T2 is obtained, including the file, data block number, and row number in the block. We can see the distribution of data.
Note that the dbms_rowid.rowid_to_absolute_fno function is defined as follows:

function dbms_rowid.rowid_to_absolute_fno
(rowid in rowid,
schema_name in varchar2,
object_name in varchar2)
return number

1) rowid: rowid;
2) schema_name: user Name. Here is the current user)
3) object_name: Object Name. Here it is T2.
 
Then, we can find the data through index scanning through the above description:
1) obtain the index data block and obtain the index column and rowid;
2) If the query only involves index columns, the query ends;
3) Otherwise, find the data block through rowid and locate the data through the row number.

Index Structure and index scan type

Here we only discuss B-tree indexes, and B-tree indexes are a tree structure. The table is blank when it was just created. The corresponding index will only have one root node and the index height is 1. In addition, the index also has a blevel statistical information to indicate the branch level in an index, the value is 0, which can be obtained through the following query:

select index_name,blevel from user_indexes where index_name =upper( 'index_name');

As new data is inserted into the table, the new index entries are added to the block until the block is full, oracle will allocate two new index blocks and add the index entries to these two new leaf blocks. The previous index blocks will become pointers to the new index blocks, this pointer contains the Relative data Block Address (Relative Block Address, RBA) pointing to the new index Block and the lowest index value in the related leaf Block. At this time, the index height will change to 2, and the blevel value will change to 1.
As the data in the table continues to grow, the index block will be further split and the height will continue to grow, eventually forming a tree structure:

After learning about the index structure, you can easily understand index scanning. There are many different types of index scanning, but you must traverse the index structure to search for matched leaf nodes. First, obtain the index's root block through a single read, and then obtain the block of the path node through multiple read operations until the block where the leaf node is located (matching block ), obtain the rowid of the data from the matched leaf node and obtain a row of data through rowid using a single read. Therefore, if the index structure height is 4, five data blocks, four index blocks, and one table data block are required to query a row of data.
Index scan types include: index range scan, unique index scan, full index scan, index skip scan, and quick full index scan. The features and application scope of each scan method will be detailed later.

The data structure in oracle is like that.

Basic Data Structure

Table

A table is a basic data structure in a relational database. A table is a set of rows. Each row contains one or more columns.

After Oracle8 Enterprise Edition, the partition option is provided, which allows you to partition tables and indexes. Using partitions, Oracle can improve performance in the following two aspects:

. Oracle does not need to access partitions that do not meet the query Conditions

. If all data in the partition meets the query conditions, Oracle Selects all data without having to perform a statement check on each row.

View

A view is a data structure constructed by SQL statements in Oracle. SQL statements are stored in a database. When a view is used in a query, the stored query is executed and data in the base table is returned to the user.

A view does not contain data, but shows some methods to view the specified base table data.

A view can be used for the following purposes:

. Simplify data access to multiple tables

. Ensure data security in the table (for example, creating a view containing the WHERE clause can restrict access to the data in the table)

. Detaches an application from some specific structures in the table.

The view is based on the base table set. The base table includes fact tables or other views in the Oracle database. If any base table in the view is modified, the view cannot continue to use them, so the view itself cannot be used.

Index

Index is a data structure used to speed up access to records in the database. An index is related to a specific table and contains data of one or more columns of the table.

Basic SQL syntax for index creation:

Creat index emp_idx1 on emp (ename, job );

The index name is used in emp_idx1, and emp is the table for creating the index. The ename and job constitute the column value of the index.

In addition to the index data, the ROWID. ROWID is saved for the relevant row in the index item, which is the fastest way to obtain the database row. Therefore, the database row acquisition is completed in this best way.

Four types of index structures used in Oracle: Standard B *-tree index, reverse key index, bitmap index, and function-based index introduced by Oracle8i. Oracle enables you to aggregate data in tables to improve performance.

Other data structures

Sequence)

It is difficult to provide unique sequence numbers for keys or identifiers. In this case, Oracle allows you to create sequence objects.

The serial number can be a name, an incremental value, or other information about the sequence. The sequence is independent of any table, so multiple tables can use the same serial number.

Synonym (Synonym)

The data structures of all Oracle databases are stored in a specific schema ). Mode is associated with a specific user name. All objects are referenced by the mode name with the object name.

For example, if you want to reference the table EMP, you must reference the table EMP with the complete name DEMO. EMP. If no specific schema name is provided, Oracle assumes that the schema is in the current username mode.

Cluster)

A cluster is a data structure that can improve the query performance. The same as the index, the cluster does not affect the logical view of the table.

Hash Cluster)

Data design

Constraints

Constraint forces the integrity of certain data in the database. When a constraint is added to a column, Oracle automatically ensures that data that does not meet the constraint is never accepted.

Constraints can be associated with a TABLE that creates or adds a column (using a keyword), or after the TABLE is created, the SQL command ALTER TABLE is used to implement the constraints ...... remaining full text>
 
Analysis of several cases where indexes are not used in Oracle

Oracle does not use B * tree indexes. The following figure shows the situation: 1. If the where condition is used and null is used, indexes may not be used. 2: count, sum, ave, max, min and other aggregation operations may result in no index 3: display or implicit function conversion, resulting in no index 4: In cbo mode, the statistical information is too old, resulting in no index 5: if no leading column is used in the composite index, the index is not used. 6: The accessed data volume exceeds a certain proportion. As a result, if the index is not used, Let's explain the following points: can Null be indexed? Generally, fulltablescan is caused by comparing null in the where condition. In fact, if the value of the index column in the table is null, therefore, this row does not exist in indexes (B * tree, bitmap index, or clustered Index). Therefore, to ensure the accuracy of the query structure, instead of index scan, full table scan is used. Of course, if an index column is defined as not null, in this case, no index column is empty, so in this case, you can use index scan. Therefore, if the where condition contains something similar to is null, = null, whether to take the index depends on whether a column in the index is defined as not null. The specific experiment is as follows: SQL> create table t (x char (3), y char (5); SQL> insert into t (x, y) values ('001 ', 'xxxxx'); SQL> insert into t (x, y) values ('002 ', null); SQL> insert into t (x, y) values (null, 'yyyy'); SQL> insert into t (x, y) values (null, null); SQL> commit; SQL> create unique index t_idx on t (x, y ); SQL> analyze table t compute statistics for table for all indexes; SQL> select blevel, leaf_blocks, num_rows from user_indexes where index_name = upper ('t_ idx '); BLEVEL LEAF_BLOCKS NUM_ROWS ---------- ----------- ---------- 0 1 3isnert four records, but the index only saves three records, the last one is not saved in the index SQL> set autotrace traceonly explain; SQL> select * from t where x is null; Execution Plan limit 0 SELECT STATEMENT ptimizer = CHOOSE (Cost = 2 Card = 1 Bytes = 8) 1 0 TABLE ACCESS (FULL) OF 'T' (Cost = 2 Card = 1 Bytes = 8) SQL> create table t1 (x char (3), y char (5) not null ); SQL> insert into t1 (x, y) values ('001', 'xx ...... remaining full text>