高手帮忙看一个表索引问题。用索引查询反而更慢了

Question

-- Create table
create table Test
(
  UA              NUMBER(20) not null,
  PPID            NUMBER(8) not null,
  MSG_ID          NUMBER(5) not null,
  PRIORITY        NUMBER(2) default 64,
  LASTSEND_DATE   DATE not null,
  MSG_DATA        VARCHAR2(2048) not null,
  MSG_INLIST      NUMBER(1) default 0,
  MODIFY_PRIORITY NUMBER(2) default 60 not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );-- Create/Recreate primary, unique and foreign key constraints 
alter table Test
  add constraint Test_KEY primary key (UA, PPID, MSG_ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );-- Create/Recreate indexes 
create index Test_INDEX on Test (MODIFY_PRIORITY, LASTSEND_DATE)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
我用如下语句查询，select * from test where MSG_INLIST = 0 order by modify_priority,lastsend_date  1000万数据执行要1个小时，为什么呢，我是用索引排序的啊

Answer 1

 MSG_INLIST = 0有多少数据量。
你建的索引，然后这个查询sql，可能连index skip scan都用不上。

Answer 2

你对你的sql执行下计划分析吧，看你的sql语句 * 以及 1000万这2个特征，很有可能走的是全表扫描，而不是索引扫描。这样的话，索引是不起作用的。一般来讲，好像是在10%(不一定准备，通常是这样的)以上的数据容量就开始走表扫描，索引无效！

Answer 3

MSG_INLIST 字段又没在索引里，当然不会走索引了

Answer 4

MSG_INLIST      NUMBER(1) default 0, 
MODIFY_PRIORITY NUMBER(2) default 60 not null 建立在这两个字段上的复合B树索引,对查询性能的提高没有什么作用.因为,这两个字段的选择性及数据分布性不高.

Answer 5

和 MSG_INLIST 选择性不高有关系，返回行数太多，使用索引反而会降低效率。

Answer 6

ORACLE方面针对优化的一些常识, 在COST模式下, 系统会根据情况,自动判断出比较优化的执行计划,并非建立索引就一定
会使用索引. 当然,在 ORACLE 9I之前, 默认的RULE模式下, 会使用索引的一些观念仍然在很多的地方流传,并影响很多人.随着ORACLE版本的变化,原来的一些想法和认识已经片面和过时.这个贴子具有一定典型代表,值得推荐.

Answer 7

LZ提供的信息太少，总结了一下，可能性有以下几点：
1.返回数据集数量超过数据总量的7%；
2.ORDER BY 索引字段而WHERE后面无索引字段的查询不走索引；
如需更详细的分析，请贴出执行计划

Answer 8

select * from test 
你把where条件去掉,order by全部去调,执行全表查询,看下速度如何

Answer 9

ORDER BY 索引字段而WHERE后面无索引字段的查询不走索引
建议贴上执行计划
估计是MSG_INLIST = 0造成速度很慢你 用MODIFY_PRIORITY =60 替换MSG_INLIST = 0，试试时间长短？

Answer 10

1. MSG_INLIST的字段类型是number，且长度为1，那么也就是说这个字段的值只可能是0~9的10个数字，也就是说一般数据分布均匀的情况下楼主访问了大约10%的数据量
   MSG_INLIST      NUMBER(1) default 0 2. where MSG_INLIST = 0 Where条件中的MSG_INLIST 并不出现在任何索引中，所以查询的时候肯定走了全表扫描3. 在1千万的数据量下，全表扫描1千万并排序，效率肯定不会快

Answer 11

create index Test_INDEX on Test (LASTSEND_DATE) 
建议这样试试，再看看执行计划，索引这事有时候得试

Answer 12

select  *
  from TestCSND20090804
 where MSG_INLIST = 0
 order by modify_priority, lastsend_date我用XP 10G走的全表扫描！
建议sql改写成select /*+index_asc(TestCSND20090804,Test_INDEX)*/
 *
  from TestCSND20090804
 where MSG_INLIST = 0

Answer 13

