Performance comparison of Oracle using indexes and not using indexes

The content of this article is about the performance comparison of Oracle using indexes and not using indexes. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

First prepare a table with millions of data, so that the analysis of data gaps is more vivid!

The following uses paging table data to analyze the table, sort according to the EMP_ID field, and find out the performance gap between using indexes 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 <= 20)
 WHERE ROWNUM_ >= 10;

Use explain plan for can 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 <= 20)
 WHERE ROWNUM_ >= 10;

to calculate SQL performance, use

select * from TABLE(dbms_xplan.display);打印出性能分析表

as shown below:

Before using index:

After using index

You can clearly see that the cpu usage is very low after using it, and the retrieval starts from the index instead of the full text Search

The above is the detailed content of Performance comparison of Oracle using indexes and not using indexes. For more information, please follow other related articles on the PHP Chinese website!