表tbl_mdsmpp0723中有记录550W左右。查询下面的语句需要时间160S
select count(1) from TBL_MDSMPP0723 WHERE GWID IN ('06','08','02','04','05','07','01','03')
GROUP BY to_char(LogTime,'YYYY"年"MM"月"DD"日"HH24"点"'), ServCode, IsmgAccount,GWID;查询下面的语句需要时间一个半小时
select count(1) from TBL_MDSMPP0723 WHERE GWID IN ('06','08','02','04','05','07','01','03')
where logtime >= to_date('20100723 09:10','yyyymmdd hh24:mi') and logtime < to_date('20100723 09:13','yyyymmdd hh24:mi');表中有两个索引,一个是logtime,一个是(logtime,servcode,ismgaccount)的组合索引。不知道怎么手动看解释计划,请高手们帮忙看下!
select count(1) from TBL_MDSMPP0723 WHERE GWID IN ('06','08','02','04','05','07','01','03')
GROUP BY to_char(LogTime,'YYYY"年"MM"月"DD"日"HH24"点"'), ServCode, IsmgAccount,GWID;查询下面的语句需要时间一个半小时
select count(1) from TBL_MDSMPP0723 WHERE GWID IN ('06','08','02','04','05','07','01','03')
where logtime >= to_date('20100723 09:10','yyyymmdd hh24:mi') and logtime < to_date('20100723 09:13','yyyymmdd hh24:mi');表中有两个索引,一个是logtime,一个是(logtime,servcode,ismgaccount)的组合索引。不知道怎么手动看解释计划,请高手们帮忙看下!
explain plan for SQL Statement
select * from table(dbms_xplan.display)
2 SELECT * FROM test;已解释。SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Plan hash value: 1357081020--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 48 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 16 | 48 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------已选择8行。SQL>