MSG_INLIST没有创建索引，造成全表扫描，在通过索引进行排序！

Answer 14

索引会对Where条件中用到的字段其作用给MSG_INLIST字段创建索引
create index index_MSG_INLIST on Test (MSG_INLIST);

Answer 15

根据经验，对于超大的表。检索出的记录超过总记录数的20%时，用fts反而比用索引快。

Answer 16

你的索引对你的查询基本不能起到提高性能，查询大量数据时甚至会严重影响性能。
原因在于：
1.排序可以考虑在使用提示，如快速全索引扫描select /*+index_ffs(...)*/ * from ...
2.查询你需要的字段，不要用'*'，Oracle会强制转换*为表中的字段名。
3.where 条件跟索引无关，导致索引无效。这样不但用不上索引，反而会因索引的创建占用(大量)空间，以及维护索引在性能上的消耗。
不妨删除你建的那两个索引，在MSG_INLIST字段上建立索引，通过/*+index_ffs(...)*/试试。(不太确定你这个字段的取值是怎样的，只能先给个粗略的建议，敬请谅解)

Answer 17

这种问题没必要再关注了.
没用到索引....你跟踪下索引就知道了....
没对WHERE后的字段建立索引...全表扫描,再加上ORDER BY ,比慢还要慢...

Answer 18

顶七楼等。
MSG_INLIST 都没有被包括在索引内。
索引列只有在搜索时加在Where条件中，才会起作用。
还有就是你选中的数据很多时，当然也会很慢。
显示数据的速度与选中的列数和行数都有一定关系，列数越多，速度越慢，同样，行数越多，速度越慢。

Answer 19

看看索引的原理吧。MSG_INLIST NUMERIC(1),基本你的数据规律性很大，这样的字段是不适合建立索引的。

Answer 20

select * from table要是没有where 条件的话，一般是走的full table scan
用select index_column from table这个就会走索引

Answer 21

如果你用MSG_INLIST查询的多的话，还是给这个字段加上索引吧。
还有加hint吧，觉得这才最直接的办法。

Answer 22

如果你要在MSG_INLIS 建立索引，请建立位图索引。我想你这个 MSG_INLIS 列主要是描述 一些状态用的

Answer 23

全表扫描并不是一定就不好，最关键是排序太耗时了，PGA太小就要走外部排序，如果临时表空间也很小的话那这SQL执行1个小时也可以理解。

Answer 24

条件没用上索引
order by 也 没用上索引

Answer 25

索引里又没有MSG_INLIST 这个字段，怎么会用到
执行计划看看吧

Answer 26

1.索引开启否，取决于“索引优化器”。只有当查询的数据类型总数<5%, 索引优化器才会启用索引；否则只用全文搜索。
2.order by 也没用上索引. where modify_priority = '**' and lastsend_date = '**'，这样modify_priority,lastsend_date才会用上索引。

Answer 27

在CBO的优化模式下，我们可以使用optimizer_mode参数控制优化模式。主要有两种模式，一种是ALL_ROWS模式，另外一种是FIRST_ROWS模式。ALL_ROWS模式适用场景：希望优化程序给出一种尽快得到全部记录的执行计划，目标是增加系统的吞吐量。
FIRST_ROWS模式使用场景：希望优化程序给出一种可以迅速的得到第一行的执行计划，目标是减少系统的响应时间。两种模式需要具体场景具体分析，比如常见的Web应用，很少有一次性得到全部记录的情况，都是分多页交互的响应操作者，因此默认的ALL_ROWS模式就不太适合了，应该考虑使用FIRST_ROWS模式进行优化。
又如，我们想要生成全部数据的报表，那么默认的ALL_ROWS模式就比较的适合。通过一个实验看一下两种优化模式下的执行计划的不同之处。
1.默认情况下，数据库采用ALL_ROWS模式。
sec@ora10g> show parameter optimizer_modeNAME                TYPE                 VALUE
------------------- -------------------- -----------------
optimizer_mode      string               ALL_ROWS2.创建千万级别的测试表t，开启autotrace，查看一下默认ALL_ROWS模式下的执行计划。
sec@ora10g> set autot trace explain
sec@ora10g> select t1.x, t2.x from t t1, t t2 where t1.x = t2.x and t1.owner='SEC';Execution Plan
----------------------------------------------------------
Plan hash value: 2371815244--------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      | 99695 |  2823K|       | 43627   (1)| 00:08:44 |
|*  1 |  HASH JOIN            |      | 99695 |  2823K|  3408K| 43627   (1)| 00:08:44 |
|*  2 |   TABLE ACCESS FULL   | T    | 99695 |  2239K|       | 29985   (1)| 00:06:00 |
|   3 |   INDEX FAST FULL SCAN| PK_T |  9969K|    57M|       |  4871   (2)| 00:00:59 |
--------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - access("T1"."X"="T2"."X")
   2 - filter("T1"."OWNER"='SEC')优化程序给出了一个快速获得t表全部记录的执行计划，使用到了索引快速全扫描的方式执行，总的执行时间较快。3.修改优化模式为FIRST_ROWS模式后，再次查询其执行计划。
