在我看来只有一个添加的复合索引可以完成这项工作：

  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
);