Oracle Development Series (iii) table access by index rowid you do not know the INDEX back TABLE, rowid back TABLE

Oracle Development Series (iii) table access by index rowid you do not know the INDEX back TABLE, rowid back TABLE
1 Introduction

Recently, the system often prompts a problem where the SQL query time is too long. After reading it, it is an SQL statement that counts the previous day according to the timestamp every day.
The total data volume of a table is 53483065.

The statement is as follows:

select count(x.serial_id) as countnum  from iodso.qos_cnst_busilog_td x where x.oper_time between trunc(sysdate- 1) and trunc(sysdate);

Execution time: (49 s for execution)




The following figure shows the execution plan:

From the above execution plan, index range scanning is also adopted.

2. Solution

If you cannot understand it, use count (*) to try it.

The execution time is as follows:

Time is very short, less than 1 s. The difference is very big. It is strange to compare the two execution plans. below is the execution plan of count (*).

 

 

Compared to the table access by index rowid that finds the slow one.

3 conclusion

The reason is that the index has a separate block storage. When calculating the data volume of the oper_time table, you only need to count the data volume in the index block, so it is faster.

The count (serialid ):

The Oracle index stores the rowid corresponding to the value of our field and the value. We search for it based on the index. After the index range scan, the rowid of the block will be returned, then we directly block the data we need based on rowid, so the following error occurs: TABLE ACCESS BY INDEX ROWID

It is much slower to query data on the data block of the table based on rowid.

4 remarks:

The execution time of the following two queries is also very fast, because the execution plan is the same as count.

Select COUNT (x. oper_time) AS countnum

Fromiodso. qos_cnst_busilog_td x

Where x. oper_timebetween trunc (sysdate-1) and trunc (sysdate );

 

 

 

Select COUNT (1) AS countnum

Fromiodso. qos_cnst_busilog_td x

Where x. oper_timebetween trunc (sysdate-1) and trunc (sysdate );