虚心求教地址列查询优化

显示全部楼层 · 2012-11-12 14:45:31

本人超级菜鸟，公司也没什么数据库大牛，只能求助论坛上的大牛了。
环境

racle 10.2.0.1.0
有一张大表，ry，记录数大概是800万左右，有一字段是人员的地址,address ,varchar2(255)，
像如下的sql如何优化
select * from ry r

wherer.sfyx='1' and r.address like '%地下%';
查询出来的记录应该是在30多万.
目前这个sql的执行时间是在90s 左右。
领导让今天就解决这个问题，可是我目前还是连执行计划都不懂的小菜鸟,虚心请教！

千问 · 2012-11-12 14:45:31

这么多结果，正常了

千问 · 2012-11-12 14:45:31

原帖由〇〇于 2011-4-25 11:02 发表
这么多结果，正常了
蛋蛋，假如从应用上修改的话也没什么可优化的余地了吗？
我试着把过滤条件改成wherer.sfyx='1' and instr(r.address,'地下')>0,再建函数索引 instr(address,'地下')，
然后收集统计信息，可是查询还是一样慢,90s,这样调整之后不是应该走索引了吗怎么还是这么慢呢？

千问 · 2012-11-12 14:45:31

你的计划呢？

千问 · 2012-11-12 14:45:31

原帖由 dingjun123 于 2011-4-25 11:16 发表
你的计划呢？
完整的sql
select *from sjcj_rydjbxx r where r.rdj_sfzc='0' and r.rdj_sfyx='1'
and r.fwzjbxxdjb_fwzbh is not null and instr(rzf_xzdxxdz,'地下')>0;
执行计划怎么查看？
SQL> explain plan for
2select count(*) from sjcj_rydjbxx r where r.rdj_sfzc='0' and r.rdj_sfyx='1' and r.fwzjbxxdjb_fw
zbh is not null
3and instr(rzf_xzdxxdz,'地下')>0;
已解释。
SQL>
怎么没结果。。。

千问 · 2012-11-12 14:45:31

sqlplus里，set autotrace traceonly,然后执行sql,弄出来

千问 · 2012-11-12 14:45:31

原帖由 dingjun123 于 2011-4-25 11:43 发表
sqlplus里，set autotrace traceonly,然后执行sql,弄出来
SQL> select * from sjcj_rydjbxx r where r.rdj_sfzc='0' and r.rdj_sfyx='1' and r.fwzjbxxdjb_fwzbh is
not null
2and instr(rzf_xzdxxdz,'地下')>0;
已选择350703行。

执行计划
----------------------------------------------------------
----------------------------------------------------------------------------------------------------
| Id| Operation
| Name
| Rows| Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 369K| 243M|9621 (1)| | |
| 1 |TABLE ACCESS BY GLOBAL INDEX ROWID| SJCJ_RYDJBXX | 369K| 243M|9621 (1)| ROWID | R
| 2 | INDEX RANGE SCAN
| INDEX_RYDJBXX_DXKJCX | 67744 | | 166 (2)| | |
----------------------------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------

1recursive calls

0db block gets
320365consistent gets
233099physical reads

0redo size
178946175bytes sent via SQL*Net to client
257565bytes received via SQL*Net from client
23382SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)
350703rows processed
SQL>

千问 · 2012-11-12 14:45:31

很简单的计划，没有什么可优化的了，返回35万条数据啊

千问 · 2012-11-12 14:45:31

就目前看，即使目前optimizer没有收集过的最新的统计数据，但是执行计划还是使用了index range scan，目前语句慢的主要原因是逻辑和物理读实在是太大了，
不知道你原表的数据是多少，如果要进一步优化，不是优化语句了，而是要考虑建索引分区了，或者是使用并行查询。

千问 · 2012-11-12 14:45:31

原帖由 bell6248 于 2011-4-25 12:50 发表

就目前看，即使目前optimizer没有收集过的最新的统计数据，但是执行计划还是使用了index range scan，目前语句慢的主要原因是逻辑和物理读实在是太大了，
不知道你原表的数据是多少，如果要进一步优化，不是优化语句了，而是要考虑建索引分区了，或者是使用并行查询。
原表的数据大概是在800万左右，有分区，按地域分的，但是这个查询是需要扫描所有分区的这个字段,
在论坛上看到很多并行查询的东西，我想问下并行查询对OLTP系统有没有什么负面影响，系统同时访问这张表的并发大概在1000左右。而且经常有update ,insert ,delete