select a.exec_sqn, a.dept_code
from ipmonmex a, lis_test_reg b
where a.exec_sqn = b.exec_sqn
and b.barcode = '1002052716'执行速度的2分钟左右,实在是太慢了,但是也是用到了索引,a.exec_sqn是唯一索引,b.exec_sqn是普通索引。执行计划如下SELECT STATEMENT, GOAL = CHOOSE 耗费=1267 基数=77072 字节=4084816
MERGE JOIN 耗费=1267 基数=77072 字节=4084816
TABLE ACCESS BY GLOBAL INDEX ROWID 对象所有者=TLHIS 对象名称=IPMONMEX 耗费=826 基数=7707246 字节=107901444
INDEX FULL SCAN 对象所有者=TLHIS 对象名称=PK_IPMONMEX 耗费=26 基数=7707246
SORT JOIN 耗费=441 基数=63901 字节=2492139
INDEX RANGE SCAN 对象所有者=TLHIS 对象名称=LIS 耗费=2 基数=63901 字节=2492139
from ipmonmex a, lis_test_reg b
where a.exec_sqn = b.exec_sqn
and b.barcode = '1002052716'执行速度的2分钟左右,实在是太慢了,但是也是用到了索引,a.exec_sqn是唯一索引,b.exec_sqn是普通索引。执行计划如下SELECT STATEMENT, GOAL = CHOOSE 耗费=1267 基数=77072 字节=4084816
MERGE JOIN 耗费=1267 基数=77072 字节=4084816
TABLE ACCESS BY GLOBAL INDEX ROWID 对象所有者=TLHIS 对象名称=IPMONMEX 耗费=826 基数=7707246 字节=107901444
INDEX FULL SCAN 对象所有者=TLHIS 对象名称=PK_IPMONMEX 耗费=26 基数=7707246
SORT JOIN 耗费=441 基数=63901 字节=2492139
INDEX RANGE SCAN 对象所有者=TLHIS 对象名称=LIS 耗费=2 基数=63901 字节=2492139
ipmonmex a, lis_test_reg b 这2个表那个大点, 把小表写在后面看看.where b.exec_sqn = a.exec_sqn 如果查询结果超过整个数据集的20% 时,建议走全表扫描...
------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群:62697716
lis_test_reg表中barcode字段建索引了吗?
ipmonmex的PK_IPMONMEX索引 INDEX FULL SCAN的方法 cost太大
有索引
而且
select b.exec_sqn
from lis_test_reg b
where b.barcode = '1002052716'
执行的速度不到0.1秒钟
select a.exec_sqn, a.dept_code
from ipmonmex a, lis_test_reg b
where a.exec_sqn(+) = b.exec_sqn --建立外连接按b表检索
and b.barcode = '1002052716'
from ipmonmex a, (select * from lis_test_reg where barcode = '1002052716' ) b
where a.exec_sqn = b.exec_sqn
where a.exec_sqn = b.exec_sqn;
试一下这样效率行不行。。
select /*+ no_merge(b) leading(b) */ a.exec_sqn, a.dept_code
from ipmonmex a, (select exec_sqn from lis_test_reg where barcode = '1002052716') b
where a.exec_sqn = b.exec_sqn;
from ipmonmex a
where exists (select 1 from
lis_test_reg b
where a.exec_sqn = b.exec_sqn
and b.barcode = '1002052716'
);