本帖最后由 muyu208 于 2011-12-1 18:03 编辑
DB版本:10.2.0.4
OS:RHEL 4.8
有一条查询语句查询某个code是否在start_no与end_no范围内,创建了start_no与end_no的联合索引,查询起来比较慢,执行计划如下,请各位DX帮忙看看有什么办法可以优化下,谢谢!
select * from TEST.TAB_BILL_PROVIDE whereSTART_NO='205013935980';
表结构
CREATE TABLE TAB_BILL_PROVIDE
(
START_NOVARCHAR2(20 BYTE),
END_NOVARCHAR2(20 BYTE),
PROVIDE_DATEDATE
NOT NULL,
)
执行计划
----------------------------------------------------------
Plan hash value: 586189817
--------------------------------------------------------------------------------------
| Id| Operation | Name
| Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT|
| 430K|51M|7297 (2)| 00:01:28 |
|*1 |TABLE ACCESS FULL| TAB_BILL_PROVIDE | 430K|51M|7297 (2)| 00:01:28 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("START_NO"='205013935980')
统计信息
----------------------------------------------------------
1recursive calls
0db block gets
33187consistent gets
32352physical reads
0redo size
1566bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
2rows processed
表中的记录:
START_NOEND_NO PROVIDE_DATE
00000277000100000277100007-14-2005 00:00:00
00000277100100000277200007-20-2005 00:00:00
00000277200100000277300006-21-2005 00:00:00
00000277300100000277400006-25-2005 00:00:00
00000277400100000277500006-29-2005 00:00:00
00000277500100000277600007-06-2005 00:00:00
00000277600100000277700006-25-2005 00:00:00
00000277700100000277800006-29-2005 00:00:00
00000277800100000277900006-29-2005 00:00:00
00000277900100000278000007-14-2005 00:00:00
|
