課程實踐（二）

使用不同的訪問路徑

案例一

點選(此處)摺疊或開啟

--執行ap_setup.sh指令碼搭建環境
# ap_setup.sh指令碼
# !/bin/bash
sqlplus / as sysdba <<EOF
alter user sh identified by sh account unlock;
grant dba to sh;
exit;
EOF
--以SH登入,執行指令碼idx_setup.sql
sh@TESTDB11>@idx_setup.sql
-- idx_setup.sql
DROP TABLE mysales PURGE;
CREATE TABLE mysales AS SELECT * FROM sh.sales;
INSERT INTO mysales SELECT * FROM mysales;
COMMIT;
INSERT INTO mysales SELECT * FROM mysales;
COMMIT;
INSERT INTO mysales SELECT * FROM mysales;
COMMIT;
INSERT INTO mysales SELECT * FROM mysales;
COMMIT;
INSERT INTO mysales SELECT * FROM mysales;
COMMIT;
INSERT INTO mysales VALUES (0, 0, SYSDATE, 0, 0, 0, 0);
COMMIT;
EXEC dbms_stats.gather_schema_stats(\'SH\');
--with_and_without_index.sql
SET ECHO ON
SET TIMING ON
SET AUTOTRACE TRACE ONLY
SET PAGESIZE 1000
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
select * from mysales where prod_id = 0;
SET TIMING OFF;
SET AUTOTRACE OFF;
--執行with_and_without_index.sql指令碼
sh@TESTDB11>@with_and_without_index.sql
Elapsed: 00:00:41.19
Execution Plan
----------------------------------------------------------
Plan hash value: 3597614299
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 402K| 11M| 40161 (1)| 00:08:02 |
|* 1 | TABLE ACCESS FULL| MYSALES | 402K| 11M| 40161 (1)| 00:08:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(\"PROD_ID\"=0)
Statistics
----------------------------------------------------------
45 recursive calls
1 db block gets
142137 consistent gets
142055 physical reads
0 redo size
970 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
-- create_mysales_index.sql
SET ECHO ON
CREATE INDEX idx_mysales_prodid on mysales(prod_id) nologging compute statistics;
--再次執行with_and_without_index.sql指令碼
sh@TESTDB11>@with_and_without_index.sql
Elapsed: 00:00:00.49
Execution Plan
----------------------------------------------------------
Plan hash value: 2732191377
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 402K| 11M| 6003 (1)| 00:01:13 |
| 1 | TABLE ACCESS BY INDEX ROWID| MYSALES | 402K| 11M| 6003 (1)| 00:01:13 |
|* 2 | INDEX RANGE SCAN | IDX_MYSALES_PRODID | 402K| | 821 (1)| 00:00:10 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(\"PROD_ID\"=0)
Statistics
----------------------------------------------------------
51 recursive calls
0 db block gets
110 consistent gets
21 physical reads
0 redo size
974 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
--建立idx_cleanup.sql指令碼,清理環境
--idx_cleanup.sql
SET ECHO ON
DROP TABLE mysales purge;
--執行idx_cleanup.sql
sh@TESTDB11>@idx_cleanup.sql

結論：當資料量大，而檢索的資料又比較少時，使用索引的效率會更好。

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

課程實踐（二）

相關文章