ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一次内链子查询优化 2

一次内链子查询优化 2

原创 Linux操作系统 作者:gaopengtttt 时间:2011-04-29 13:08:47 0 删除 编辑
   ----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                        |      1 |        |      0 |00:00:13.11 |    3115K|
|   1 |  SORT ORDER BY                    |                        |      1 |      2 |      0 |00:00:13.11 |    3115K|
|   2 |   CONCATENATION                   |                        |      1 |        |      0 |00:00:13.11 |    3115K|
|*  3 |    FILTER                         |                        |      1 |        |      0 |00:00:06.44 |    1557K|
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  4 |     TABLE ACCESS BY INDEX ROWID   | yy               |      1 |      1 |      0 |00:00:06.44 |    1557K| 6
|*  5 |      INDEX RANGE SCAN             | yy|      1 |    169 |    181K|00:00:05.81 |    1452K| 5
|*  6 |       TABLE ACCESS BY INDEX ROWID | yy            |    181K|      1 |    181K|00:00:02.41 |     725K| 2
|*  7 |        INDEX RANGE SCAN           | yy|    181K|      1 |    181K|00:00:01.69 |     544K| 1
|*  8 |        TABLE ACCESS BY INDEX ROWID| yy            |    181K|      1 |    181K|00:00:02.21 |     725K| 4
|*  9 |         INDEX RANGE SCAN          | yy            |    181K|      2 |    181K|00:00:01.49 |     544K| 3
|  10 |     NESTED LOOPS                  |                        |      0 |      1 |      0 |00:00:00.01 |       0 |
|  11 |      TABLE ACCESS BY INDEX ROWID  | yy              |      0 |      1 |      0 |00:00:00.01 |       0 |
|* 12 |       INDEX UNIQUE SCAN           | yy|      0 |      1 |      0 |00:00:00.01 |       0 |
|* 13 |      TABLE ACCESS FULL            | yy|      0 |      1 |      0 |00:00:00.01 |       0 |
|* 14 |    FILTER                         |                        |      1 |        |      0 |00:00:06.67 |    1557K|
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 15 |     TABLE ACCESS BY INDEX ROWID   | yy               |      1 |      1 |      0 |00:00:06.67 |    1557K|
|* 16 |      INDEX RANGE SCAN             | yy|      1 |    459 |    181K|00:00:05.99 |    1452K|
|* 17 |       TABLE ACCESS BY INDEX ROWID | yy            |    181K|      1 |    181K|00:00:02.53 |     725K|
|* 18 |        INDEX RANGE SCAN           |yy|    181K|      1 |    181K|00:00:01.80 |     544K|
|* 19 |        TABLE ACCESS BY INDEX ROWID| yy           |    181K|      1 |    181K|00:00:02.29 |     725K|
|* 20 |         INDEX RANGE SCAN          | yy            |    181K|      2 |    181K|00:00:01.55 |     544K|
|* 21 |     TABLE ACCESS BY INDEX ROWID   | yy           |    181K|      1 |    181K|00:00:02.41 |     725K|
|* 22 |      INDEX RANGE SCAN             | yy|    181K|      1 |    181K|00:00:01.69 |     544K|
|* 23 |      TABLE ACCESS BY INDEX ROWID  | yy            |    181K|      1 |    181K|00:00:02.21 |     725K|
|* 24 |       INDEX RANGE SCAN            | yy          |    181K|      2 |    181K|00:00:01.49 |     544K|
|  25 |       NESTED LOOPS                |                        |      0 |      1 |      0 |00:00:00.01 |       0 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  26 |        TABLE ACCESS BY INDEX ROWID| yy               |      0 |      1 |      0 |00:00:00.01 |       0 |
|* 27 |         INDEX UNIQUE SCAN         | yy          |      0 |      1 |      0 |00:00:00.01 |       0 |
|* 28 |        TABLE ACCESS FULL          | yy|      0 |      1 |      0 |00:00:00.01 |       0 |
----------------------------------------------------------------------------------------------------------------------
开发使用了内联子查询,内联子查询会根据条件把外层表的每条数据到内层表进行一次匹配,如果是全表扫描就要进行这样多行的全表扫描,有点像NEST LOOP,所以这里
start才会是181K,所以重点落到如何改写内联子查询。
我如下改写
select count(*) from (
Select *
  From ppp
 Where (c1 = '0501' and c2 = 'test' and c3 <= sysdate)
   AND (1 = 0 OR SUBSTR(c4, 4, 1) = '0')
   AND c5 IN
       ('1', '2', '3', '4', '5',
        '6')
    or (c6 = '0501' and c6 = 'test' and c7 <= sysdate)
   AND (1 = 0 OR SUBSTR(c4, 4, 1) = '0')
   and (123 in
       (select 123
           from 123
          where 123 in (select 123
                                from 123
                               where 123 = 'vicky.li')) and
       agentcode = 'vicky.li')
   and riskcode = '0501') t,
 (SELECT DISTINCT t1 FROM sdf where 123 = '0501') p,
 (SELECT DISTINCT t2 FROM 123 WHERE 123 = '0501') c
 where t.123=p.ProposalNo and t.123=c.123
  ORDER BY t.123 desc, t.123 desc;
以前语句13秒,现在语句2秒。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-694084/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
wxh gp_22389860 <<深入理解MySQL主从原理专栏>> 发布 可加WX了解

注册时间:2008-10-13

  • 博文量
    718
  • 访问量
    3040572