Oracle Development Series (iii) Table ACCESS by index ROWID You don't know the index back table

1 Introduction

Recently, the system often prompts a SQL query for too long, and looked at a daily count of the day by the time stamp of the amount of SQL.
The total data volume for the table is 53483065.

The statements are as follows:

Select COUNT (x.serial_id) as Countnum from  iodso.qos_cnst_busilog_td x where x.oper_time between Trunc (Sysdate-1) an D trunc (sysdate);

The execution time situation is as follows: (Execution to 49s)




Look at the next execution plan is this:

From the execution plan above, it's gone. Index range scan.

2 Resolution

Do not understand, decided to use COUNT (*) to try.

The time of execution is as follows:

Time is fast and 1s is not there. It's very different, it's weird. Compare the execution plan of both, below is the execution plan of Count (*)

Compared to the one that was found to be slow, more than one TABLE ACCESS by INDEX ROWID.

3 Conclusion

The reason: The index has a separate block storage, according to the data volume of the Oper_time statistics only need to be in the index of the block of statistical data can be, so faster.

That count (serialid):

The Oracle index holds the value of our field and the value corresponding to the ROWID, we look up according to the index, after the index range is scanned, it will return the block's rowid, and then according to rowID directly to block up the data we need, so it appears: TABLE ACCESS by INDEX ROWID

Because you also want to query the data on the data block of the ROWID back table, so the speed is much slower.

4 Notes:

The following two queries are executed quickly because the execution plan is the same as the 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);

Oracle Development Series (iii) Table ACCESS by index ROWID You don't know the index back table