数据库优化问题 做查询的时候,加上(+)外连接后会很慢,但是不加有些数据还查不出来,现在的问题是如何能让速度快一些那?而该查的数据都能查询出来(表中的数据很大,并且是多表关联)谁有什么好办法吗? 查询是实时的 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 你把sql写出来,说得清楚一些才可以给你建议的 SELECT I.INSTRUMENT_ID, GOODSRACK.STOCKROOM_OID, GOODSRACK.DESCRIPTION GOODS_RACK, G.DESCRIPTION GOODS_LOCATION, NULL BOX_OID, I.STATUS, STATUSCHANGEENTRY.INSTRUMENTSTATUS, I.INSTRUMENT_TYPE_OID, SLOG.END_DATE, I.TYPE_CODE, I.METER_CODE, STOCKROOM.ADMINISTRATOR FROM STOCKROOM, GOODSRACK,GOODSLOCATION G, STOCKLOG SLOG, STATUSCHANGEENTRY, REQUEST R, INSTRUMENT I WHERE STOCKROOM.STOCK_ROOM_OID = GOODSRACK.STOCKROOM_OID(+) AND GOODSRACK.GOODS_RACK_OID = G.GOODSRACK_OID(+) AND G.GOODS_LOCATION_OID = SLOG.GOODS_LOCATION_OID (+) AND SLOG.STOCK_LOG_OID = (SELECT MAX(STOCKLOG.STOCK_LOG_OID) FROM STOCKLOG WHERE STOCKLOG.INSTRUMENT_OID = I.INSTRUMENT_OID) AND STATUSCHANGEENTRY.STATUS = 0 AND STATUSCHANGEENTRY.INSTRUMENT_OID = I.INSTRUMENT_OID AND R.REQUEST_OID = I.REQUEST_OID AND i.division_oid = :1 Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStopSELECT STATEMENT Optimizer Mode=CHOOSE 1 18 FILTER HASH JOIN OUTER 1 147 16 HASH JOIN OUTER 1 132 13 HASH JOIN OUTER 1 115 8 MERGE JOIN CARTESIAN 1 100 5 TABLE ACCESS BY INDEX ROWID JLUSER.STATUSCHANGEENTRY 1 12 1 NESTED LOOPS 1 91 3 NESTED LOOPS 1 79 2 TABLE ACCESS BY INDEX ROWID JLUSER.INSTRUMENT 1 66 1.1 BITMAP CONVERSION TO ROWIDS BITMAP INDEX SINGLE VALUE JLUSER.IDX_DIVISION_OID INDEX UNIQUE SCAN JLUSER.REQUEST_PRIMARY_KEY 271 K 3 M INDEX RANGE SCAN JLUSER.IDX_INSTRUMENTOID_STATCHENTRY 2 1 BUFFER SORT 2 18 4 TABLE ACCESS FULL JLUSER.STOCKROOM 2 18 2 TABLE ACCESS FULL JLUSER.GOODSRACK 34 510 2 TABLE ACCESS FULL JLUSER.GOODSLOCATION 3 K 63 K 4 TABLE ACCESS FULL JLUSER.STOCKLOG 160 2 K 2 SORT AGGREGATE 1 10 TABLE ACCESS FULL JLUSER.STOCKLOG 1 10 2 这是计划 嗯,关注比如STATUSCHANGEENTRY.STATUS = 0 能否先对这个表做过滤然后JOIN,这样能不能做优化呢?或者对JOIN以后数据量小的表先执行JOIN。 pl/sql语句询问 求sql语句 oracle存储过程使用游标嵌套循环的问题,急等!!! oracle中存储过程里for循环如何每一次循环都从表中取出相邻的两条记录 Oracle 10g 穿越XP防火墙访问问题! 查询每月数据,如何消除笛卡尔积,麻烦各位大虾指点 一个oracle故障排除的问题 服务器的周与自然周相差4天,怎么回事? 关于如何在存储过程中进行字符串连接以及类型转换 高分求Oracle8i的备份与恢复脚本 把Excel数据导到Oracle如何实现加密// ORACLE中发邮件用UTL_SMTP,那么如果SMTP服务器要求密码验证怎么办?
I.STATUS, STATUSCHANGEENTRY.INSTRUMENTSTATUS, I.INSTRUMENT_TYPE_OID, SLOG.END_DATE, I.TYPE_CODE, I.METER_CODE, STOCKROOM.ADMINISTRATOR
FROM STOCKROOM, GOODSRACK,GOODSLOCATION G, STOCKLOG SLOG, STATUSCHANGEENTRY, REQUEST R, INSTRUMENT I
WHERE
STOCKROOM.STOCK_ROOM_OID = GOODSRACK.STOCKROOM_OID(+)
AND GOODSRACK.GOODS_RACK_OID = G.GOODSRACK_OID(+)
AND G.GOODS_LOCATION_OID = SLOG.GOODS_LOCATION_OID (+)
AND SLOG.STOCK_LOG_OID = (SELECT MAX(STOCKLOG.STOCK_LOG_OID) FROM STOCKLOG WHERE STOCKLOG.INSTRUMENT_OID = I.INSTRUMENT_OID)
AND STATUSCHANGEENTRY.STATUS = 0
AND STATUSCHANGEENTRY.INSTRUMENT_OID = I.INSTRUMENT_OID
AND R.REQUEST_OID = I.REQUEST_OID
AND i.division_oid = :1
FILTER
HASH JOIN OUTER 1 147 16
HASH JOIN OUTER 1 132 13
HASH JOIN OUTER 1 115 8
MERGE JOIN CARTESIAN 1 100 5
TABLE ACCESS BY INDEX ROWID JLUSER.STATUSCHANGEENTRY 1 12 1
NESTED LOOPS 1 91 3
NESTED LOOPS 1 79 2
TABLE ACCESS BY INDEX ROWID JLUSER.INSTRUMENT 1 66 1.1
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE JLUSER.IDX_DIVISION_OID
INDEX UNIQUE SCAN JLUSER.REQUEST_PRIMARY_KEY 271 K 3 M
INDEX RANGE SCAN JLUSER.IDX_INSTRUMENTOID_STATCHENTRY 2 1
BUFFER SORT 2 18 4
TABLE ACCESS FULL JLUSER.STOCKROOM 2 18 2
TABLE ACCESS FULL JLUSER.GOODSRACK 34 510 2
TABLE ACCESS FULL JLUSER.GOODSLOCATION 3 K 63 K 4
TABLE ACCESS FULL JLUSER.STOCKLOG 160 2 K 2
SORT AGGREGATE 1 10
TABLE ACCESS FULL JLUSER.STOCKLOG 1 10 2
这是计划
比如STATUSCHANGEENTRY.STATUS = 0 能否先对这个表做过滤然后JOIN,这样能不能做优化呢?
或者对JOIN以后数据量小的表先执行JOIN。