ITPub博客

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

课程实践(二)

原创 Oracle 作者:luisedalian 时间:2014-01-18 11:07:04 0 删除 编辑
使用不同的访问路径

案例一

点击(此处)折叠或打开

  1. --执行ap_setup.sh脚本搭建环境
  2. # ap_setup.sh脚本
  3. # !/bin/bash
  4. sqlplus / as sysdba <<EOF

  5. alter user sh identified by sh account unlock;
  6. grant dba to sh;
  7. exit;
  8. EOF

  9. --以SH登录,执行脚本idx_setup.sql
  10. sh@TESTDB11>@idx_setup.sql
  11. -- idx_setup.sql
  12. DROP TABLE mysales PURGE;
  13. CREATE TABLE mysales AS SELECT * FROM sh.sales;
  14. INSERT INTO mysales SELECT * FROM mysales;
  15. COMMIT;
  16. INSERT INTO mysales SELECT * FROM mysales;
  17. COMMIT;
  18. INSERT INTO mysales SELECT * FROM mysales;
  19. COMMIT;
  20. INSERT INTO mysales SELECT * FROM mysales;
  21. COMMIT;
  22. INSERT INTO mysales SELECT * FROM mysales;
  23. COMMIT;
  24. INSERT INTO mysales VALUES (0, 0, SYSDATE, 0, 0, 0, 0);
  25. COMMIT;

  26. EXEC dbms_stats.gather_schema_stats(\'SH\');

  27. --with_and_without_index.sql
  28. SET ECHO ON
  29. SET TIMING ON
  30. SET AUTOTRACE TRACE ONLY
  31. SET PAGESIZE 1000

  32. ALTER SYSTEM FLUSH SHARED_POOL;
  33. ALTER SYSTEM FLUSH BUFFER_CACHE;

  34. select * from mysales where prod_id = 0;

  35. SET TIMING OFF;
  36. SET AUTOTRACE OFF;

  37. --执行with_and_without_index.sql脚本
  38. sh@TESTDB11>@with_and_without_index.sql

  39. Elapsed: 00:00:41.19

  40. Execution Plan
  41. ----------------------------------------------------------
  42. Plan hash value: 3597614299

  43. -----------------------------------------------------------------------------
  44. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  45. -----------------------------------------------------------------------------
  46. | 0 | SELECT STATEMENT | | 402K| 11M| 40161 (1)| 00:08:02 |
  47. |* 1 | TABLE ACCESS FULL| MYSALES | 402K| 11M| 40161 (1)| 00:08:02 |
  48. -----------------------------------------------------------------------------

  49. Predicate Information (identified by operation id):
  50. ---------------------------------------------------

  51.    1 - filter(\"PROD_ID\"=0)


  52. Statistics
  53. ----------------------------------------------------------
  54.          45 recursive calls
  55.           1 db block gets
  56.      142137 consistent gets
  57.      142055 physical reads
  58.           0 redo size
  59.         970 bytes sent via SQL*Net to client
  60.         523 bytes received via SQL*Net from client
  61.           2 SQL*Net roundtrips to/from client
  62.           5 sorts (memory)
  63.           0 sorts (disk)
  64.           1 rows processed
  65.           

  66. -- create_mysales_index.sql

  67. SET ECHO ON
  68. CREATE INDEX idx_mysales_prodid on mysales(prod_id) nologging compute statistics;

  69. --再次执行with_and_without_index.sql脚本
  70. sh@TESTDB11>@with_and_without_index.sql

  71. Elapsed: 00:00:00.49

  72. Execution Plan
  73. ----------------------------------------------------------
  74. Plan hash value: 2732191377

  75. --------------------------------------------------------------------------------------------------
  76. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  77. --------------------------------------------------------------------------------------------------
  78. | 0 | SELECT STATEMENT | | 402K| 11M| 6003 (1)| 00:01:13 |
  79. | 1 | TABLE ACCESS BY INDEX ROWID| MYSALES | 402K| 11M| 6003 (1)| 00:01:13 |
  80. |* 2 | INDEX RANGE SCAN | IDX_MYSALES_PRODID | 402K| | 821 (1)| 00:00:10 |
  81. --------------------------------------------------------------------------------------------------

  82. Predicate Information (identified by operation id):
  83. ---------------------------------------------------

  84.    2 - access(\"PROD_ID\"=0)


  85. Statistics
  86. ----------------------------------------------------------
  87.          51 recursive calls
  88.           0 db block gets
  89.         110 consistent gets
  90.          21 physical reads
  91.           0 redo size
  92.         974 bytes sent via SQL*Net to client
  93.         523 bytes received via SQL*Net from client
  94.           2 SQL*Net roundtrips to/from client
  95.           6 sorts (memory)
  96.           0 sorts (disk)
  97.           
  98. --创建idx_cleanup.sql脚本,清理环境
  99. --idx_cleanup.sql
  100. SET ECHO ON
  101. DROP TABLE mysales purge;

  102. --执行idx_cleanup.sql
  103. sh@TESTDB11>@idx_cleanup.sql
结论:当数据量大,而检索的数据又比较少时,使用索引的效率会更好。

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

请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    1986
  • 访问量
    5643026