联全索引的用法

Question

本帖最后由 jixueyou 于 2010-05-08 18:43:04 编辑

Answer 1

两千多万数据,K002中也有百万左右的数据。我现在的问题是：当我只用k002时根本就没有用到索引，数据库进行的是全表扫描。但当我用到索引中的多个字段时索引才能起作用。例如：k002='2' and k003='2 

Answer 2

建立索引你建立了7个字段的联合索引，建立联合索引的目的是大部分情况下where条件全用到的，而你只用到后置列，为什么要建全部的联合索引呢？where k002='2',因为没有用到前导列，所以走FFS，很正常
where  k002='2' and k003='2'用到了索引，应该是走index skip scan的，因为k001的数据分布种类很少
你应该收集了统计信息，嘿嘿
你如果只需要这样的两种查询，那么可以对(k002,k003)建立索引
数据量那么大，建立全量索引的存储要多少？维护起来要有多少开销？不是说不能建立7个字段的联合索引，是要考虑有没有必要的问题

Answer 3

原因不外乎“不能使用索引 或者 使用索引会返回不正确的结果“，或者”不应该使用，如果使用了索引，性能会变得很糟糕“。首先要明白：如果能证明使用这个索引后确实会加快速度（通过耗用的时间和I/O 次数来判断），oracle才会用。其次，你要知道索引的机制，它是如果定位的，oracle是以block为最小单位，读取和存储也显然是以block为单位。下面来看这样一个简化的例子，假设我们通过索引读取一个瘦表，而且要读取表中 20%的行。若这
个表中有 100,000 行，其中的 20%就是 2,000 行。如果行大小约为 80 字节，在一个块大小为 8KB 的数
据库中，每个块上则有大约 100 行。这说明，这个表有大约 11.000 个块。了解了执行情况，计算起来就
非常容易了。我们要通过索引读取20,000 行；这说明，大约是20,000 个TABLE ACCESS BY ROWID操
作。为此要处理20,000个表块来执行这个查询。不过，整个表才有大约11.000个块！最后会把表中的每
一个块读取好处理20次。即使把行的大小提高一个数量级，达到每行800字节，这样每块有11.行，现在
表中就有 11.,000 个块。要通过索引访问 20,000 行，仍要求我们把每一个块平均读取 2 次。在这种情况
下，全表扫描就比使用索引高效得多，因为每个块只会命中一次。如果查询使用这个索引来访问数据，效
率都不会高，除非对于800 字节的行，平均只访问表中不到5%的数据（这样一来，就只会访问大约5,000
个块） ，如果是80字节的行，则访问的数据应当只占更小的百分比（大约0.5%或更少） 。 补充一点，你这几个字段不知道是什么类型的，如果是number的，要想用索引就别加单引号。要细分析，oracle不使用索引的原因就多了，可以分好多情况，你的k002='2'不使用索引而用全表扫描是很可能的，原因就是k001的值的唯一性很好，你换成k001='2'很可能就会使用了，也就是说，假如k001的值比如说都是 2,而k002的值几乎都不同，那我想对这个表你再用k002时就会用索引了。
至于k002='2' and k003='2'时用了索引，这就看具体情况了，显然oracle优化器认为通过索引定位这2个字段所在的块然后再读取这些块 比  直接读全表的块要I/0更少，所以oracle利用了索引。另外，显然你也需要对复合索引的机制了解一下：http://q.yesky.com/group/review-11028252.html

Answer 4

前导索引好像就是这样的，如果可以走index skip scan,必须用多个非前导列的情况才有可能，单个列走不到index skip scan的，以前做过试验，要走的话只能用hint了，但是效率不一定高

Answer 5

上面说错了，比如索引（a,b,c）,unique a的数量是2，unqiue b的数量是10000，unique c的也是10000
那么where b = and c= 是可以走index skip scan的，where b= 也是一样的，但是where c= 就走不了索引了但是改为unique b的数量比如是100，unqiue c的数量是10000，那么where c= 是有可能走index skip scan的你的k002不走索引，可能是k002的unique数量和k001差不了多少，所以走不了索引，k003unique数量和k001unique应该相差比较大

Answer 6

---case1:联合索引，非前导列不走索引情况----------------
DINGJUN123>drop table test;表已删除。DINGJUN123>CREATE TABLE test AS
  2      SELECT ROWNUM a,ROWNUM-1 b ,ROWNUM-2 c,ROWNUM-3 d,ROWNUM-4 e
  3      FROM all_objects;表已创建。DINGJUN123>SELECT  COUNT (DISTINCT a) FROM test;COUNT(DISTINCTA)
