sql优化一例(index_desc)-CSDN博客

在生产库看到这样一条sql，没有走索引。

10:26:05 SQL> EXPLAIN plan FOR
10:27:06 2 SELECT thread_id
10:27:06 3 FROM (SELECT thread_id, rownum rn
10:27:06 4 FROM (SELECT b.thread_id
10:27:06 5 FROM test_blog b
10:27:06 6 WHERE b.STATUS != -1
10:27:06 7 ORDER BY b.gmt_modified DESC)
10:27:06 8 WHERE rownum <= :1)
10:27:06 9 WHERE rn >= :2;
 
PLAN_TABLE_OUTPUT
---------------------------------
 
-------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-------------------------------------------
| 0 | SELECT STATEMENT | | 1429K| 35M| | 6123 |
|* 1 | VIEW | | 1429K| 35M| | 6123 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 1429K| 17M| | 6123 |
|* 4 | SORT ORDER BY STOPKEY| | 1429K| 23M| 76M| 6123 |
|* 5 | TABLE ACCESS FULL | TEST_BLOG | 1429K| 23M| | 798 |
-----------------------------------

在status、gmt_modified、thread_id上有个组合索引IDX_BLOG_STATUS,大家都知道”不等于”是不会用到index,分析了一下业务，完全可以把status != -1改为status = 0,这里我还多考虑了一步，加个hint，固定它走IDX_BLOG_STATUS。

看一下加hint后的执行计划,感觉没有什么大的问题

10:34:04 SQL> EXPLAIN plan FOR
10:34:20 2 SELECT thread_id
10:34:20 3 FROM (SELECT /*+ index(b IDX_BLOG_STATUS) */thread_id, rownum rn
10:34:20 4 FROM (SELECT b.thread_id
10:34:20 5 FROM test_blog b
10:34:20 6 WHERE b.STATUS = 0
10:34:20 7 ORDER BY b.gmt_modified DESC)
10:34:20 8 WHERE rownum <= 500)
10:34:20 9 WHERE rn >= 450;
 
Explained.
 
Elapsed: 00:00:00.00
10:34:21 SQL> SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
----------------------------------------------
 
----------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 13000 | 5174 |
|* 1 | VIEW | | 500 | 13000 | 5174 |
|* 2 | COUNT STOPKEY | | | | |
| 3 | VIEW | | 1429K| 17M| 5174 |
|* 4 | INDEX RANGE SCAN DESCENDING| IDX_BLOG_STATUS | 1429K| 23M| 12935 |
----------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
--------------------------
 
1 - filter("from$_subquery$_001"."RN">=450)
2 - filter(ROWNUM<=500)
4 - access("B"."STATUS"=0)
 
Note: cpu costing IS off
 
19 rows selected.
 
Elapsed: 00:00:00.08
10:34:25 SQL> SET autot traceonly
10:35:33 SQL> SELECT thread_id
10:35:35 2 FROM (SELECT thread_id, rownum rn
10:35:35 3 FROM (SELECT /*+index( b IDX_BLOG_STATUS)*/b.thread_id
10:35:35 4 FROM test_blog b
10:35:35 5 WHERE b.STATUS =0
10:35:35 6 ORDER BY b.gmt_modified DESC)
10:35:35 7 WHERE rownum <= 500)
10:35:35 8 WHERE rn >= 450;
 
51 rows selected.
 
Elapsed: 00:00:52.97
 
Execution Plan
----------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=10499 Card=500 Bytes=13000)
0 VIEW (Cost=10499 Card=500 Bytes=13000)
1 COUNT (STOPKEY)
2 VIEW (Cost=10499 Card=1429964 Bytes=18589532)
3 SORT (ORDER BY STOPKEY) (Cost=10499 Card=1429964 Bytes=24309388) 
4 INDEX(RANGE SCAN) OF 'IDX_BLOG_STATUS' (NON-UNIQUE)(Cost=12935Card=1429964Bytes=24309388) 
 
Statistics
------------------------------
0 recursive calls
0 db block gets
43836 consistent gets
43058 physical reads
7988 redo size
1530 bytes sent via SQL*Net TO client
689 bytes received via SQL*Net FROM client
5 SQL*Net roundtrips TO/FROM client
1 sorts (memory)
0 sorts (disk)
51 rows processed
 
实际走得就有问题了，多了个sort

hint加得有点问题,改成/*+ index_desc( b IDX_BLOG_STATUS) */，因为都能在索引中完成(包括sort)，不用回表。

10:47:18 SQL> SELECT thread_id
10:47:19 2 FROM (SELECT thread_id, rownum rn
10:47:19 3 FROM (SELECT /*+index_desc( b IDX_BLOG_STATUS)*/b.thread_id
10:47:19 4 FROM test_blog b
10:47:19 5 WHERE b.STATUS =0
10:47:19 6 ORDER BY b.gmt_modified DESC)
10:47:19 7 WHERE rownum <= 500)
10:47:19 8 WHERE rn >= 450;
 
51 rows selected.
 
Elapsed: 00:00:06.81
 
Execution Plan
------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5174 Card=500 Bytes=13000)
1 0 VIEW (Cost=5174 Card=500 Bytes=13000)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=5174 Card=1429964 Bytes=18589532)
4 3 INDEX (RANGE SCAN DESCENDING) OF 'IDX_BLOG_STATUS' (NON-UNIQUE)
 (Cost=12935 Card=1429964 Bytes=24309388)
 
Statistics
-----------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1529 bytes sent via SQL*Net TO client
689 bytes received via SQL*Net FROM client
5 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
51 rows processed