在生產庫看到這樣一條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也不可信呀，關鍵還是要看實際跑得怎麼樣!