帮我看看这个执行计划
EXPLAIN PLAN FOR SELECT * FROM (SELECT a.*,ROWNUM r FROM (SELECT
sbbh,fxbh,clbh,hphm,hpzl,tztx,qjtx,jgsj FROM collect_af WHERE INSTR(hphm,'A')>0 ORDER BY
jgsj DESC) a WHERE ROWNUM<=40) WHERE r>=1;
执行后结果:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4092853332
--------------------------------------------------------------------------------
-----------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e | Pstart| Pstop |
--------------------------------------------------------------------------------
-----------------------
| 0 | SELECT STATEMENT | | 40 | 13040 | 39 (3)| 00:
00:01 | | |
|* 1 | VIEW | | 40 | 13040 | 39 (3)| 00:
00:01 | | |
|* 2 | COUNT STOPKEY | | | | |
| | |
| 3 | VIEW | | 254 | 79502 | 39 (3)| 00:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
00:01 | | |
|* 4 | SORT ORDER BY STOPKEY| | 254 | 55118 | 39 (3)| 00:
00:01 | | |
| 5 | PARTITION RANGE ALL | | 254 | 55118 | 38 (0)| 00:
00:01 | 1 | 20 |
|* 6 | TABLE ACCESS FULL | COLLECT_AF | 254 | 55118 | 38 (0)| 00:
00:01 | 1 | 20 |
--------------------------------------------------------------------------------
-----------------------
在HPHM 和 CLBH上建立了索引;
表中数据有2000多W条:
当查询条件包含号牌号码时,查询结果要90秒左右!
各位大神有什么看法?