ITPub博客

首页 > 数据库 > Oracle > 课程实践(二)续

课程实践(二)续

原创 Oracle 作者:luisedalian 时间:2014-01-18 11:17:29 0 删除 编辑

案例二


点击(此处)折叠或打开

  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

  16. @dait
  17. --恢复SQL*Plus buffer
  18. GET buffer.sql NOLIST
  19. --恢复SQL*Plus系统变量设置
  20. @sqlplus_settings
  21. SET TERMOUT ON

  22. --执行drop_customers_indexes.sql脚本
  23. sh@TESTDB11>@drop_customers_indexes.sql

  24. --query00.sql. 没有索引走全表扫描,成本比较高

  25. SET ECHO ON
  26. SET TIMING ON
  27. SET AUTOTRACE TRACEONLY
  28. SET PAGESIZE 1000

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

  32. SET TIMING OFF
  33. SET AUTOTRACE OFF

  34. --执行query00.sql,
  35. 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;

  6. SELECT ui.table_name, DECODE(ui.index_type, \'NORMAL\', ui.uniqueness, ui.index_type) AS index_type,
  7.        ui.index_name
  8. FROM user_indexes ui WHERE ui.table_name = \'CUSTOMERS\'
  9. 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;

  6. --查看索引使用情况
  7. 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;

  10. SET TIMING OFF
  11. SET AUTOTRACE OFF

  12. --执行query01.sql脚本,
  13. sh@TESTDB11>@query01.sql



点击(此处)折叠或打开

  1. --query02.sql
  2. --走位图索引,比全表扫描还慢
  3. SET ECHO ON
  4. SET TIMING ON
  5. SET AUTOTRACE TRACEONLY
  6. SET PAGESIZE 1000

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

  10. SET TIMING OFF
  11. SET AUTOTRACE OFF




点击(此处)折叠或打开

  1. --查看索引的使用情况
  2. sh@TESTDB11>SELECT * FROM v$object_usage;


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17013648/viewspace-1072461/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 课程实践(二)
请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    1986
  • 访问量
    5643017