在线等待，查询速度太慢，能否优化？

Question

select id from tbl_member_info where 
ID in 
(select ID from TBL_FLIGHT_ACTIVITY group by ID having count(*)>=50) and 
ID in 
(select ID from tbl_member_fulfillment_history where ACTIVE_DATE >=to_date('2007-11-27','yyyy-mm-dd') and CARD_STATUS <> 'N' ) and
UNITED_CARD_CODE='N' and 
IS_TEST_MEMBER='N' and  
MEMBER_STATUS_CODE='AC' and  
ADDRESS_STATUS='Y' and 
OPERATE_DATE<=to_date('2007-11-27','yyyy-mm-dd') and 
MEMBER_LEVEL_CODE='1'tbl_member_info 表里记录50万
TBL_FLIGHT_ACTIVITY 表记录300万
tbl_member_fulfillment_history 表里里记录100万
我在pl/sql developer里面查询花了5分钟，第一个子查询花了20秒

Answer 1

select id from tbl_member_info where 
UNITED_CARD_CODE='N' and 
IS_TEST_MEMBER='N' and  
MEMBER_STATUS_CODE='AC' and  
ADDRESS_STATUS='Y' and 
MEMBER_LEVEL_CODE='1' and
OPERATE_DATE <=to_date('2007-11-27','yyyy-mm-dd') and 
ID in 
(select ID from TBL_FLIGHT_ACTIVITY group by ID having count(*)>=50) and 
ID in 
(select ID from tbl_member_fulfillment_history where ACTIVE_DATE >=to_date('2007-11-27','yyyy-mm-dd') and CARD_STATUS <> 'N' )这样会快点吗？

Answer 2

再试试这个吧。select id from tbl_member_info T1, tbl_member_fulfillment_history T2 where 
T1.UNITED_CARD_CODE='N' and 
T1.IS_TEST_MEMBER='N' and  
T1.MEMBER_STATUS_CODE='AC' and  
T1.ADDRESS_STATUS='Y' and 
T1.MEMBER_LEVEL_CODE='1' and 
T1.OPERATE_DATE <=to_date('2007-11-27','yyyy-mm-dd') and 
T1.ID=T2.ID and
T2.ACTIVE_DATE >=to_date('2007-11-27','yyyy-mm-dd') and 
T2.CARD_STATUS <> 'N'
T1.ID in 
(select ID from TBL_FLIGHT_ACTIVITY group by ID having count(*)>=50) and 

Answer 3

把in改成exists试试看，并且把执行计划贴出来看看SELECT ID
  FROM tbl_member_info a
 WHERE EXISTS (SELECT   1
                   FROM tbl_flight_activity b
                  WHERE a.ID = b.ID
               GROUP BY ID
                 HAVING COUNT (*) >= 50)
   AND EXISTS (
          SELECT ID
            FROM tbl_member_fulfillment_history c
           WHERE active_date >= TO_DATE ('2007-11-27', 'yyyy-mm-dd')
             AND card_status <> 'N'
             AND c.ID = a.ID)
   AND united_card_code = 'N'
   AND is_test_member = 'N'
   AND member_status_code = 'AC'
   AND address_status = 'Y'
   AND operate_date <= TO_DATE ('2007-11-27', 'yyyy-mm-dd')
   AND member_level_code = '1'

Answer 4

你是在sql plus 里看的吗？
是的话
set autotrace on
然后执行sql
最后出来的就是执行计划

Answer 5

或者
SET AUTOTRACE ON EXPLAIN 
这样只显示执行计划，不显示select的结果

Answer 6

知道5楼是高手，你的sql语句我试了两次，比前两种都慢20多秒

Answer 7

我的执行计划
Execution Plan
----------------------------------------------------------
 SELECT STATEMENT Optimizer=CHOOSE (Cost=3820 Card=1 Bytes=96)
 HASH JOIN (SEMI) (Cost=3820 Card=1 Bytes=96)
 NESTED LOOPS (SEMI) (Cost=4 Card=1 Bytes=83)
 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TBL_MEMBER_INFO' (Cost=2 Card=1 Bytes=51)
 INDEX (RANGE SCAN) OF 'FK_MEMBERLEVEL_CODE_MEMBERINFO' (NON-UNIQUE) (Cost=1 Card=8809)
 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_MEMBER_FULFILLMENT_HISTORY' (Cost=2 Card=860 Bytes=27520)
 INDEX (RANGE SCAN) OF 'FK_R_MEMBERINFO_ID_MEMBERFULFI' (NON-UNIQUE) (Cost=1 Card=400)
 VIEW OF 'VW_NSO_1' (Cost=3815 Card=4236 Bytes=55068)
 FILTER
 SORT (GROUP BY) (Cost=3815 Card=4236 Bytes=16944)
 INDEX (FAST FULL SCAN) OF 'FK_MEMBERINFO_ID_FLIGHTACTIV' (NON-UNIQUE) (Cost=628 Card=2309992 Bytes=9239968)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    1593272  consistent gets
      39649  physical reads
          0  redo size
        215  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Answer 8

