在生产库看到这样一条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
逻辑读从43836下降到10个。
总结：explain plan也不可信呀，关键还是要看实际跑得怎么样!