sec@ora10g> alter session set optimizer_mode =first_rows;Session altered.sec@ora10g> select t1.x, t2.x from t t1, t t2 where t1.x = t2.x and t1.owner='SEC';Execution Plan
----------------------------------------------------------
Plan hash value: 217223811---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 99695 |  2823K|   129K  (1)| 00:25:57 |
|   1 |  NESTED LOOPS      |      | 99695 |  2823K|   129K  (1)| 00:25:57 |
|*  2 |   TABLE ACCESS FULL| T    | 99695 |  2239K| 29985   (1)| 00:06:00 |
|*  3 |   INDEX UNIQUE SCAN| PK_T |     1 |     6 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - filter("T1"."OWNER"='SEC')
   3 - access("T1"."X"="T2"."X")优化程序给出了一种快速获得t表第一条记录的执行计划，使用到了索引唯一性扫描的方式执行，总的执行时间相对ALL_ROWS模式就长了许多。4.参考一下Oracle 10g官方文档关于optimizer_mode参数的描述
OPTIMIZER_MODEProperty Description
Parameter type String
Syntax OPTIMIZER_MODE ={ first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows }
Default value all_rows
Modifiable ALTER SESSION,ALTER SYSTEMOPTIMIZER_MODEestablishes the default behavior. for choosing an optimization approach for the instance.Values:first_rows_nThe optimizer uses a cost-based approach and optimizes with a goal of best response time to return the firstnrows (wheren= 1, 10, 100, 1000).first_rowsThe optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.all_rowsThe optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).5.小结
这种优化手段给我们的启示是什么？Oracle默认的优化模式并不一定是我们想要的，必须根据自己的系统特点细心的定制。
Oracle的自动化进程越来越快，这就给一些DBA一种普遍的误解，认为在数据库层面上基本上不用做过多的优化调整，只要按照Oracle的自动化策略走就可以了。这种想法是不正确的。越是自动化，其优化细节就隐藏的越深，越是要静下心来深入的探索和调整。

Answer 28

一句话  没有走索引 而是全表扫描 因为你的索引字段没有出现在where关键字后面,作为查询条件.

Answer 29

create index Test_INDEX on Test (MODIFY_PRIORITY, LASTSEND_DATE)
索引建得不对。。除非你查询这个字段同时使用你这个索引才会有效.分开建成两个索引就可以了

Answer 30

我感觉对MSG_INLIST建bitmap索引试试，因为对应每个MSG_INLIST的值有很大的数据量

Answer 31

更改一下索引试一下
create index Test_INDEX1 on Test (MSG_INLIST, MODIFY_PRIORITY, LASTSEND_DATE)

Answer 32

怎么又被推荐了呢？哈哈，LZ去MSDN干活，顺便把自己从前的贴推荐下？

Answer 33

create index dec_idx on Test(decode(MSG_INLIST,0,0)) 这样 select * from Test where decode(MSG_INLIST,0,0) = 0 就快了。   测试下哦，我只是看Tom的书，随便想到的