select dw.wid , dww.org_id ,dww.store_id
from dwd.dwd_ware dw
join dwd.dwd_product_sort dp1 on dw.wpid = dp1.id and dp1.yn=1
join dwd.dwd_product_sort dp2 on dp2.id = dp1.fatherid and dp2.yn=1
join dwd.dwd_product_sort dp3 on dp3.id = dp2.fatherid and dp3.id in(1713, 1316) and length(wid)<9 and dp3.yn=1
join dwd.dwd_wms_warestock dww on dww.product_id = dw.wid
----------------------select /*+ no_index(dw DWD_WARE_INDEX2) */ dw.wid , dww.org_id ,dww.store_id
from dwd.dwd_ware dw
join dwd.dwd_product_sort dp1 on dw.wpid = dp1.id and dp1.yn=1
join dwd.dwd_product_sort dp2 on dp2.id = dp1.fatherid and dp2.yn=1
join dwd.dwd_product_sort dp3 on dp3.id = dp2.fatherid and dp3.id in(1713, 1316) and length(wid)<9 and dp3.yn=1
join dwd.dwd_wms_warestock dww on dww.product_id = dw.wid
为什么我用第一个SQL,数据要查几分钟,今天运行好几个小时都没出数据,第二个SQL用了不到1秒就能出结果,请问是为什么?no_index到底改怎么用
create unique index DWD.DWD_WARE_INDEX1 on DWD.DWD_WARE (WID)
tablespace TS_DWD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index DWD.DWD_WARE_INDEX2 on DWD.DWD_WARE (WPID)
tablespace TS_DWD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index DWD.DWD_WARE_INDEX3 on DWD.DWD_WARE (CAIGOU)
tablespace TS_DWD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index DWD.DWD_WARE_INDEX4 on DWD.DWD_WARE (WFACTURER)
tablespace TS_DWD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
----------------
create index DWD.DWD_PRODUCT_SORT_INDEX1 on DWD.DWD_PRODUCT_SORT (ID)
tablespace TS_DWD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
from dwd.dwd_ware dw
join dwd.dwd_product_sort dp1 on dw.wpid = dp1.id and dp1.yn=1
join dwd.dwd_product_sort dp2 on dp2.id = dp1.fatherid and dp2.yn=1
join dwd.dwd_product_sort dp3 on dp3.id = dp2.fatherid and dp3.id in(1713, 1316) and length(wid)<9 and dp3.yn=1
join dwd.dwd_wms_warestock dww on dww.product_id = dw.wid
----------------------select /*+ no_index(dw DWD_WARE_INDEX2) */ dw.wid , dww.org_id ,dww.store_id
from dwd.dwd_ware dw
join dwd.dwd_product_sort dp1 on dw.wpid = dp1.id and dp1.yn=1
join dwd.dwd_product_sort dp2 on dp2.id = dp1.fatherid and dp2.yn=1
join dwd.dwd_product_sort dp3 on dp3.id = dp2.fatherid and dp3.id in(1713, 1316) and length(wid)<9 and dp3.yn=1
join dwd.dwd_wms_warestock dww on dww.product_id = dw.wid
为什么我用第一个SQL,数据要查几分钟,今天运行好几个小时都没出数据,第二个SQL用了不到1秒就能出结果,请问是为什么?no_index到底改怎么用
create unique index DWD.DWD_WARE_INDEX1 on DWD.DWD_WARE (WID)
tablespace TS_DWD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index DWD.DWD_WARE_INDEX2 on DWD.DWD_WARE (WPID)
tablespace TS_DWD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index DWD.DWD_WARE_INDEX3 on DWD.DWD_WARE (CAIGOU)
tablespace TS_DWD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index DWD.DWD_WARE_INDEX4 on DWD.DWD_WARE (WFACTURER)
tablespace TS_DWD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
----------------
create index DWD.DWD_PRODUCT_SORT_INDEX1 on DWD.DWD_PRODUCT_SORT (ID)
tablespace TS_DWD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
解决方案 »
- sqlldr导入csv数据文件到bonus表,这个表必须是空的吗?
- 存储过程
- ORACLE扩展表空间,用的是裸设备
- 一个itpub没人解决的问题
- 用dblink + trigger实现两个数据库表的同步, 但字符集不同怎末办?
- 在 pl sql 里错删除了table表,请问怎么能恢复?急,在线等带
- ---c# 怎么读出 和 写入 Oracle里 long 类型的字段???
- 简单问题求助。(it is simple!)
- 接触过ORACLE APPLICATIONS进来签名留言,同时讨论系统迁移。
- unix环境下如何启动oracle数据库?
- 初学者求教CSDN大哥指点该段存储过程的解释,非常感谢!
- 怎么根据主表来查询子表的数据?
SELECT STATEMENT, GOAL = ALL_ROWS 1308 1 82
TABLE ACCESS BY INDEX ROWID DWD DWD_WARE 1293 1 14
NESTED LOOPS 1308 1 82
NESTED LOOPS 15 1 68
NESTED LOOPS 13 1 61
MERGE JOIN CARTESIAN 11 1 50
PARTITION RANGE ALL 2 1 39
TABLE ACCESS FULL DWD DWD_WMS_WARESTOCK 2 1 39
BUFFER SORT 9 3024 33264
TABLE ACCESS FULL DWD DWD_PRODUCT_SORT 9 3024 33264
TABLE ACCESS BY INDEX ROWID DWD DWD_PRODUCT_SORT 2 1 11
INDEX RANGE SCAN DWD DWD_PRODUCT_SORT_INDEX1 1 1
TABLE ACCESS BY INDEX ROWID DWD DWD_PRODUCT_SORT 2 1 7
INDEX RANGE SCAN DWD DWD_PRODUCT_SORT_INDEX1 1 1
INDEX RANGE SCAN DWD DWD_WARE_INDEX2 2 3735
第二个的执行计划
SELECT STATEMENT, GOAL = ALL_ROWS 145970 1 82
TABLE ACCESS BY INDEX ROWID DWD DWD_PRODUCT_SORT 2 1 7
NESTED LOOPS 145970 1 82
NESTED LOOPS 145968 1 75
NESTED LOOPS 145966 1 64
NESTED LOOPS 145964 1 53
TABLE ACCESS FULL DWD DWD_WARE 145962 160811 2251354
TABLE ACCESS BY GLOBAL INDEX ROWID DWD DWD_WMS_WARESTOCK 0 1 39
INDEX RANGE SCAN DWD INDEX_WMS_WARESTOCK_WID 0 1
TABLE ACCESS BY INDEX ROWID DWD DWD_PRODUCT_SORT 2 1 11
INDEX RANGE SCAN DWD DWD_PRODUCT_SORT_INDEX1 1 1
TABLE ACCESS BY INDEX ROWID DWD DWD_PRODUCT_SORT 2 1 11
INDEX RANGE SCAN DWD DWD_PRODUCT_SORT_INDEX1 1 1
INDEX RANGE SCAN DWD DWD_PRODUCT_SORT_INDEX1 1 1
一般这种比较简单的SQL应该不需要hint的
会应用于基本函数的、基于B+树的、基于位图的、基于簇的索引等。