# 课程实践（二）续

1. --执行drop_customers_indexes.sql脚本, 删除customers表上的所有索引
2. --创建复合索引
3. CREATE INDEX idx_gender_limit_code ON customers(cust_gender, cust_credit_limit, cust_postal_code)
4. NOLOGGING COMPUTE STATISTICS;

1. --query03.sql, 与query01.sql的区别就是删除一个谓词.
2. SET ECHO ON
3. SET TIMING ON
4. SET AUTOTRACE TRACEONLY
5. SET PAGESIZE 1000

6. @flush.sql
7. SELECT /*+ INDEX(c) */ c.* FROM customers c WHERE cust_gender = \'M\' AND cust_credit_limit = 10000;

8. SET TIMING OFF
9. SET AUTOTRACE OFF

10. --执行query03.sql
11. --程序虽然使用了复合索引,但cust_credit_limit不具有很好的选择度,所以成本会高很多
12. sh@TESTDB11>@query03.sql

1. --query04.sql
2. --将上一步中cust_credi_limit替换成了cust_postal_code, 后者的可选择度更好,所以成本降低。
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 c
9. WHERE cust_gender = \'M\' AND cust_postal_code = 40804;

10. SET TIMING OFF
11. SET AUTOTRACE OFF

12. --执行query04.sql
13. sh@TESTDB11>@query04.sql

1. --query05.sql.
2. --此时索引的前导部分不再是查询的一部分,但优化程序仍能使用些索引. 但成本不是最低的.
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 c WHERE cust_postal_code = 40804 AND cust_credit_limit = 10000;

9. SET TIMING OFF
10. SET AUTOTRACE OFF

• 博文量
1986
• 访问量
5642205