課程實踐（二）續

案例二

點選(此處)摺疊或開啟

--drop_customers_indexes.sql. 刪除customers表上除主鍵索引外的所有索引

SET TERMOUT OFF

--儲存當前SQL*Plus系統變數的設定

STORE SET sqlplus_settings replace

--保護SQL*Plus buffer

SAVE buffer.sql replace

SET TIMING OFF HEADING OFF VERIFY OFF AUTOTRACE OFF FEEDBACK OFF

--儲存生成的SQL語句

SPOOL dait.sql

SELECT \'DROP INDEX \' || i.index_name || \';\' FROM user_indexes i

WHERE i.table_name = \'CUSTOMERS\'

AND NOT EXISTS

   (SELECT \'x\' FROM user_constraints c

    WHERE c.index_name = i.index_name AND c.table_name = i.table_name AND c.status = \'ENABLED\');

SPOOL OFF

@dait

--恢復SQL*Plus buffer

GET buffer.sql NOLIST

--恢復SQL*Plus系統變數設定

@sqlplus_settings

SET TERMOUT ON

--執行drop_customers_indexes.sql指令碼

sh@TESTDB11>@drop_customers_indexes.sql

--query00.sql. 沒有索引走全表掃描,成本比較高

SET ECHO ON

SET TIMING ON

SET AUTOTRACE TRACEONLY

SET PAGESIZE 1000

@flush.sql

SELECT /*+ FULL(c) */ c.* FROM customers c

WHERE cust_gender = \'M\' AND cust_postal_code = 40804 AND cust_credit_limit = 10000;

SET TIMING OFF

SET AUTOTRACE OFF

--執行query00.sql,

sh@TESTDB11>@query00.sql

點選(此處)摺疊或開啟

--在CUSTOMERS表上建立三個索引,並檢視

sh@TESTDB11>SET ECHO ON

sh@TESTDB11>CREATE INDEX idx_cust_gender ON customers (cust_gender) NOLOGGING COMPUTE STATISTICS;

sh@TESTDB11>CREATE INDEX idx_cust_postal_code ON CUSTOMERS(cust_postal_code) NOLOGGING COMPUTE STATISTICS;

sh@TESTDB11>CREATE INDEX idx_cust_credit_limit ON CUSTOMERS(cust_credit_limit) NOLOGGING COMPUTE STATISTICS;

SELECT ui.table_name, DECODE(ui.index_type, \'NORMAL\', ui.uniqueness, ui.index_type) AS index_type,

       ui.index_name

FROM user_indexes ui WHERE ui.table_name = \'CUSTOMERS\'

ORDER BY ui.table_name, ui.uniqueness DESC;

點選(此處)摺疊或開啟

--開始監視customers表上的索引

sh@TESTDB11>ALTER INDEX customers_pk MONITORING USAGE;

sh@TESTDB11>ALTER INDEX idx_cust_gender MONITORING USAGE;

sh@TESTDB11>ALTER INDEX idx_cust_credit_limit MONITORING USAGE;

sh@TESTDB11>ALTER INDEX idx_cust_postal_code MONITORING USAGE;

--檢視索引使用情況

sh@TESTDB11>SELECT * FROM v$object_usage;

點選(此處)摺疊或開啟

--query01.sql

--優化器選擇使用一個索引, 走INDEX FULL SCAN, 成本比全表掃描低.

SET ECHO ON

SET TIMING ON

SET AUTOTRACE TRACEONLY

SET PAGESIZE 1000

@flush.sql

SELECT /*+ INDEX(c) */ c.* FROM customers

WHERE cust_gender = \'M\' AND cust_postal_code = 40804 AND cust_credit_limit = 10000;

SET TIMING OFF

SET AUTOTRACE OFF

--執行query01.sql指令碼,

sh@TESTDB11>@query01.sql

點選(此處)摺疊或開啟

--query02.sql

--走點陣圖索引,比全表掃描還慢

SET ECHO ON

SET TIMING ON

SET AUTOTRACE TRACEONLY

SET PAGESIZE 1000

@flush.sql

SELECT /*+ INDEX_COMBINE(c) */ c.* FROM customers c

WHERE cust_gender = \'M\' AND cust_postal_code = 40804 AND cust_credit_limit = 10000;

SET TIMING OFF

SET AUTOTRACE OFF

點選(此處)摺疊或開啟

--檢視索引的使用情況

sh@TESTDB11>SELECT * FROM v$object_usage;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17013648/viewspace-1072461/，如需轉載，請註明出處，否則將追究法律責任。

課程實踐（二）續

案例二

相關文章