为什么加了索引执行更慢了呢？

[yangchao_713]

表结构如下：

-- Create table
create table T_GG_MOBILENO_SENDABLE
(
  sendable_id          NUMBER(10) not null,
  mobileno_id          NUMBER(10),
  mobileno             NUMBER(11),
  mobileno_kind        NUMBER(4),
  mobileno_age         NUMBER(4),
  mobileno_money       NUMBER(8),
  mobileno_area        NVARCHAR2(6),
  mobileno_sex         NUMBER(1),
  mobileno_double      NUMBER(1),
  mobileno_treble      NUMBER(1)
)
tablespace GXT
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index INDEX_T_GG_MOBILENO_AGE on T_GG_MOBILENO_SENDABLE (MOBILENO_AGE)
  tablespace GXT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index INDEX_T_GG_MOBILENO_AREA on T_GG_MOBILENO_SENDABLE (MOBILENO_AREA)
  tablespace GXT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index INDEX_T_GG_MOBILENO_DOUBLE on T_GG_MOBILENO_SENDABLE (MOBILENO_DOUBLE)
  tablespace GXT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index INDEX_T_GG_MOBILENO_KIND on T_GG_MOBILENO_SENDABLE (MOBILENO_KIND)
  tablespace GXT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index INDEX_T_GG_MOBILENO_MONEY on T_GG_MOBILENO_SENDABLE (MOBILENO_MONEY)
  tablespace GXT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index INDEX_T_GG_MOBILENO_SENDABLE on T_GG_MOBILENO_SENDABLE (MOBILENO)
  tablespace GXT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index INDEX_T_GG_MOBILENO_SEX on T_GG_MOBILENO_SENDABLE (MOBILENO_SEX)
  tablespace GXT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index INDEX_T_GG_MOBILENO_TREBLE on T_GG_MOBILENO_SENDABLE (MOBILENO_TREBLE)
  tablespace GXT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_GG_MOBILENO_SENDABLE
  add constraint PK_T_GG_MOBILENO_SENDABLE primary key (SENDABLE_ID)
  using index 
  tablespace GXT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

SQL语句：

select  count( distinct( MOBILENO))
  from t_gg_mobileno_sendable a
 WHERE (MOBILENO_KIND = 1
        and MOBILENO_AGE >= 0 and MOBILENO_AGE <= 99 and
       MOBILENO_MONEY between 0 and 999)

Where后面的条件是动态的 所以有的索引暂时没有用到

这是为什么捏？如果对count加强制索引 怎么加？

[Dave]

贴下执行计划.

[Dave]

把表分析一下，在看看执行计划，是否有变。

[我本是朱]

索引的规则挺多的，应该是某些索引失效

[SKY_4K_PPM]

MOBILENO_AGE
MOBILENO_MONEY 的查询条件都要+ >0 的条件么？

[加油馒头]

还是看下执行计划 看下究竟慢在哪里

[jdsnhan]

同上。没有环境分析不出来，贴出执行计划帮你分析

[Rotel-刘志东]

这必须看一下执行计划才能知道的。

[cosio]

最好把前后的执行计划贴出来,看一下!

[tiangtangcao]

楼主贴一下执行计划。

关于在count中添加索引的，是需要使用到函数索引的。

可以参考：http://bbs.51cto.com/thread-26271-1.html

[yangchao_713]

执行计划

[yangchao_713]

这个吧

Execution Plan
----------------------------------------------------------
Plan hash value: 1954617196

--------------------------------------------------------------------------------
------------------------

| Id  | Operation                    | Name                    | Rows  | Bytes |
 Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
------------------------

|   0 | SELECT STATEMENT             |                         |     1 |    19 |
     0   (0)| 00:00:01 |

|   1 |  SORT GROUP BY               |                         |     1 |    19 |
            |          |

|*  2 |   TABLE ACCESS BY INDEX ROWID| T_GG_MOBILENO_SENDABLE  |     3 |    57 |
     0   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | INDEX_T_GG_MOBILENO_AGE |     1 |       |
     0   (0)| 00:00:01 |

--------------------------------------------------------------------------------
------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MOBILENO_MONEY"<=999 AND "MOBILENO_KIND"=1 AND "MOBILENO_MONEY">=
0)

   3 - access("MOBILENO_AGE">=0 AND "MOBILENO_AGE"<=99)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    2204994  consistent gets
      99408  physical reads
          0  redo size
        429  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

[yangchao_713]

这个吧

Execution Plan
----------------------------------------------------------
Plan hash value: 1954617196

--------------------------------------------------------------------------------
------------------------

| Id  | Operation                    | Name                    | Rows  | Bytes |
 Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
------------------------

|   0 | SELECT STATEMENT             |                         |     1 |    19 |
     0   (0)| 00:00:01 |

|   1 |  SORT GROUP BY               |                         |     1 |    19 |
            |          |

|*  2 |   TABLE ACCESS BY INDEX ROWID| T_GG_MOBILENO_SENDABLE  |     3 |    57 |
     0   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | INDEX_T_GG_MOBILENO_AGE |     1 |       |
     0   (0)| 00:00:01 |

--------------------------------------------------------------------------------
------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MOBILENO_MONEY"<=999 AND "MOBILENO_KIND"=1 AND "MOBILENO_MONEY">=
0)

   3 - access("MOBILENO_AGE">=0 AND "MOBILENO_AGE"<=99)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    2204994  consistent gets
      99408  physical reads
          0  redo size
        429  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

