一条SQL的优化问题请教版主？

husthxd

还是把执行计划贴出来看看把....

yukeelau

select count(userid) from order
where ifsend = 1
and substr(sendid,1,2) = 'EC'
and asst_id in (904,4553,4874)
and userid in (select userid from order
where ifsend = 1 and asst_id = 0 and substr(sendid,1,2) not in ('EQ','TB','EP'));
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
在sendid上建索引，然后将substr(sendid,1,2)='EC'改成sendid like 'EC%'应该会更好些。

ljljlj168

没加DISTINCT的：
Plan Table
--------------------------------------------------------------------------------
| Operation                  |  Name    |  Rows | Bytes|  Cost  |  TQ  |IN-OUT|
PQ Distrib | Pstart| Pstop |

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

| SELECT STATEMENT           |          |     1 |   21 |     10 |      |      |
           |       |       |

|  SORT AGGREGATE            |          |     1 |   21 |        |      |      |
           |       |       |

Plan Table
--------------------------------------------------------------------------------

|   FILTER                   |          |       |      |        |      |      |
           |       |       |

|    TABLE ACCESS BY INDEX RO|EC_ORDER_ |     1 |   21 |     10 |      |      |
           |       |       |

|     INDEX RANGE SCAN       |ASST_INDE |     1 |      |      1 |      |      |
           |       |       |

|    TABLE ACCESS BY INDEX RO|EC_ORDER_ |     1 |   21 |      6 |      |      |

Plan Table
--------------------------------------------------------------------------------
           |       |       |

|     AND-EQUAL              |          |       |      |        |      |      |
           |       |       |

|      INDEX RANGE SCAN      |ASST_INDE |     1 |      |      1 |      |      |
           |       |       |

|      INDEX RANGE SCAN      |EC_ORDER_ |     1 |      |      1 |      |      |
           |       |       |


加DISTINCT的：
Plan Table
--------------------------------------------------------------------------------
| Operation                  |  Name    |  Rows | Bytes|  Cost  |  TQ  |IN-OUT|
PQ Distrib | Pstart| Pstop |

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

| SELECT STATEMENT           |          |     1 |   42 |     12 |      |      |
           |       |       |

|  SORT GROUP BY             |          |     1 |   42 |        |      |      |
           |       |       |

Plan Table
--------------------------------------------------------------------------------

|   NESTED LOOPS             |          |     1 |   42 |     12 |      |      |
           |       |       |

|    TABLE ACCESS BY INDEX RO|EC_ORDER_ |     1 |   21 |     10 |      |      |
           |       |       |

|     INDEX RANGE SCAN       |ASST_INDE |     1 |      |      1 |      |      |
           |       |       |

|    TABLE ACCESS BY INDEX RO|EC_ORDER_ |     1 |   21 |      2 |      |      |

Plan Table
--------------------------------------------------------------------------------
           |       |       |

|     INDEX RANGE SCAN       |EC_ORDER_ |     1 |      |      1 |      |      |
           |       |       |

谢谢

ljljlj168

句子：
select count(userid) from ec_order_mainorder a
where ifsend = 1
and  sendid like 'EC%'
and  asst_id = 1453
and exists (select userid from ec_order_mainorder b
              where ifsend = 1
              and asst_id = 0
              and a.userid = b.userid
              and substr(sendid,1,2) not in ('EQ','TB','EP'));

select count(distinct userid) from ec_order_mainorder a
where ifsend = 1
and  sendid like 'EC%'
and  asst_id = 1453
and exists (select userid from ec_order_mainorder b
              where ifsend = 1
              and asst_id = 0
              and a.userid = b.userid
              and substr(sendid,1,2) not in ('EQ','TB','EP'));

ljljlj168

没加DISTINCT的：
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=1 Bytes=21)
   1    0   SORT (AGGREGATE)
   2    1     FILTER
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'EC_ORDER_MAINORDER'
          (Cost=28 Card=1 Bytes=21)

   4    3         INDEX (RANGE SCAN) OF 'SENDID_UNIQUE' (UNIQUE) (Cost
          =3 Card=1)

   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'EC_ORDER_MAINORDER'
          (Cost=17 Card=1 Bytes=21)

   6    5         AND-EQUAL
   7    6           INDEX (RANGE SCAN) OF 'EC_ORDER_MAINORDER_IDX_USER
          ID' (NON-UNIQUE) (Cost=3 Card=1)

   8    6           INDEX (RANGE SCAN) OF 'EC_ORDER_MAINORDER_ASSTID_I
          DX' (NON-UNIQUE) (Cost=10 Card=1)





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

加DISTINCT的：
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=1 Bytes=42)
   1    0   SORT (GROUP BY)
   2    1     NESTED LOOPS (Cost=31 Card=1 Bytes=42)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'EC_ORDER_MAINORDER'
          (Cost=28 Card=1 Bytes=21)

   4    3         INDEX (RANGE SCAN) OF 'SENDID_UNIQUE' (UNIQUE) (Cost
          =3 Card=1)

   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'EC_ORDER_MAINORDER'
          (Cost=3 Card=1 Bytes=21)

   6    5         INDEX (RANGE SCAN) OF 'EC_ORDER_MAINORDER_IDX_USERID
          ' (NON-UNIQUE) (Cost=2 Card=1)





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

husthxd

换成下面的写法试试?

1.
select count(distinct userid) from
(
select userid
from ec_order_mainorder a
where ifsend = 1
and sendid like 'EC%'
and asst_id = 1453
and exists (select userid from ec_order_mainorder b
  where ifsend = 1
  and asst_id = 0
  and a.userid = b.userid
  and substr(sendid,1,2) not in ('EQ','TB','EP')
  )
);

2.
select count(distinct userid) from ec_order_mainorder a
where ifsend = 1
and sendid like 'EC%'
and asst_id = 1453
and userid in (select /*+hash_sj*/ userid from ec_order_mainorder b
where ifsend = 1
and asst_id = 0
and substr(sendid,1,2) not in ('EQ','TB','EP')
);

ljljlj168

谢谢

husthxd

最初由 ljljlj168 发布
[B]谢谢 [/B]

可能的话把表的数据大小,列上的索引情况再说明一下?
btw:
最好把调优结果贴出来看看.

lwxxrq

就算慢也不至于执行不出来吧。
不排序的就可以，是不是临时表空间满了之类的其它原因导致的？这个表有多少行？查询的结果分别是多少行呢？也讲讲吧

ljljlj168

开始的确跑不出来
后来改了改可以了