課程實踐（二）

LuiseDalian發表於2014-01-18
使用不同的訪問路徑

案例一

點選(此處)摺疊或開啟

  1. --執行ap_setup.sh指令碼搭建環境
  2. # ap_setup.sh指令碼
  3. # !/bin/bash
  4. sqlplus / as sysdba <<EOF

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

  11. --以SH登入,執行指令碼idx_setup.sql
  12. sh@TESTDB11>@idx_setup.sql
  13. -- idx_setup.sql
  14. DROP TABLE mysales PURGE;
  15. CREATE TABLE mysales AS SELECT * FROM sh.sales;
  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 SELECT * FROM mysales;
  25. COMMIT;
  26. INSERT INTO mysales VALUES (0, 0, SYSDATE, 0, 0, 0, 0);
  27. COMMIT;

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

  31. --with_and_without_index.sql
  32. SET ECHO ON
  33. SET TIMING ON
  34. SET AUTOTRACE TRACE ONLY
  35. SET PAGESIZE 1000

  37. ALTER SYSTEM FLUSH SHARED_POOL;
  38. ALTER SYSTEM FLUSH BUFFER_CACHE;

  40. select * from mysales where prod_id = 0;

  42. SET TIMING OFF;
  43. SET AUTOTRACE OFF;

  45. --執行with_and_without_index.sql指令碼
  46. sh@TESTDB11>@with_and_without_index.sql

  48. Elapsed: 00:00:41.19

  50. Execution Plan
  51. ----------------------------------------------------------
  52. Plan hash value: 3597614299

  54. -----------------------------------------------------------------------------
  55. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  56. -----------------------------------------------------------------------------
  57. | 0 | SELECT STATEMENT | | 402K| 11M| 40161 (1)| 00:08:02 |
  58. |* 1 | TABLE ACCESS FULL| MYSALES | 402K| 11M| 40161 (1)| 00:08:02 |
  59. -----------------------------------------------------------------------------

  61. Predicate Information (identified by operation id):
  62. ---------------------------------------------------

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


  67. Statistics
  68. ----------------------------------------------------------
  69.          45 recursive calls
  70.           1 db block gets
  71.      142137 consistent gets
  72.      142055 physical reads
  73.           0 redo size
  74.         970 bytes sent via SQL*Net to client
  75.         523 bytes received via SQL*Net from client
  76.           2 SQL*Net roundtrips to/from client
  77.           5 sorts (memory)
  78.           0 sorts (disk)
  79.           1 rows processed
  80.           

  82. -- create_mysales_index.sql

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

  87. --再次執行with_and_without_index.sql指令碼
  88. sh@TESTDB11>@with_and_without_index.sql

  90. Elapsed: 00:00:00.49

  92. Execution Plan
  93. ----------------------------------------------------------
  94. Plan hash value: 2732191377

  96. --------------------------------------------------------------------------------------------------
  97. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  98. --------------------------------------------------------------------------------------------------
  99. | 0 | SELECT STATEMENT | | 402K| 11M| 6003 (1)| 00:01:13 |
  100. | 1 | TABLE ACCESS BY INDEX ROWID| MYSALES | 402K| 11M| 6003 (1)| 00:01:13 |
  101. |* 2 | INDEX RANGE SCAN | IDX_MYSALES_PRODID | 402K| | 821 (1)| 00:00:10 |
  102. --------------------------------------------------------------------------------------------------

  104. Predicate Information (identified by operation id):
  105. ---------------------------------------------------

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


  110. Statistics
  111. ----------------------------------------------------------
  112.          51 recursive calls
  113.           0 db block gets
  114.         110 consistent gets
  115.          21 physical reads
  116.           0 redo size
  117.         974 bytes sent via SQL*Net to client
  118.         523 bytes received via SQL*Net from client
  119.           2 SQL*Net roundtrips to/from client
  120.           6 sorts (memory)
  121.           0 sorts (disk)
  122.           
  123. --建立idx_cleanup.sql指令碼,清理環境
  124. --idx_cleanup.sql
  125. SET ECHO ON
  126. DROP TABLE mysales purge;

  128. --執行idx_cleanup.sql
  129. sh@TESTDB11>@idx_cleanup.sql
結論：當資料量大，而檢索的資料又比較少時，使用索引的效率會更好。

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

相關文章