首页 > 代码库 > 怎样让索引仅仅能被一个SQL使用
怎样让索引仅仅能被一个SQL使用
有个徒弟问我,要创建一个索引,去优化一个SQL,可是创建了索引之后其它 SQL 也要用 这个索引,其它SQL慢死了。要优化的SQL又快。遇到这样的问题咋搞?
一般遇到这样的问题还是非常少的。处理的方法非常多。我简单的给大家介绍一种方法。
还是直接看我实验操作步骤吧。
在SCOTT账户里面创建一个測试表和一个索引 SQL> create table test as select * from dba_objects; 表已创建。 SQL> create index idx_test on test(object_id); 索引已创建。
SQL> set lines 200 pages 200 SQL> set autot trace SQL> select * from test where object_id=10; 运行计划 ---------------------------------------------------------- Plan hash value: 2473784974 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 207 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_TEST | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=10) Note ----- - dynamic sampling used for this statement (level=6) 统计信息 ---------------------------------------------------------- 44 recursive calls 0 db block gets 136 consistent gets 4 physical reads 0 redo size 1404 bytes sent via SQL*Net to client 420 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed然后人工设置索引统计信息 把集群银子搞大(很大) SQL> begin 2 dbms_stats.set_index_stats(ownname => ‘SCOTT‘, 3 indname => ‘IDX_TEST‘, 4 numrows => 100000000000, 5 numlblks => 100000, 6 numdist => 100000, 7 avglblk => 100000, 8 avgdblk => 100000, 9 clstfct => 100000000000); 10 end; 11 / PL/SQL 过程已成功完毕。SQL> select * from test where object_id=10; 运行计划 ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 207 | 290 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| TEST | 1 | 207 | 290 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=10) Note ----- - dynamic sampling used for this statement (level=6) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 1039 consistent gets 0 physical reads 0 redo size 1404 bytes sent via SQL*Net to client 420 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed这个时候。全部的SQL都不会走这个索引了,你想让某个SQL走索引。直接hint 让它走就ok了SQL> select /*+ index(test idx_test) */ * from test where object_id=10; 运行计划 ---------------------------------------------------------- Plan hash value: 2473784974 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 207 | 1446K (1)| 04:49:20 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 207 | 1446K (1)| 04:49:20 | |* 2 | INDEX RANGE SCAN | IDX_TEST | 1 | | 15 (94)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=10) Note ----- - dynamic sampling used for this statement (level=6) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1404 bytes sent via SQL*Net to client 420 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
这样还没完,由于要是有人收集了统计信息,会覆盖我们set的统计信息,所以收集完统计信息之后,要再跑一下那个set的统计信息就ok了。
怎样让索引仅仅能被一个SQL使用
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。