The difference between using indexes and not using indexes in oracle

Editor to share with you the difference between using an index and not using an index in oracle. I hope you will gain a lot after reading this article. Let's discuss it together.

First of all, prepare a table with millions of pieces of data, so that it is more vivid to analyze the data gap!

The following is to analyze the table with paged table data, sort according to the EMP_ID field, and the performance gap between using and not using indexes!

Sql query syntax preparation. Specific business writes sql syntax according to specific tables:

SELECT * FROM (SELECT ROW_.*, ROWNUM ROWNUM_ FROM (SELECT * FROM KQS_SQ_INFO i ORDER BY i.EMP_ID desc) ROW_ WHERE ROWNUM = 10

Use explain plan for to analyze sql

As follows:

Explain plan for SELECT * FROM (SELECT ROW_.*, ROWNUM ROWNUM_ FROM (SELECT * FROM KQS_SQ_INFO i ORDER BY i.EMP_ID desc) ROW_ WHERE ROWNUM = 10

Calculate the SQL performance using the

Select * from TABLE (dbms_xplan.display); print out the performance analysis table

As shown below:

Before using the index:

After using the index

You can clearly see that the utilization rate of cpu is very low after use, and the retrieval starts from the index rather than full-text retrieval.

After reading this article, I believe you have a certain understanding of the difference between using an index and not using an index in oracle. If you want to know more about it, welcome to follow the industry information channel. Thank you for your reading!