Home » SQL & PL/SQL » SQL & PL/SQL » Improving sql sentence (merged)
Improving sql sentence (merged) [message #421934] Fri, 11 September 2009 23:25 Go to next message
jcyp_71
Messages: 15
Registered: March 2009
Junior Member
Hello, i would like to know if there is a way to improve this sentence. It is executed through all day but it generates too much user I/O peaks:

SELECT A.CAMPOA
FROM TABLA A
WHERE DECODE(LENGTH(A.CAMPOB),6,A.CAMPOB,7,SUBSTR(CAMPOB,2,6),8,SUBSTR(CAMPOB,3,6)) >= DECODE(:b1,'A',LTRIM(RTRIM(TO_CHAR(:b2,'000000'))),LTRIM(RTRIM(TO_CHAR(:b2,'0000000'))))
AND DECODE(LENGTH(A.CAMPOB),6,A.CAMPOB,7,SUBSTR(CAMPOB,2,6),8,SUBSTR(CAMPOB,3,6)) <= DECODE(:b1,'A',LTRIM(RTRIM(TO_CHAR(:b5,'000000'))),LTRIM(RTRIM(TO_CHAR(:b5,'0000000'))))
AND NVL(A.CAMPOC,'N') = 'N'
AND NVL(A.CAMPOD,'N') = 'N'
AND A.CAMPOE || '' = :b1
AND A.CAMPOA > 0
Re: Improving sql sentence [message #421937 is a reply to message #421934] Fri, 11 September 2009 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
SELECT a.campoa
FROM   tabla a
WHERE  Decode(Length(a.campob),6,a.campob,
                               7,Substr(campob,2,6),
                               8,Substr(campob,3,6)) >= Decode(:b1,'A',Ltrim(Rtrim(To_char(:b2,'000000'))),
                                                                   Ltrim(Rtrim(To_char(:b2,'0000000'))))
       AND Decode(Length(a.campob),6,a.campob,
                                   7,Substr(campob,2,6),
                                   8,Substr(campob,3,6)) <= Decode(:b1,'A',Ltrim(Rtrim(To_char(:b5,'000000'))),
                                                                       Ltrim(Rtrim(To_char(:b5,'0000000'))))
       AND Nvl(a.campoc,'N') = 'N'
       AND Nvl(a.campod,'N') = 'N'
       AND a.campoe
           ||'' = :b1
       AND a.campoa > 0 


The use of functions precludes use of index.

Post EXPLAIN_PLAN
Re: Improving sql sentence [message #421938 is a reply to message #421934] Fri, 11 September 2009 23:42 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Cross/multi-posting does NOT improve quantity or quality of responses.
Re: Improving sql sentence (merged) [message #421940 is a reply to message #421934] Sat, 12 September 2009 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Tell us why you use " || '' ".

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Improving sql sentence (merged) [message #421963 is a reply to message #421934] Sat, 12 September 2009 09:48 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
create index a1 on tabla (
                             Decode(Length(campob),6,campob,
                                                     7,Substr(campob,2,6),
                                                     8,Substr(campob,3,6))
                           , Nvl(campoc,'N')
                           , Nvl(campod,'N')
                           , campoe||''
                           , campoa
                         )
/


Try this function based index. I am pretty sure you do not know what a function based index is so do some googling and find out.

Good luck, this is just an easy fix, the real issue may be database design was not done well as noted by the need for you to substr and decode all over the place.

If this works you should see a query plan that only accesses the index (at least based on this example). Should be very fast assuming your are not retrieving most of the rows in this table and this table is short.

Kevin
Re: Improving sql sentence (merged) [message #421994 is a reply to message #421963] Sat, 12 September 2009 20:02 Go to previous messageGo to next message
jcyp_71
Messages: 15
Registered: March 2009
Junior Member
Hello, thanks for your reply. I must say that i'm conscious of this bad sql sentence, i did not created it but i'm working in the database area and i must do anything to fix it or improve it, that's why i'm asking for your help.

-) In fact, this query does a full table scan, i know that the functions break whatever index.
-) It runs slowly and as i said before it generates too much user I/O peaks.
-) It is executed for about ten times in an hour.

I've been reading about functioned-based indexes, and i created one in a test db for DECODE(LENGTH(A.CAMPOB),6,A.CAMPOB,7,SUBSTR(CAMPOB,2,6),8,SUBSTR(CAMPOB,3,6)).......

On doing this, i got Index Range Scan and it runs faster, however, the application is OLTP and i dont' know if this index can affect the performance of database. The table is big and it is accessed frequently. I need your suggestions.

By the way, the datatypes of the columns are varchar2.

Thanks in advance.
Re: Improving sql sentence (merged) [message #421995 is a reply to message #421934] Sat, 12 September 2009 20:27 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>I need your suggestions.

Yet you continue to ignore Posting Guidelines & ignore request for additional information.

How many rows in the table?
About how many rows get returned (on the average)?

What or how much are you allowed to change to obtain better performance?
Re: Improving sql sentence (merged) [message #422025 is a reply to message #421934] Sun, 13 September 2009 15:01 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If you want to know if your OLTP system will work better or worse with a different set of indexes, the best way is to create them and find out.

You have test systems, get your boss to authorizer resources for such testing.

Good luck, Kevin
Previous Topic: unix time insertion into db?
Next Topic: Accept User Inputs
Goto Forum:
  


Current Time: Tue Dec 06 00:22:29 CST 2016

Total time taken to generate the page: 0.10262 seconds