案例二

点击(此处)折叠或打开

--drop_customers_indexes.sql. 删除customers表上除主键索引外的所有索引

SET TERMOUT OFF

--保存当前SQL*Plus系统变量的设置

STORE SET sqlplus_settings replace

--保护SQL*Plus buffer

SAVE buffer.sql replace

SET TIMING OFF HEADING OFF VERIFY OFF AUTOTRACE OFF FEEDBACK OFF

--保存生成的SQL语句

SPOOL dait.sql

SELECT \'DROP INDEX \' || i.index_name || \';\' FROM user_indexes i

WHERE i.table_name = \'CUSTOMERS\'

AND NOT EXISTS

   (SELECT \'x\' FROM user_constraints c

    WHERE c.index_name = i.index_name AND c.table_name = i.table_name AND c.status = \'ENABLED\');

SPOOL OFF

@dait

--恢复SQL*Plus buffer

GET buffer.sql NOLIST

--恢复SQL*Plus系统变量设置

@sqlplus_settings

SET TERMOUT ON

--执行drop_customers_indexes.sql脚本

sh@TESTDB11>@drop_customers_indexes.sql

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

SET ECHO ON

SET TIMING ON

SET AUTOTRACE TRACEONLY

SET PAGESIZE 1000

@flush.sql

SELECT /*+ FULL(c) */ c.* FROM customers c

WHERE cust_gender = \'M\' AND cust_postal_code = 40804 AND cust_credit_limit = 10000;

SET TIMING OFF

SET AUTOTRACE OFF

--执行query00.sql,

sh@TESTDB11>@query00.sql

点击(此处)折叠或打开

--在CUSTOMERS表上创建三个索引,并查看

sh@TESTDB11>SET ECHO ON

sh@TESTDB11>CREATE INDEX idx_cust_gender ON customers (cust_gender) NOLOGGING COMPUTE STATISTICS;

sh@TESTDB11>CREATE INDEX idx_cust_postal_code ON CUSTOMERS(cust_postal_code) NOLOGGING COMPUTE STATISTICS;

sh@TESTDB11>CREATE INDEX idx_cust_credit_limit ON CUSTOMERS(cust_credit_limit) NOLOGGING COMPUTE STATISTICS;

SELECT ui.table_name, DECODE(ui.index_type, \'NORMAL\', ui.uniqueness, ui.index_type) AS index_type,

       ui.index_name

FROM user_indexes ui WHERE ui.table_name = \'CUSTOMERS\'

ORDER BY ui.table_name, ui.uniqueness DESC;

点击(此处)折叠或打开

--开始监视customers表上的索引

sh@TESTDB11>ALTER INDEX customers_pk MONITORING USAGE;

sh@TESTDB11>ALTER INDEX idx_cust_gender MONITORING USAGE;

sh@TESTDB11>ALTER INDEX idx_cust_credit_limit MONITORING USAGE;

sh@TESTDB11>ALTER INDEX idx_cust_postal_code MONITORING USAGE;

--查看索引使用情况

sh@TESTDB11>SELECT * FROM v$object_usage;

点击(此处)折叠或打开

--query01.sql

--优化器选择使用一个索引, 走INDEX FULL SCAN, 成本比全表扫描低.

SET ECHO ON

SET TIMING ON

SET AUTOTRACE TRACEONLY

SET PAGESIZE 1000

@flush.sql

SELECT /*+ INDEX(c) */ c.* FROM customers

WHERE cust_gender = \'M\' AND cust_postal_code = 40804 AND cust_credit_limit = 10000;

SET TIMING OFF

SET AUTOTRACE OFF

--执行query01.sql脚本,

sh@TESTDB11>@query01.sql

点击(此处)折叠或打开

--query02.sql

--走位图索引,比全表扫描还慢

SET ECHO ON

SET TIMING ON

SET AUTOTRACE TRACEONLY

SET PAGESIZE 1000

@flush.sql

SELECT /*+ INDEX_COMBINE(c) */ c.* FROM customers c

WHERE cust_gender = \'M\' AND cust_postal_code = 40804 AND cust_credit_limit = 10000;

SET TIMING OFF

SET AUTOTRACE OFF

点击(此处)折叠或打开

--查看索引的使用情况

sh@TESTDB11>SELECT * FROM v$object_usage;

ITPub博客

课程实践（二）续

案例二

最新文章