Oracle Procedure performance.

Question

0.00/5 (No votes)

See more:

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.

SQL

(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

SQL

id,item_code,item_desc

in 5 cases like..

SQL

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

Posted 7-Dec-13 6:14am

s#@!k

Updated 13-Dec-13 9:05am

Jörgen Andersson

v2

Add a Solution

Comments

[no name] 7-Dec-13 12:23pm

Ok I have first to mention I do not know Oracle...but I work a lot with SQL
a.) A lot of select statements have a where clause (which is really good, not to blow up the result set)
b.) What is the problem to define an index? If this is the problem then it ends in "I need a perfect solution but I can't make the basics suitable for this"

s#@!k 7-Dec-13 12:32pm

I don't know do i create index on varchar2(100) type column which has 6 lacks records.Do i create it in procedure?

[no name] 7-Dec-13 12:37pm

As I mentioned I do not know the deatils of Oracle. But generally creating an index is nothing more like "CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX index

ON table (col [, col …]);

E.g for an non unique ascending index for field f1 in table t1:

CREATE INDEX MyIndexName ON t1 (f1)

Jörgen Andersson 7-Dec-13 17:30pm

Take the DBA out the back and shoot him.
Now fix the column types and add appropriate indexes.

thatraja 9-Dec-13 7:34am

Agree

s#@!k 10-Dec-13 0:40am

Thank you so much for your response idle63,Jorgen Andersson and thatraja.It is a data ware house i can't force them to change the column definitions and hope that would n't be happen.Please welcome any other solutions.Your responses are more valuable for me..

Jörgen Andersson 10-Dec-13 3:22am

The column types aren't really very important for the performance, that's just messing up the data integrity.
But without indexes you can never achieve any performance.
Improve your question and add the query and the columns of the table you're querying and we'll try to tell you what index(es) you probably need.

s#@!k 13-Dec-13 14:32pm

Please have a look on the table structure and reply back any

CHill60 10-Dec-13 15:31pm

As Jorgen Andersson says it might be worth posting your sql -- "nearly 5 conditions" doesn't sound right to me. I agree with the posts about using indexes but you might gain some (little) improvement if the query is not written well.

s#@!k 13-Dec-13 14:31pm

please have a look on the query and reply back.

s#@!k 11-Dec-13 0:52am

Thank you so much.Please find the 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..(hope you ppl got clear idea)

Zoltán Zörgő 13-Dec-13 16:35pm

Just an opinion: "can't force them to change the column definitions". Well, you don't have to. Indexes are not changing table definition. If you pay them to provide you services, you are fully entitled to ask for index creation. If they are in-house providers, they have to support the same business as you so again, you are fully entitled to ask for an index.
If you are not in the position to negotiate, you should contact your senior leader and ask for support.
Believe us, no stored procedure will help you. You can optimize the code of the procedure to deal with the conditions you want to use, but the query response time will not depend on it. Such a query can't be optimized in an other way.

CHill60 14-Dec-13 5:23am

Virtual 5!

s#@!k 16-Dec-13 2:38am

Thank you so much..

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Answer 1 · 2013-12-13T09:18:00

Solution 1

It looks to me that just one added composite index would do the job:

SQL

CREATE INDEX MyTable_Composite_IX
ON MyTable (
    1_code
   ,2_code
   ,3_code
   ,4_code
   ,5_code
);

Posted 13-Dec-13 9:18am

Jörgen Andersson

Comments

s#@!k 16-Dec-13 2:39am

Thank you so much