如何检查Oracle中是否正在使用索引 [英] How to check whether index is being used or not in Oracle

问题描述

SELECT * 
FROM   (SELECT TEMP.*, 
               ROWNUM RNUM 
        FROM   (SELECT entry_guid 
                FROM   alertdevtest.ENTRY 
                WHERE  Upper(alert_name) = 'alertname' 
                       AND user_guid = 'AlertProductClientTest' 
                       AND product_code = '-101' 
                       AND status_code != 13) TEMP 
        WHERE  ROWNUM <= 2500) 
WHERE  rnum >= 0; 

SELECT * 
FROM   (SELECT TEMP.*, 
               ROWNUM RNUM 
        FROM   (SELECT entry_guid 
                FROM   alertdevtest.ENTRY 
                WHERE  Upper(alert_name) = 'alertname' 
                       AND user_guid = 'AlertProductClientTest' 
                       AND product_code = '-101' 
                       AND status_code != 13 
                       AND product_view IN ( 'PView' )) TEMP 
        WHERE  ROWNUM <= 2500) 
WHERE  rnum >= 0; 

在上述查询中运行的Am在第二个查询中看到的性能与第一个查询相比有所下降.唯一的区别是第二个查询中的附加过滤器AND PRODUCT_VIEW IN('PView').但是它在该列上有索引.请让我知道性能下降的原因可能是什么，如何检查索引是否正在使用?我正在使用Oracle SQL开发人员，并尝试检查说明计划，但没有太多细节.

Am running above queries and seeing performance degradation in the second query compare to the first one. The only difference is being the additional filter AND PRODUCT_VIEW IN ('PView') in second query. But it has index on that column. Please let me know what can be the reason for performance degradation and how can I check whether index being used or not? Am using Oracle SQL developer and tried checking explain plan but couldn't get much details.

推荐答案

在Oracle SQL Developer中，当工作表中包含SQL时，会有一个说明计划"按钮，您也可以按F10键.执行Explain计划后，它将显示在SQL Developer的底部视图中.有一列"OBJECT_NAME"，它将告诉您正在使用什么索引.例如，在我刚运行的查询中，左列(OPERATION)首先显示"SELECT STATEMENT"，然后显示SORT(AGGREGATE)，然后显示INDEX(RANGE SCAN)，然后在OBJECT_NAME列中显示TICKER_IDX1，即我桌子上索引的名称.

In Oracle SQL Developer, when you have SQL in the worksheet, there is a button "Explain Plan", you can also hit F10. After you execute Explain plan, it will show in the bottom view of SQL Developer. There is a column "OBJECT_NAME", it will tell you what index is being used. For example, in a query I just ran, in the left column (OPERATION) it shows "SELECT STATEMENT" first, then SORT (AGGREGATE) and then INDEX (RANGE SCAN) and then in the OBJECT_NAME column it shows TICKER_IDX1, which is the name of an index on my table.

因此，您可以通过OBJECT_NAME列查看正在使用的索引.

So you can see via the OBJECT_NAME column what indexes are being used.

Oracle基于成本的优化器可能会选择次优执行计划.许多次更新统计信息将解决此问题.其他选择是添加其他索引，换句话说就是多列索引.您可以提示一条SQL语句，但这很少需要.另外，可以重写查询.

It can happen that the Oracle Cost Based Optimizer chooses a sub-optimal execution plan. Many times updating statistics will solve the issue. Other choices are to add additional indexes, in other words a multi-column index. You can hint a SQL statement, but that is rarely needed. Also, it's possible to rewrite the query.

这篇关于如何检查Oracle中是否正在使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！