大家帮忙看看是sql语句问题，还是资料库需要调整

wtdragon

资料库sga1.7g
现在有一个表的数据有32000笔资料
语句如下
表mic_news上substr(classcode,0,8)，hit列上已加过索引，主键在id上
select  id,
       title,
       addtime,
       hit,
       CONTENT,
       classcode,
       typeid
  from (select  *
          from mic_news
         where substr(classcode, 0, 8) = 00140019
         order by hit desc)
where rownum <= 10

执行时间为10秒左右

下面是explain plan

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=113 Card=10 Byte
          s=275400)

   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=113 Card=100 Bytes=275400)
   3    2       SORT (ORDER BY STOPKEY) (Cost=3665 Card=3255 Bytes=524
          055)

   4    3         TABLE ACCESS (FULL) OF 'MIC_NEWS' (Cost=3585 Card=32
          55 Bytes=524055)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      58731  consistent gets         
         4  physical reads
          0  redo size
       8674  bytes sent via SQL*Net to client
       3433  bytes received via SQL*Net from client
         32  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

强制使用索引语句如下

select  id,
       title,
       addtime,
       hit,
       CONTENT,
       classcode,
       typeid
  from (select /*+index(m) */ *
          from mic_news m
         where substr(classcode, 0, 8) = 00140019
         order by hit desc)
where rownum <= 10
这个时候使用的索引为主键上的索引，执行时间超过20秒，强制使用index(mic_news,idx_hit)以及index(mic_news,idx_substr(classcode,0,8))没有任何效果
explain plan 如下

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=8440 Card=10 Byt
          es=275400)

   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=8440 Card=100 Bytes=275400)
   3    2       SORT (ORDER BY STOPKEY) (Cost=274711 Card=3255 Bytes=5
          24055)

   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'MIC_NEWS' (Cost=27
          4631 Card=3255 Bytes=524055)

   5    4           INDEX (FULL SCAN) OF 'PK_MIC_NEWS' (UNIQUE) (Cost=
          681 Card=325506)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    274658  consistent gets        
          4  physical reads
          0  redo size
       8679  bytes sent via SQL*Net to client
       3433  bytes received via SQL*Net from client
         32  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed
从explain plan里面能发现consistent gets很高
各位大侠，是语句有问题吗？

棉花糖ONE

select  id,
       title,
       addtime,
       hit,
       CONTENT,
       classcode,
       typeid
  from (select  *
          from mic_news
         where substr(classcode, 0, 8) = 00140019
         order by hit desc)
where rownum <= 10

语句改成

select  id,
       title,
       addtime,
       hit,
       CONTENT,
       classcode,
       typeid
  from (select  *
          from mic_news
         where substr(classcode, 0, 8) = '00140019'
         order by hit desc)
where rownum <= 10

创建(substr(classcode,0,8),hit desc)上的组合索引

wtdragon

原帖由 棉花糖ONE 于 2009-4-7 18:56 发表
select  id,
       title,
       addtime,
       hit,
       CONTENT,
       classcode,
       typeid
  from (select  *
          from mic_news
         where substr(classcode, 0, 8) = 00140019
         order by hit desc)
where rownum  

之前就已经加过复合索引，在substr(classcode, 0, 8) 以及hit上
但是根据你写的语句修改后
将substr(classcode, 0, 8) = 00140019 加上单引号后
explain plan如下，那个classcode列为varchar型的，难道是因为字符型转换的问题？还是怎样

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=4 Card=10 Bytes=
          275400)

   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=4 Card=100 Bytes=275400)
   3    2       SORT (ORDER BY STOPKEY) (Cost=27 Card=837 Bytes=134757
          )

   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'MIC_NEWS' (Cost=5
          Card=837 Bytes=134757)

   5    4           INDEX (RANGE SCAN) OF 'IDX_MIC_NEWS_C_H' (NON-UNIQ
          UE) (Cost=2 Card=837)





Statistics
----------------------------------------------------------
          3  recursive calls
          0  db block gets
        115  consistent gets      
         4  physical reads
          0  redo size
       8674  bytes sent via SQL*Net to client
       3433  bytes received via SQL*Net from client
         32  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         10  rows processed

棉花糖ONE

原帖由 wtdragon 于 2009-4-7 19:08 发表


之前就已经加过复合索引，在substr(classcode, 0, 8) 以及hit上
但是根据你写的语句修改后
将substr(classcode, 0, 8) = 00140019 加上单引号后
explain plan如下，那个classcode列为varchar型的，难道是因为字符型转换的问题？还是怎样

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=4 Card=10 Bytes=
          275400)

   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=4 Card=100 Bytes=275400)
   3    2       SORT (ORDER BY STOPKEY) (Cost=27 Card=837 Bytes=134757
          )

   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'MIC_NEWS' (Cost=5
          Card=837 Bytes=134757)

   5    4           INDEX (RANGE SCAN) OF 'IDX_MIC_NEWS_C_H' (NON-UNIQ
          UE) (Cost=2 Card=837)





Statistics
----------------------------------------------------------
          3  recursive calls
          0  db block gets
        115  consistent gets      
         4  physical reads
          0  redo size
       8674  bytes sent via SQL*Net to client
       3433  bytes received via SQL*Net from client
         32  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         10  rows processed

是的,因为隐式转换导致的,你建立的索引没有避免排序,改成
(substr(classcode, 0, 8) ,hit desc)

wtdragon

原帖由 棉花糖ONE 于 2009-4-7 19:11 发表


是的,因为隐式转换导致的,你建立的索引没有避免排序,改成
(substr(classcode, 0, 8) ,hit desc)

就是在这个
(substr(classcode, 0, 8) ,hit desc) 上面加的索引
还是不能避免sort
现在的速度能到1.3秒
如果能避免排序的话，速度还会有提升吧，应该~~~
谢谢棉花糖大侠~~

棉花糖ONE

我测试了一下前面的如果是函数这时候没法避免排序,我怀疑是个bug,你把语句改成如下

select  id,
       title,
       addtime,
       hit,
       CONTENT,
       classcode,
       typeid
  from (select  *
          from mic_news
         where substr(classcode, 0, 8) = '00140019'
         order by substr(classcode, 0, 8),hit desc)
where rownum <= 10

应该是不影响你的逻辑的

wtdragon

原帖由 棉花糖ONE 于 2009-4-7 19:31 发表
我测试了一下前面的如果是函数这时候没法避免排序,我怀疑是个bug,你把语句改成如下

select  id,
       title,
       addtime,
       hit,
       CONTENT,
       classcode,
       typeid
  from (select  *
          from mic_news
         where substr(classcode, 0, 8) = '00140019'
         order by substr(classcode, 0, 8),hit desc)
where rownum  

谢谢棉花糖大侠，使用你写的语句后的explain plan如下
consistent gets已经降到最低
这个时候已经没有了sort


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=10 Bytes=
          275400)

   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=1 Card=100 Bytes=275400)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'MIC_NEWS' (Cost=5 Ca
          rd=837 Bytes=134757)

   4    3         INDEX (RANGE SCAN) OF 'IDX_MIC_NEWS_C_H' (NON-UNIQUE
          ) (Cost=2 Card=837)





Statistics
----------------------------------------------------------
          3  recursive calls
          0  db block gets
         47  consistent gets
          4  physical reads
          0  redo size
       8674  bytes sent via SQL*Net to client
       3433  bytes received via SQL*Net from client
         32  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

棉花糖ONE

ok,执行计划满足了

wtdragon

以下是我的总结，就是要使用索引让consistent gets降至最低，不知道对不对~~

棉花糖ONE

11g已经解决这问题了



SQL> create table test_sort(name,object_name) as select 'select',object_name fro
m
  2  dba_objects;

表已创建。

SQL> create index i_test_sort_1 on test_sort(substr(name,0,3),object_name);

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'TEST_SORT');

PL/SQL 过程已成功完成。

SQL> set autot trace
SQL> select * from (select * from test_sort where substr(name,0,3)='sel' order b
y
  2  object_name) where rownum<11;

已选择10行。


执行计划
----------------------------------------------------------
Plan hash value: 4154852915

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

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

| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%C

PU)| Time     |

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

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

|   0 | SELECT STATEMENT              |               |    10 |   740 |     8
(0)| 00:00:01 |

|*  1 |  COUNT STOPKEY                |               |       |       |
   |          |

|   2 |   VIEW                        |               |    10 |   740 |     8
(0)| 00:00:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| TEST_SORT     |    10 |   350 |     8
(0)| 00:00:01 |

|*  4 |     INDEX RANGE SCAN          | I_TEST_SORT_1 |       |       |     3
(0)| 00:00:01 |

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

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


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

   1 - filter(ROWNUM<11)
   4 - access(SUBSTR("NAME",0,3)='sel')


统计信息
----------------------------------------------------------
        140  recursive calls
          0  db block gets
        372  consistent gets
          0  physical reads
          0  redo size
        696  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL> select * from v$version;

BANNER
----------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production