課程實踐（二）續

LuiseDalian發表於2014-01-18

案例二


點選(此處)摺疊或開啟

  1. --drop_customers_indexes.sql. 刪除customers表上除主鍵索引外的所有索引
  2. SET TERMOUT OFF
  3. --儲存當前SQL*Plus系統變數的設定
  4. STORE SET sqlplus_settings replace
  5. --保護SQL*Plus buffer
  6. SAVE buffer.sql replace
  7. SET TIMING OFF HEADING OFF VERIFY OFF AUTOTRACE OFF FEEDBACK OFF
  8. --儲存生成的SQL語句
  9. SPOOL dait.sql
  10. SELECT \'DROP INDEX \' || i.index_name || \';\' FROM user_indexes i
  11. WHERE i.table_name = \'CUSTOMERS\'
  12. AND NOT EXISTS
  13.    (SELECT \'x\' FROM user_constraints c
  14.     WHERE c.index_name = i.index_name AND c.table_name = i.table_name AND c.status = \'ENABLED\');
  15. SPOOL OFF

  17. @dait
  18. --恢復SQL*Plus buffer
  19. GET buffer.sql NOLIST
  20. --恢復SQL*Plus系統變數設定
  21. @sqlplus_settings
  22. SET TERMOUT ON

  24. --執行drop_customers_indexes.sql指令碼
  25. sh@TESTDB11>@drop_customers_indexes.sql

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

  29. SET ECHO ON
  30. SET TIMING ON
  31. SET AUTOTRACE TRACEONLY
  32. SET PAGESIZE 1000

  34. @flush.sql
  35. SELECT /*+ FULL(c) */ c.* FROM customers c
  36. WHERE cust_gender = \'M\' AND cust_postal_code = 40804 AND cust_credit_limit = 10000;

  38. SET TIMING OFF
  39. SET AUTOTRACE OFF

  41. --執行query00.sql,
  42. sh@TESTDB11>@query00.sql


點選(此處)摺疊或開啟

  1. --在CUSTOMERS表上建立三個索引,並檢視
  2. sh@TESTDB11>SET ECHO ON
  3. sh@TESTDB11>CREATE INDEX idx_cust_gender ON customers (cust_gender) NOLOGGING COMPUTE STATISTICS;
  4. sh@TESTDB11>CREATE INDEX idx_cust_postal_code ON CUSTOMERS(cust_postal_code) NOLOGGING COMPUTE STATISTICS;
  5. sh@TESTDB11>CREATE INDEX idx_cust_credit_limit ON CUSTOMERS(cust_credit_limit) NOLOGGING COMPUTE STATISTICS;

  7. SELECT ui.table_name, DECODE(ui.index_type, \'NORMAL\', ui.uniqueness, ui.index_type) AS index_type,
  8.        ui.index_name
  9. FROM user_indexes ui WHERE ui.table_name = \'CUSTOMERS\'
  10. ORDER BY ui.table_name, ui.uniqueness DESC;



點選(此處)摺疊或開啟

  1. --開始監視customers表上的索引
  2. sh@TESTDB11>ALTER INDEX customers_pk MONITORING USAGE;
  3. sh@TESTDB11>ALTER INDEX idx_cust_gender MONITORING USAGE;
  4. sh@TESTDB11>ALTER INDEX idx_cust_credit_limit MONITORING USAGE;
  5. sh@TESTDB11>ALTER INDEX idx_cust_postal_code MONITORING USAGE;

  7. --檢視索引使用情況
  8. sh@TESTDB11>SELECT * FROM v$object_usage;


點選(此處)摺疊或開啟

  1. --query01.sql
  2. --優化器選擇使用一個索引, 走INDEX FULL SCAN, 成本比全表掃描低.
  3. SET ECHO ON
  4. SET TIMING ON
  5. SET AUTOTRACE TRACEONLY
  6. SET PAGESIZE 1000
  7. @flush.sql
  8. SELECT /*+ INDEX(c) */ c.* FROM customers
  9. WHERE cust_gender = \'M\' AND cust_postal_code = 40804 AND cust_credit_limit = 10000;

  11. SET TIMING OFF
  12. SET AUTOTRACE OFF

  14. --執行query01.sql指令碼,
  15. sh@TESTDB11>@query01.sql



點選(此處)摺疊或開啟

  1. --query02.sql
  2. --走點陣圖索引,比全表掃描還慢
  3. SET ECHO ON
  4. SET TIMING ON
  5. SET AUTOTRACE TRACEONLY
  6. SET PAGESIZE 1000

  8. @flush.sql
  9. SELECT /*+ INDEX_COMBINE(c) */ c.* FROM customers c
  10. WHERE cust_gender = \'M\' AND cust_postal_code = 40804 AND cust_credit_limit = 10000;

  12. SET TIMING OFF
  13. SET AUTOTRACE OFF




點選(此處)摺疊或開啟

  1. --檢視索引的使用情況
  2. sh@TESTDB11>SELECT * FROM v$object_usage;


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

相關文章