Create an effective index for OracleSQL to reduce the number of returned tables

Back-to-table: in data, when querying data, find the index in the index and obtain the rowid of the row. Then, query the data in the table based on the rowid, that is, return to the table. In the database

Back-to-table: in data, when querying data, find the index in the index and obtain the rowid of the row. Then, query the data in the table based on the rowid, that is, return to the table. In the database

Back-to-table: in data, when querying data, find the index in the index and obtain the rowid of the row. Then, query the data in the table based on the rowid, that is, return to the table.

In a database, data is stored in blocks. each row of data in a table has a unique ROWID. Each time you use SQL for query, you must scan the data block, find the ROWID of the row, and then scan the data block of the table. Returning the table will cause more data blocks to be scanned.

Example: SELECT a, B, cFROM TEST_DB WHERE B = 1

The query statement can be executed in two situations:

A. No index is created on B.

If no index is created on B, a full table scan is required to scan all data blocks in the SQL statement. Find and filter records from the data block. When no index is available, searching for data will scan all data blocks in the table, resulting in low performance.

B. Create an index on B

If you create an index on B, perform an index scan before executing the SQL statement, locate the position where B = 1 is located in the index (generally only three data blocks need to be scanned), obtain the ROWID of the modified row, and then query the data (back to the table) based on its ROWID ), if the data volume to be searched is small, the number of times the table is returned is small. In the preceding example, only B is in the index, and a is not in the index. Therefore, a must be queried once in the table. If a is also in the index, you do not need to go back to the table.

In database queries, there are many places to use the return table, such as paging queries. In general, the index should be divided by pages, and then the ROWID will be returned, and the query will be performed back to the table through the ROWID.

For example, the paging statement: SELECT * FROM (SELECT ROW_NUMBER OVER (order by a) RN, T. * from table t where B =? And c =? ) Where rn> = 1 and rn <= 20

In this paging query statement, we create indexes for B, C, and A. the query procedure is as follows:

1. First query the inner statement SELECT * from table t where B =? And c = ?,, Assume that 1000 rows of data are returned.

2. find the ROWID of the 1000 rows of data through the index. Because the index is continuous, assume that the index of the 1000 rows of data is distributed in three data blocks. Generally, you need to read six data blocks. Then retrieve the Table query data based on ROWID. The worst case is that the 1000 rows of data are distributed in 1000 data blocks, and 1000 data blocks need to be read. The total data block to be read is 1006.

If we use another method:

SELECT * from table t, (select rid from (select rowid rid, ROW_NUMBER OVER (order by a) rn from table where B =? And c = ?) Where rn> 1 and rn <= 20) tmp where tmp. RID = T. ROWID

In this example, select rid from (select rowid rid, ROW_NUMBER OVER (order by a) rn from table where B =? And c = ?) Where rn> 1 and rn <= 20, you can get all the data in the index. Like above, it is almost 6 data blocks. After paging, there are only 20 rows of data. In the case that the 20 rows of ROWID are returned to the table for data query, the worst case is that the 20 rows are all in 20 different blocks, in this case, only 26 data blocks are scanned.

Therefore, the effective use of indexes can reduce the number of times the table is returned, greatly improving the SQL Performance.