Oracle程序性能。 [英] Oracle Procedure performance.
本文介绍了Oracle程序性能。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我写了oracle 11g程序,它将根据条件选择记录。
问题是我的表列全部是没有索引的varchar2类型,我以后也不能指望。
我的程序需要根据同一个表中where子句的近5个条件返回巨大的结果集。请帮助加速我的程序结果集。
表结构。
(id(number(PK)),
item_code( varchar ),
item_desc( varchar ),
1_code( varchar ),
1_desc( varchar ),
2_code( varchar ),
2_desc( varchar ),
3_code( varchar ),
3_desc( varchar ),
4_code( varchar ),
4_desc(< span class =code-keyword> varchar ),
5_code( varchar ),
5_desc( varchar ));
我的查询需要返回
id,item_code,item_desc $ p 5个案例中的$ p> ..
其中 1_code = 1_code_in;
其中 1_code = 1_code_in 和 2_code = 2_code_in;
其中 1_code = 1_code_in 和 2_code = 2_code_in 并且 3_code = 3_code_in;
直到5_code
解决方案
在我看来只有一个添加的复合索引可以完成这项工作: CREATE INDEX MyTable_Composite_IX
ON MyTable(
1_code
,2_code
,3_code
,4_code
,5_code
);
Hi,
I have written oracle 11g procedure which will select records based on where conditions.
Problem is my table columns all are varchar2 types with has no index and I can't expect in future also.
My procedure need to return huge result set based on nearly 5 conditions on where clause from the same table.Please help in speed up my procedure result set.
table structure.
(id(number(PK)),
item_code(varchar),
item_desc(varchar),
1_code(varchar),
1_desc(varchar),
2_code(varchar),
2_desc(varchar),
3_code(varchar),
3_desc(varchar),
4_code(varchar),
4_desc(varchar),
5_code(varchar),
5_desc(varchar));
My query need to return
id,item_code,item_desc
in 5 cases like..
where 1_code=1_code_in;
where 1_code=1_code_in and 2_code=2_code_in;
where 1_code=1_code_in and 2_code=2_code_in and 3_code=3_code_in;
till 5_code
解决方案 It looks to me that just one added composite index would do the job:CREATE INDEX MyTable_Composite_IX
ON MyTable (
1_code
,2_code
,3_code
,4_code
,5_code
);
这篇关于Oracle程序性能。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文