nested loop心得
今天調優SQL,語句如下:
select a.seq_captureway as CAPTUREWAYNO,
a.dd_captureway as CAPTUREWAYCODE,
a.seq_animalmoni as ANIMALMONINO,
b.nm_worker as WORKERNM
from rat_caveflea a, rat_worker b
where a.seq_worker = b.seq_worker(+)
and a.seq_animalmoni = '11308'
and a.ZONECODE like '53%'
原來的執行計劃有下面一段
NESTED LOOPS OUTER
TABLE ACCESS FULL RAT_CAVEFLEA FILTER("A"."SEQ_ANIMALMONI"='11308' AND "A"."ZONECODE" LIKE '53%')
TABLE ACCESS BY INDEX ROWID REDFIS RAT_WORKER
INDEX UNIQUE SCAN PK_RAT_WORKER access("A"."SEQ_WORKER"="B"."SEQ_WORKER"(+))
新增索引create index IDX_rat_caveflea_AZ on rat_caveflea(seq_animalmoni,ZONECODE),rat_caveflea 表沒有全表掃描了。
發現:雖然有a.seq_worker = b.seq_worker(+),但是在巢狀迴圈裡,a表只要能通過其他過濾條件過濾就行了,在2個表關聯的地方沒有必要建立索引。
測試:建立索引 create index IDX_rat_caveflea_AZW on rat_caveflea(seq_animalmoni,ZONECODE,seq_worker)nologging;分析表後,發現還是用的索引IDX_rat_caveflea_AZ。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25027760/viewspace-743144/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- HASH JOIN ,MERGE JOIN ,NESTED LOOPOOP
- nested loop,sort merge join,hash joinOOP
- 【sql調優之執行計劃】nested loops join and nested loop join outerSQLOOP
- 資料庫實現原理#1(Nested Loop Join)資料庫OOP
- HASH JOIN ,MERGE JOIN ,NESTED LOOP(R2)OOP
- HASH JOIN ,MERGE JOIN ,NESTED LOOP的比較OOP
- first_rows更傾向使用nested loop操作。OOP
- Mysql join 的本質,Nested Loop Join 和 Hash JoinMySqlOOP
- 【MySQL】MySQL效能優化之Block Nested-Loop Join(BNL)MySql優化BloCOOP
- 【MySQL】MySQL效能最佳化之Block Nested-Loop Join(BNL)MySqlBloCOOP
- 表的連線方式:NESTED LOOP、HASH JOIN、SORT MERGE JOIN(轉)OOP
- 多表連線的三種方式詳解 hash join、merge join、 nested loopOOP
- for loopOOP
- ElasticSearch多層nested查詢、nested過濾排除非結果內容Elasticsearch
- Event LoopOOP
- 資料庫表--nested table資料庫
- NESTED LOOPS 成本計算OOP
- 【筆記】forall and nested table and cursor筆記
- event loop整理OOP
- node event loopOOP
- javascript - event loopJavaScriptOOP
- JavaScript Event LoopJavaScriptOOP
- Ruby loop 方法OOP
- treevalue——Master Nested Data Like TensorAST
- [轉]Trees in SQL: Nested Sets and Materialized PathSQLZed
- 瀏覽器的event loop和node的event loop瀏覽器OOP
- 瀏覽器event loop和node的event loop講解瀏覽器OOP
- 淺談event loopOOP
- Performance Without the Event LoopORMOOP
- 月度文章——Event LoopOOP
- JavaScript與Event LoopJavaScriptOOP
- Event Loop個人理解OOP
- Javascript之Event LoopJavaScriptOOP
- Daniel Powter: Free LoopOOP
- nested exception is IncompleteElementException: Could not find parameter mapException
- LeetCode-Flatten Nested List IteratorLeetCode
- 請教關於JdonNews中的nested
- [心得] Effective Java心得筆記Java筆記