贴执行计划吧，才知道慢在哪里
虽然许多时候exist要比in快，但不是绝对的

Answer 9

主要消耗的成本在
SORT (GROUP BY) (Cost=3815 Card=4236 Bytes=16944) 
INDEX (FAST FULL SCAN) OF 'FK_MEMBERINFO_ID_FLIGHTACTIV' (NON-UNIQUE) (Cost=628 Card=2309992 Bytes=9239968) 
Statistics 
你的执行计划要把前面的编号贴出来啊，否则怎么知道顺序啊

Answer 10

主要是慢在这句
SELECT   ID
                  FROM tbl_flight_activity
              GROUP BY ID
                HAVING COUNT (*) >= 50
这个表本身数据就多，可以看出来成本主要消耗在group by 这

Answer 11

你换这样的，看看执行计划和执行时间
SELECT ID
  FROM (SELECT   ID
            FROM tbl_flight_activity
        GROUP BY ID
          HAVING COUNT (*) >= 50) b,
       (SELECT DISTINCT ID
                   FROM tbl_member_fulfillment_history
                  WHERE active_date >= TO_DATE ('2007-11-27', 'yyyy-mm-dd')
                    AND card_status <> 'N') c,
       tbl_member_info a
 WHERE a.ID = b.ID
   AND a.ID = c.ID
   AND united_card_code = 'N'
   AND is_test_member = 'N'
   AND member_status_code = 'AC'
   AND address_status = 'Y'
   AND operate_date <= TO_DATE ('2007-11-27', 'yyyy-mm-dd')
   AND member_level_code = '1'

Answer 12

以前不懂执行计划，我再贴一遍
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3820 Card=1 Bytes=96
          )   1    0   HASH JOIN (SEMI) (Cost=3820 Card=1 Bytes=96)
   2    1     NESTED LOOPS (SEMI) (Cost=4 Card=1 Bytes=83)
   3    2       TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TBL_MEMBER_IN
          FO' (Cost=2 Card=1 Bytes=51)   4    3         INDEX (RANGE SCAN) OF 'FK_MEMBERLEVEL_CODE_MEMBERINF
          O' (NON-UNIQUE) (Cost=1 Card=8809)   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'TBL_MEMBER_FULFILLME
          NT_HISTORY' (Cost=2 Card=860 Bytes=27520)   6    5         INDEX (RANGE SCAN) OF 'FK_R_MEMBERINFO_ID_MEMBERFULF
          I' (NON-UNIQUE) (Cost=1 Card=400)   7    1     VIEW OF 'VW_NSO_1' (Cost=3815 Card=4236 Bytes=55068)
   8    7       FILTER
   9    8         SORT (GROUP BY) (Cost=3815 Card=4236 Bytes=16944)
  10    9           INDEX (FAST FULL SCAN) OF 'FK_MEMBERINFO_ID_FLIGHT
          ACTIV' (NON-UNIQUE) (Cost=628 Card=2309992 Bytes=9239968)Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    1593272  consistent gets
      39398  physical reads
          0  redo size
        215  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Answer 13

看了你的执行计划，应该95%以上是花在
SELECT   ID
            FROM tbl_flight_activity
        GROUP BY ID
          HAVING COUNT (*) >= 50)
这句里，而这句里，大概80%的时间是花在GROUP BY ID
          HAVING COUNT (*) >= 50这一部分

Answer 14

假如你的tbl_member_info ID都是唯一的，可以改成下面的试试看
另外tbl_member_fulfillment_history如果每个id只有一条纪录，可以把DISTINCT去掉SELECT ID
  FROM tbl_flight_activity
         b,
       (SELECT DISTINCT ID
                   FROM tbl_member_fulfillment_history
                  WHERE active_date >= TO_DATE ('2007-11-27', 'yyyy-mm-dd')
                    AND card_status <> 'N') c,
       tbl_member_info a
 WHERE a.ID = b.ID
   AND a.ID = c.ID
   AND united_card_code = 'N'
   AND is_test_member = 'N'
   AND member_status_code = 'AC'
   AND address_status = 'Y'
   AND operate_date <= TO_DATE ('2007-11-27', 'yyyy-mm-dd')
   AND member_level_code = '1'
   GROUP BY a.ID
          HAVING COUNT (*) >= 50

Answer 15

我的表没有做过分析
tbl_member_info ID是主键，是唯一索引，另外还有八个由单个字段组成的索引
tbl_flight_activity ID 也是一个索引，另外还有10个索引，也有聚合索引
tbl_member_fulfillment_history 中有三个索引，ID也是其中之一