----------------
           13344DINGJUN123>select  COUNT( DISTINCT b) FROM test;COUNT(DISTINCTB)
----------------
           13344DINGJUN123>CREATE INDEX test_idx ON test(a,b,c);索引已创建。DINGJUN123>begin
  2   dbms_stats.gather_table_stats(ownname => user,tabname => 'TEST');
  3   dbms_stats.gather_index_stats(ownname => user,indname => 'TEST_IDX');
  4  end;
  5  /PL/SQL 过程已成功完成。DINGJUN123>set autotrace traceonly
DINGJUN123>SELECT *  FROM test WHERE b = 99
  2  ;
执行计划
----------------------------------------------------------
Plan hash value: 1357081020--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    21 |    18   (6)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    21 |    18   (6)| 00:00:01 |
--------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter("B"=99)
----------------------------case2：联合索引非前导列走索引------------------------
DINGJUN123>drop table test;表已删除。DINGJUN123>CREATE TABLE test
  2      AS
  3      SELECT DECODE(MOD(ROWNUM,2), 0, '1', '2' ) a,
  4                        ROWNUM-1 b,
  5                        ROWNUM-2 c,
  6                        ROWNUM-3 d,
  7                        ROWNUM-4 e
  8        FROM all_objects;表已创建。DINGJUN123>
DINGJUN123>select distinct a from test;A
--
1
2DINGJUN123>select count(distinct b) from test;COUNT(DISTINCTB)
----------------
           13347
DINGJUN123>CREATE INDEX test_idx ON test(a,b,c);索引已创建。DINGJUN123>begin
  2   dbms_stats.gather_table_stats(ownname => user,tabname => 'TEST');
  3   dbms_stats.gather_index_stats(ownname => user,indname => 'TEST_IDX');
  4  end;
  5  /PL/SQL 过程已成功完成。DINGJUN123>set autotrace traceonly
DINGJUN123>SELECT *  FROM test WHERE b = 99;
执行计划
----------------------------------------------------------
Plan hash value: 2705879578----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    18 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    18 |     4   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | TEST_IDX |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - access("B"=99)
       filter("B"=99)
DINGJUN123>select *  from test where c=99;
执行计划
----------------------------------------------------------
Plan hash value: 1357081020--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    18 |    17   (6)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    18 |    17   (6)| 00:00:01 |
--------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter("C"=99)
---------------------下面测试，c用到了索引,单独的b不走索引，和你的情况有点类似,原因是a和b数量相当，但是c的数量比a和b多多了--------
DINGJUN123>drop table test;表已删除。DINGJUN123>CREATE TABLE test
  2      AS
  3      SELECT DECODE(MOD(ROWNUM,2), 0, '1', '2' ) a,
  4                        DECODE(MOD(ROWNUM,2), 0, '1', '2' ) b,
  5                        ROWNUM-2 c,
  6                        ROWNUM-3 d,
  7                        ROWNUM-4 e
  8        FROM all_objects;表已创建。DINGJUN123>CREATE INDEX test_idx ON test(a,b,c);索引已创建。DINGJUN123>begin
  2   dbms_stats.gather_table_stats(ownname => user,tabname => 'TEST');
  3   dbms_stats.gather_index_stats(ownname => user,indname => 'TEST_IDX');
  4  end
  5  ;
  6  /PL/SQL 过程已成功完成。DINGJUN123>set autotrace traceonly
DINGJUN123>select * from test where b=0;未选定行
执行计划
----------------------------------------------------------
Plan hash value: 1357081020--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    16 |    15   (7)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    16 |    15   (7)| 00:00:01 |
--------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter(TO_NUMBER("B")=0)DINGJUN123>select * from test where  c =99;
执行计划
----------------------------------------------------------
Plan hash value: 2705879578----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    16 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    16 |     4   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | TEST_IDX |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - access("C"=99)
       filter("C"=99)
DINGJUN123>select * from test where b=0 and c=99;未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2705879578----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    16 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    16 |     4   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | TEST_IDX |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - access("C"=99)
       filter("C"=99 AND TO_NUMBER("B")=0)

Answer 7

http://soft.chinabyte.com/307/11162307.shtml