[tiangtangcao]

从这个执行计划来说，应该不会很慢啊~~~

物理读在99408 ~~~

楼主，你这个数据量大概有多大？？

[yangchao_713]

[Quote=引用 13 楼 tiangtangcao 的回复:]

从这个执行计划来说，应该不会很慢啊~~~

物理读在99408 ~~~

楼主，你这个数据量大概有多大？？
[/Quote]

整个表800W+ 这个语句执行完查到7500+ 但是执行了4分多种 ！

[yangchao_713]

[Quote=引用 15 楼 tx2730 的回复:]

楼主的索引不会对效率有多大的帮助甚至可能比不走索引更慢,仔细看下便知:MOBILENO_AGE>=0 AND MOBILENO_AGE<=99这个条件估计包含了所有的数据,所以索引在查询的时候不会有什么帮助,再有distinct不会用到索引, 你的MOBILENO建了索引,应改为group by方式
SQL code
SELECT COUNT(MOBILENO)
FROM (SELECT……
[/Quote]

你这种方法也不行啊，会不会跟数据库设置有关系捏？

[007-x]

[Quote=引用 16 楼 yangchao_713 的回复:]
引用 15 楼 tx2730 的回复:

楼主的索引不会对效率有多大的帮助甚至可能比不走索引更慢,仔细看下便知:MOBILENO_AGE>=0 AND MOBILENO_AGE<=99这个条件估计包含了所有的数据,所以索引在查询的时候不会有什么帮助,再有distinct不会用到索引, 你的MOBILENO建了索引,应改为group by方式
SQL code
SELECT COUNT(MO……
[/Quote]
应该是你这句SQL查询的资料太多了,去掉group by,你看下资料量是多少

SELECT A.MOBILENO
          FROM T_GG_MOBILENO_SENDABLE A
         WHERE A.MOBILENO_KIND = 1
           AND MOBILENO_AGE >= 0
           AND A.MOBILENO_AGE <= 99
           AND A.MOBILENO_MONEY BETWEEN 0 AND 999

或者走MOBILENO_KIND的索引试试:

SELECT COUNT(MOBILENO)
  FROM (SELECT A.MOBILENO
          FROM T_GG_MOBILENO_SENDABLE A
         WHERE A.MOBILENO_KIND = 1
           AND MOBILENO_AGE+0 >= 0
           AND A.MOBILENO_AGE+0 <= 99
           AND A.MOBILENO_MONEY+0 BETWEEN 0 AND 999
         GROUP BY A.MOBILENO)

[yangchao_713]

[Quote=引用 17 楼 tx2730 的回复:]

SELECT COUNT(MOBILENO)
  FROM (SELECT A.MOBILENO
          FROM T_GG_MOBILENO_SENDABLE A
         WHERE A.MOBILENO_KIND = 1
           AND MOBILENO_AGE+0 >= 0
           AND A.MOBILENO_AGE+0 <= 99
           AND A.MOBILENO_MONEY+0 BETWEEN 0 AND 999
         GROUP BY A.MOBILENO)

[/Quote]

嗯嗯 这个牛X 哎。。。要恶补数据库呢 方便的话 解释一下？

[BenChiM888]

本身group by 就已经是唯一的了，没有必要再distinct， 加distinct就不要group by
加group by 就不要distinct。


[Quote=引用 18 楼 yangchao_713 的回复:]

[Quote=引用 17 楼 tx2730 的回复:]
SQL code

SELECT COUNT(MOBILENO)
FROM (SELECT A.MOBILENO
FROM T_GG_MOBILENO_SENDABLE A
WHERE A.MOBILENO_KIND = 1
AND MOBILENO_AGE+0 >= 0
……
[/Quote]

[007-x]

从你之前贴的执行计划可以看出来,这句SQL用到的索引是INDEX_T_GG_MOBILENO_AGE,这个字段的所有值估计也就是0~99,也就是说这个表里面所有的数据都要查出来再做下一步的筛选. 因此走这个索引不会提高查询效率(MOBILENO_MONEY同理). MOBILENO_KIND这个字段不知道是做什么用的,但是条件很单一(MOBILENO_KIND=1),可以一试.
那就需要改变原先的执行计划, 改成MOBILENO_AGE+0这样的写法就会使MOBILENO_AGE这个字段的索引失效,强制Oracle使用MOBILENO_KIND字段的索引.我的想法就是这样来的,见笑了.
当然要让Oracle走什么索引也可以用hint.

[007-x]

楼主的索引不会对效率有多大的帮助甚至可能比不走索引更慢,仔细看下便知:MOBILENO_AGE>=0 AND MOBILENO_AGE<=99这个条件估计包含了所有的数据,所以索引在查询的时候不会有什么帮助,再有distinct不会用到索引, 你的MOBILENO建了索引,应改为group by方式

SELECT COUNT(MOBILENO)
  FROM (SELECT A.MOBILENO
          FROM T_GG_MOBILENO_SENDABLE A
         WHERE A.MOBILENO_KIND = 1
           AND MOBILENO_AGE >= 0
           AND A.MOBILENO_AGE <= 99
           AND A.MOBILENO_MONEY BETWEEN 0 AND 999
         GROUP BY A.MOBILENO)