Oracle Explain plan使用總結

寫多了SQL語句，伴随着資料量的海增，總會遇到性能的問題。在Oracle領域一個不好的習慣，一旦遇到性能問題就推給DBA來做。長期如此，反而對DBA的工作感到神秘。至少筆者所在機關就是如此，DBA向來是牛氣沖天的。

　　要調整SQL語句的性能，就得知道這條SQL語句花費了多少COST。Explain plan工具可幫我們分析這些工作。而調整SQL語句的性能，肯定要涉及索引了。Oracle索引比較常用的有二種，1.B-TREE索引，B-TREE 适用于值變化較多的列，2.BITMAP索引。BITMAP适用于值變化較少的列（少于300個值），比如：性别這樣的列。

　　有了上述基礎就可以開始優化工作了。工具：pl/sql developer。

　　1.建表

　　Java代碼

　　create table HEK_TEST_IN

　　(

　　PID   INTEGER primary key,

　　NDATE DATE,

　　NNOTE VARCHAR2(50)

　　)

　　create table HEK_TEST_INDETAIL

　　(

　　PID   INTEGER not null,

　　FID   INTEGER,

　　NNAME VARCHAR2(50),

　　NQTY  FLOAT,

　　NNOTE VARCHAR2(50),

　　NSIZE VARCHAR2(20)

　　);

　　alter table HEK_TEST_INDETAIL

　　add constraint FK_TEST_1 foreign key (FID)

　　references HEK_TEST_IN (PID);

　　create index HEK_TEST_INDETAIL_INDEX on HEK_TEST_INDETAIL (NNAME, NSIZE);

　　2.測試具體SQL語句到底有沒有使用index。

　　2.1條件查詢:

　　select *  from hek_test_in where pid=3

　　Explain Paln輸出;

　　SELECT STATEMENT, GOAL = CHOOSE

　　TABLE ACCESS BY INDEX ROWID    Object owner=APPS    Object name=HEK_TEST_IN

　　INDEX UNIQUE SCAN    Object owner=APPS    Object name=SYS_C00211467

　　分析得出:hek_test_in查詢時使用索引掃描，為什麼呢？因為我們建立表時，指定Primary Key時，Oracel會自動建立一個UNIQUE INDEX。

　　-------------------------------------------------------------------------------

　　2.2連接配接查詢:

　　select * from hek_test_in a,hek_test_indetail b where a.pid=b.fid;

　　Explain Paln輸出;

　　SELECT STATEMENT, GOAL = CHOOSE

　　NESTED LOOPS

　　TABLE ACCESS FULL    Object owner=APPS    Object name=HEK_TEST_INDETAIL

　　TABLE ACCESS BY INDEX ROWID    Object owner=APPS    Object name=HEK_TEST_IN

　　INDEX UNIQUE SCAN    Object owner=APPS    Object name=SYS_C00211467

　　分析得出:hek_test_in查詢時使用索引掃描，而HEK_TEST_INDETAIL使用全表掃描。

　　-----------------------------------------------------------------------------

　　2.3組合索引的條件查詢：

　　select *  from hek_test_indetail where nname = ''

　　Explain Paln輸出;

　　SELECT STATEMENT, GOAL = CHOOSE

　　TABLE ACCESS BY INDEX ROWID    Object owner=APPS    Object name=HEK_TEST_INDETAIL

　　INDEX RANGE SCAN    Object owner=APPS    Object name=HEK_TEST_INDETAIL_INDEX

　　分析得出:查詢時使用組合索引掃描。注：組合索引跟建立的列順序有關，如果條件語句換成where nsize='',也會導至全表掃描。

　　－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

　　2.4組合索引的排序查詢：

　　select *  from hek_test_indetail order by nname

　　Explain Paln輸出;

　　SELECT STATEMENT, GOAL = CHOOSE

　　SORT ORDER BY

　　TABLE ACCESS FULL    Object owner=APPS    Object name=HEK_TEST_INDETAIL

　　分析得出:排序查詢時無法使用組合索引，進而導緻全表掃描。

　　2.5 基于NULL條件查詢：

　　select *  from hek_test_indetail where nname is null

　　Explain Paln輸出;

　　SELECT STATEMENT, GOAL = CHOOSE

　　TABLE ACCESS FULL    Object owner=APPS    Object name=HEK_TEST_INDETAIL

　　分析得出:NULL查詢導緻全表掃描。與此類似的還有is not null,<>也會導至全表掃描。