sql优化高手进，帮忙分析下这条sql的执行效率

Question

SQL> select count(*) from bibliosM a,(select * from (select recordid,rownum rn from (select distinct b.recordid, a.indexkey from bibliosM_publishyear a,(select distinct recordid  from bibliosM a, bibliosM_TITLE,holding h where bibliosM_TITLE.Indexkey like 'JSP%'  and bibliosM_TITLE.recordid = a.id  and h.metatable=80000002 and h.metaid=a.id and ((local='3Z' and shelfno<>0 and serviceaddr is null)) and (a.secret = 0 or a.secret is null)) b where b.recordid = a.recordid order by substr(a.indexkey,1,4) desc, b.recordid desc )where rownum <= 20) where rn >= 1) b where a.id = b.recordid ;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4885 Card=1 Bytes=30
          )   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=4885 Card=20 Bytes=600)
   3    2       VIEW (Cost=4884 Card=20 Bytes=520)
   4    3         COUNT (STOPKEY)
   5    4           VIEW (Cost=4884 Card=1167 Bytes=15171)
   6    5             SORT (ORDER BY STOPKEY) (Cost=4884 Card=1167 Byt
          es=227565)   7    6               SORT (UNIQUE) (Cost=4846 Card=1167 Bytes=22756
          5)   8    7                 HASH JOIN (Cost=4808 Card=1167 Bytes=227565)
   9    8                   HASH JOIN (Cost=4573 Card=1167 Bytes=19605
          6)  10    9                     HASH JOIN (Cost=4527 Card=17129 Bytes=42
          8225)  11   10                       TABLE ACCESS (BY INDEX ROWID) OF 'HOLD
          ING' (Cost=32 Card=17129 Bytes=342580)  12   11                         INDEX (RANGE SCAN) OF 'HOLDING_LOCAL
          ' (NON-UNIQUE) (Cost=1 Card=91642)  13   10                       TABLE ACCESS (FULL) OF 'BIBLIOSM' (Cos
          t=4448 Card=623672 Bytes=3118360)  14    9                     TABLE ACCESS (BY INDEX ROWID) OF 'BIBLIO
          SM_TITLE' (Cost=10 Card=42490 Bytes=6076070)  15   14                       INDEX (RANGE SCAN) OF 'BIBLIOSM_TITLE_
          IDX' (NON-UNIQUE) (Cost=2 Card=1)  16    8                   TABLE ACCESS (FULL) OF 'BIBLIOSM_PUBLISHYE
          AR' (Cost=216 Card=287514 Bytes=7762878)  17    2       INDEX (UNIQUE SCAN) OF 'SYS_C001468' (UNIQUE) (Cost=1
          Card=1 Bytes=4)Statistics
----------------------------------------------------------
         18  recursive calls
          0  db block gets
     155190  consistent gets
      21251  physical reads
          0  redo size
        518  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
          1  rows processed
这条sql还是比较慢的。望达人指点下。

Answer 1

TABLE ACCESS (FULL) OF 'BIBLIOSM' (Cost=4448 Card=623672 Bytes=3118360) 这块cost 比较大. 4448， 可以走索引..
------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群：62697716 

Answer 2

兄弟，不是我打击你，你的sql写的很差，你先优化一下逻辑结构。

Answer 3

       TABLE ACCESS (FULL) OF 'BIBLIOSM' (Cos
          t=4448 Card=623672 Bytes=3118360) 
          
          TABLE ACCESS (FULL) OF 'BIBLIOSM_PUBLISHYE
          AR' (Cost=216 Card=287514 Bytes=7762878) 从上面2处来看，没有走索引，走的是全表扫描，而且看这2个表的数据量还是非常的大的，楼主可以检查下这2处地方为什么没有走索引。

Answer 4

/* Formatted on 2009-12-7 16:18:45 (QP5 v5.115.810.9015) */
SELECT   COUNT ( * )
  FROM   bibliosM a,
         (SELECT   *
            FROM   (SELECT   recordid, ROWNUM rn
                      FROM   (  SELECT   DISTINCT b.recordid, a.indexkey
                                  FROM   bibliosM_publishyear a,
                                         (SELECT   DISTINCT recordid
                                            FROM   bibliosM a,
                                                   bibliosM_TITLE,
                                                   holding h
                                           WHERE   bibliosM_TITLE.Indexkey LIKE
                                                      'JSP%'
                                                   AND bibliosM_TITLE.recordid =
                                                         a.id
                                                   AND h.metatable = 80000002
                                                   AND h.metaid = a.id
                                                   AND ( (local = '3Z'
                                                          AND shelfno <> 0
                                                          AND serviceaddr IS NULL))
                                                   AND (a.secret = 0
                                                        OR a.secret IS NULL)) b
                                 WHERE   b.recordid = a.recordid
                              ORDER BY   SUBSTR (a.indexkey, 1, 4) DESC,
                                         b.recordid DESC)
                     WHERE   ROWNUM <= 20)
           WHERE   rn >= 1) b
 WHERE   a.id = b.recordid;
