Oracle composite index and return table

In simple words, after the database finds the row where the specified record is located based on the index, it also needs to re-fetch data from the data block according to the rowid. A back-to-table statement is generally executed.

In simple words, after the database finds the row where the specified record is located based on the index, it also needs to re-fetch data from the data block according to the rowid. A back-to-table statement is generally executed.

Back table

Simply put, after the database finds the row where the specified record is located based on the index, it also needs to re-fetch data from the data block according to the rowid.

"Back-to-TABLE" generally refers to "table access by index rowid" displayed in the execution plan ".

For example, the select field contains columns not included in the index.

According to tom's Oracle programming art, the big_table and million data are created.

Index creation:

Create index idx_big_table_created on big_table (created );

The following statement does not return to the table because only the index column is queried.

Explain plan

Select created

From big_table

Where created = to_date ('2014/1/30 10:08:30 ', 'yyyy/mm/dd hh24: mi: ss ');

Select * from table (dbms_xplan.display)

The following statement returns to the table because other columns except the index column are also queried, although the id field is the primary key.

Explain plan

Select id, created

From big_table

Where created = to_date ('2014/1/30 10:08:30 ', 'yyyy/mm/dd hh24: mi: ss ');

Select * from table (dbms_xplan.display)

Composite Index

When an index contains multiple indexed columns, it is called a concatented index.

The following statement does not return to the table:

Explain plan

Select object_name, count (1)

From big_table

Where created> = to_date ('2014/1/30 10:07:50 ', 'yyyy/mm/dd hh24: mi: ss ')

And created <= to_date ('2014/1/30 10:08:37 ', 'yyyy/mm/dd hh24: mi: ss ')

Group by object_name;

Select * from table (dbms_xplan.display)

About composite indexes

1. How many indexes can be created for a table? For example, no more than five

This is not final. You need to determine the query efficiency and dml efficiency by yourself. indexes can accelerate the select query speed, but also reduce the execution speed of dml statements such as delete, insert, and update.

2. Is the combined index more efficient than a single index?

If multiple fields in the Union Index appear in the where predicate, the efficiency of the Union index is higher than that of the single-column index, because more than one condition can be used to filter out fewer records from the index, this reduces the number of times the table needs to be scanned back. You can even obtain all the results in the joint index directly, so you do not need to return the table.

However, because the combined index of multiple columns must be larger than the single-column index, that is to say, the same index must store more physical blocks than the single-column index. Therefore, if only one column in The Union index appears in the query, the efficiency is not as high as that of the single column index.

3. What is the role of compress compression?

Not studied

4. When querying data on the Internet, it is said that the oracle joint index requires a leading column. Is this related to the version?

The leading column has no relationship with the version.

This is the concept of a leading column. If a joint index on f1 and f2 is created, f1, or the so-called leading column, must be used for query, this index is valid because the index is sorted by the leading column. If the where condition predicate does not contain the leading column, you must perform an index scan to obtain the expected result, in this case, the efficiency is often poor.

5. If no leading column is required, what is the role of the reverse?

In view of the concept of leading columns described above, we consider the following table to store table (f1, f2 );

Aa 1

AB 2

Ac 3

Ad 4

AE 5

If we create a common index on the table f1 and sort it by f1, We need to traverse all the indexes starting from a For where f1 = ad, if you create a reverse index for f1, you can get the expected results faster because there is only one da.

This article permanently updates the link address: