Oracle back table (i)

To write efficient SQL, you must have a clear SQL execution path, a lot of articles about how to improve SQL performance, and I'll talk about how to improve query performance from reducing the number of SQL back tables, because the back table will cause more chunks to be scanned.

As we all know, data stores in database tables are in blocks, called blocks, and each row of data in a table has a unique address flag of ROWID.

As an example:

Select a from test_db where b=5

A, suppose there is no index on B

1, then this SQL will be a table scan, scan all the data blocks of the table

2. Find the record from the data block and filter

It is conceivable that no index will result in scanning all data blocks of the table, performance is poor

b, suppose there is an index on B

1, then the SQL will be indexed scan, in the index to find the location of b=5, generally only need to scan 3 blocks around to find

2. Get the rowid of all b=5 rows

3, according to ROWID re-query data (this is the back table), if the amount of data is small, then the number of back table is less, if the required data are all in the index, then will not return to the table, such as a also in the index, if a is not in the index, then still need to return to the table once a.

Experience: If possible, try to query only the index, without returning the table or just a few back tables.

For example, paging needs to be returned to the table, generally as far as possible on the index page, and then return to rowID, and then rowid back to the table query.

The following is a common 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
SELECT * FROM (select Row_number over (order by a) rn,t.* from table T where b=? and c=?) where Rn>=1 and RN <=20

Let's analyze (assuming the index is B,C,A):

1, first query the inner statement select * FROM table t where b=? and c=, assuming that 1000 rows of data are returned

2, through the index to find the ROWID of the 1000 rows of data, because the index is continuous, assuming that the index of 1000 rows of data is distributed in 5 blocks, then almost 8 blocks read

3, again according to ROWID retrieval table query data, the worst case is that the 1000 rows of data distributed in 1000 blocks, you need to read 1000 blocks. So count the top 8 bucks for the total Yaodu District 1000+8=1008 block.

Let's change the wording:

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
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

To analyze again:

1. Innermost SQL 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 can get all the data from the index, and because the index is orderly, it's almost 8 blocks read

2, after paging, only 20 rows of data, and then according to the 20 rows of rowID back to the table query data, the worst case is 20 rows are in 20 different blocks, then a total of 20+8=28

From the above analysis can be seen, the effective use of indexes, reduce the number of back to the table, can greatly improve SQL performance, it is worth the effort to understand.

Oracle Back table (i)