格式排版了下，楼主下面这块是重点优化对象，select 嵌套的太多，SQL 尽量分开来写。下面部分SQL 还是限制索引的，就是加了索引，也不一定会走索引。如果该SQL 不方便，就用hint 强制走索引看看.
/* Formatted on 2009-12-7 16:25:35 (QP5 v5.115.810.9015) */
  SELECT   DISTINCT b.recordid, a.indexkey
    FROM   bibliosM_publishyear a,
           (SELECT   DISTINCT recordid
              FROM   bibliosM a, bibliosM_TITLE, holding h
             WHERE       bibliosM_TITLE.Indexkey LIKE 'JSP%'
                     AND bibliosM_TITLE.recordid = a.id
                     AND h.metatable = 80000002
                     AND h.metaid = a.id
                     AND ( (    local = '3Z'
                            AND shelfno <> 0
                            AND serviceaddr IS NULL))
                     AND (a.secret = 0 OR a.secret IS NULL)) b
   WHERE   b.recordid = a.recordid
ORDER BY   SUBSTR (a.indexkey, 1, 4) DESC, b.recordid DESC
Oracle 限制索引 
http://blog.csdn.net/tianlesoftware/archive/2009/10/14/4671674.aspx
------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群：62697716 

Answer 5

例如 DISTINCT  order by 都比较影响执行效率

Answer 6

很奇怪，如果不查holding这个表再加上/*+RULE*/就很快，其他都是一样SQL> SELECT /*+RULE*/  DISTINCT b.recordid, a.indexkey
  2      FROM   bibliosM_publishyear a,
  3             (SELECT   DISTINCT recordid
  4                FROM   bibliosM a, bibliosM_TITLE
  5               WHERE       bibliosM_TITLE.Indexkey LIKE 'JSP%'
  6                       AND bibliosM_TITLE.recordid = a.id
  7                       AND (a.secret = 0 OR a.secret IS NULL)) b
  8     WHERE   b.recordid = a.recordid
  9  ORDER BY   SUBSTR (a.indexkey, 1, 4) DESC, b.recordid DESC;124 rows selected.
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   SORT (ORDER BY)
   2    1     SORT (UNIQUE)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'BIBLIOSM_PUBLISHYEAR
          '   4    3         NESTED LOOPS
   5    4           NESTED LOOPS
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'BIBLIOSM_TITLE
          '   7    6               INDEX (RANGE SCAN) OF 'BIBLIOSM_TITLE_IDX' (NO
          N-UNIQUE)   8    5             TABLE ACCESS (BY INDEX ROWID) OF 'BIBLIOSM'
   9    8               INDEX (UNIQUE SCAN) OF 'SYS_C001468' (UNIQUE)
  10    4           INDEX (RANGE SCAN) OF 'BIBLIOSM_PUBLISHYEAR_REC' (
          NON-UNIQUE)Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        912  consistent gets
          0  physical reads
          0  redo size
       3359  bytes sent via SQL*Net to client
        744  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        124  rows processed
但是如果加上holding这个表，使用/*+RULE*/反而更慢。

Answer 7

你的SQL逻辑太乱套了 自己先改一下
select count(*) from ......
select * from  .......
这样的语句都会影响效率的
建议改成
select count(rowid) from ......
select 列1,列2,列3,列4,列5,.... from  .......

Answer 8

最后优化SQLSELECT DISTINCT i.recordid,y.indexkey
   FROM   bibliosM_TITLE i, bibliosM a, bibliosM_publishyear y
   WHERE  1<=(SELECT COUNT(id) FROM holding WHERE shelfno <> 0 AND serviceaddr IS NULL AND metatable=80000002 AND metaid=i.recordid AND LOCAL='3Z')
     AND i.Indexkey LIKE 'JSP%'
       AND y.recordid=i.recordid
       AND i.recordid = a.id
       order by y.indexkey desc
执行时间仅为1.3s，比原来的3.5s快了一倍不止。
谢谢大家，散分结贴。