有表表APPLICATION_DATA_BAK 记录条数为500万,字段段link_id的有6种取值,且不能为空。现有查询语句
Select * from APPLICATION_DATA_BAK WHERE LINK_ID=342
不加任何索引的执行计划为
238683 rows selected.
Elapsed: 00:00:15.11
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13624 Card=239728
Bytes=13904224) 1 0 TABLE ACCESS (FULL) OF 'APPLICATION_DATA_BAK' (TABLE) (Cos
t=13624 Card=239728 Bytes=13904224)
Statistics
----------------------------------------------------------
195 recursive calls
0 db block gets
77193 consistent gets
61349 physical reads
0 redo size
6482097 bytes sent via SQL*Net to client
175544 bytes received via SQL*Net from client
15914 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
238683 rows processed
对LINK_ID加bitmap索引时的执行计划为:
238683 rows selected.
Elapsed: 00:00:14.62
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13624 Card=239728
Bytes=13904224) 1 0 TABLE ACCESS (FULL) OF 'APPLICATION_DATA_BAK' (TABLE) (Cos
t=13624 Card=239728 Bytes=13904224)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
77143 consistent gets
61226 physical reads
0 redo size
6482097 bytes sent via SQL*Net to client
175544 bytes received via SQL*Net from client
15914 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
238683 rows processed 加索引后发现性能并没有改善,同时查询计划中也并没有使用索引,因此我强制使用指定的索引,起sql语句为select /*+ index(APPLICATION_DATA_BAK, LINKID_INDEX)*/ * FROM APPLICATION_DATA_BAK WHERE LINK_ID=342; 其执行计划为:
238683 rows selected.
Elapsed: 00:00:13.34
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16816 Card=239728
Bytes=13904224) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'APPLICATION_DATA_BAK' (T
ABLE) (Cost=16816 Card=239728 Bytes=13904224) 2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'LINKID_INDEX' (INDEX (
BITMAP))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23028 consistent gets
7218 physical reads
0 redo size
17347210 bytes sent via SQL*Net to client
175544 bytes received via SQL*Net from client
15914 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
238683 rows processed
性能几乎没有改善,请问为了提高该查询语句的性能,该怎么加索引呢,谢谢!
Select * from APPLICATION_DATA_BAK WHERE LINK_ID=342
不加任何索引的执行计划为
238683 rows selected.
Elapsed: 00:00:15.11
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13624 Card=239728
Bytes=13904224) 1 0 TABLE ACCESS (FULL) OF 'APPLICATION_DATA_BAK' (TABLE) (Cos
t=13624 Card=239728 Bytes=13904224)
Statistics
----------------------------------------------------------
195 recursive calls
0 db block gets
77193 consistent gets
61349 physical reads
0 redo size
6482097 bytes sent via SQL*Net to client
175544 bytes received via SQL*Net from client
15914 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
238683 rows processed
对LINK_ID加bitmap索引时的执行计划为:
238683 rows selected.
Elapsed: 00:00:14.62
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13624 Card=239728
Bytes=13904224) 1 0 TABLE ACCESS (FULL) OF 'APPLICATION_DATA_BAK' (TABLE) (Cos
t=13624 Card=239728 Bytes=13904224)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
77143 consistent gets
61226 physical reads
0 redo size
6482097 bytes sent via SQL*Net to client
175544 bytes received via SQL*Net from client
15914 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
238683 rows processed 加索引后发现性能并没有改善,同时查询计划中也并没有使用索引,因此我强制使用指定的索引,起sql语句为select /*+ index(APPLICATION_DATA_BAK, LINKID_INDEX)*/ * FROM APPLICATION_DATA_BAK WHERE LINK_ID=342; 其执行计划为:
238683 rows selected.
Elapsed: 00:00:13.34
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16816 Card=239728
Bytes=13904224) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'APPLICATION_DATA_BAK' (T
ABLE) (Cost=16816 Card=239728 Bytes=13904224) 2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'LINKID_INDEX' (INDEX (
BITMAP))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23028 consistent gets
7218 physical reads
0 redo size
17347210 bytes sent via SQL*Net to client
175544 bytes received via SQL*Net from client
15914 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
238683 rows processed
性能几乎没有改善,请问为了提高该查询语句的性能,该怎么加索引呢,谢谢!
解决方案 »
- Hibernate Oracle 数据库主键 如何设计?
- 【急】在oracle中,nvarchar2()的长度问题
- 多表查询继续中(急)
- 散光剩下的分,只为求助如何在left outer join的on条件语句中使用子查询
- oracle中怎么样用存储过程实现批量的将文本文件导入数据库中
- 开发的时候用的oracle9i,现在数据库用oracle10g出现的问题。。。
- 请问各位:如何在linux下安装oracle8.1.7?谢谢
- 调用PRO*C库的C程序编译出现错误,高手指教
- jsp 访问 oracle 数据库问题
- Oracle 根据其他列数据更新某一列数据
- 数据库说明书中,下面两点应该写什么内容?看来看去不知道写什么内容,那位高人指点一下,最好有列子
- 关于分组的问题
23028 consistent gets
7218 physical reads
瓶颈仿佛不在这里。每个语句多跑几次,取最后一次的贴出来。
1、这个统计值17347210 bytes sent via SQL*Net to client 太大了
2、确实返回的行比较多;1、减小bytes sent via SQL*Net to client
原因:使用了select *,而表中的列比较多,或列有一定长度
方法: 只选取需要的列,通常应用不需要所有的列
以下是测试结果,表employer有53个字段
SQL>select * from employer;
省略输出......
已选择532行。
已用时间: 00: 09: 58.02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMPLOYER'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
75 consistent gets
0 physical reads
0 redo size
169223 bytes sent via SQL*Net to client
888 bytes received via SQL*Net from client
37 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
532 rows processed--改为select EMPID进行测试
SQL>select EMPID from employer;
已选择532行。
已用时间: 00: 00: 00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMPLOYER'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
75 consistent gets
0 physical reads
0 redo size
10148 bytes sent via SQL*Net to client
888 bytes received via SQL*Net from client
37 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
532 rows processed
测试发现,选取少的列,有利于降低bytes sent via SQL*Net to client,同时减少运行时间。2、在返还数据行过多时,缩短响应时间
如果楼主的取这些行是用作报表,则可以让界面分页显示,而后台使用first_rows关键字缩短响应时间在以上测试中仍受一些因素影响,希望大家一起讨论
--问题1的测试是在终端上通过sqlplus执行获取的结果,因此运行时间除了与网络有关,
是否与这个有关:数据在sqlplus上回显的速度。
如果与回显速度有关,哪么在数据库主机上运行以上sql的执行时间肯定要少:通常不用回显;
因此,建议在生产系统试运行期间,对数据库进行严密监控。以上结果是否有效,还请楼主试完了进行反馈。