Home » RDBMS Server » Performance Tuning » Sol req : Alternative way of rewriting the query (Oracle 10gr2)
Sol req : Alternative way of rewriting the query [message #514857] Wed, 06 July 2011 23:50 Go to next message
ganeshkn21
Messages: 36
Registered: June 2011
Location: bangalore
Member
hi,
The below queries retrieve the same number of records
i wanted to know if there is any way the below query
can be rewritten differently other than the alternatives which i have enumaerated below :

-----main query---

select * from test1 a where
(NVL (a.flag_state, NULL) < '7'
OR NVL (a.flag_state, NULL) > '7')
AND (NVL (a.flag_state, NULL) < '14'
OR NVL (a.flag_state, NULL) > '14')
AND (NVL (a.flag_state, NULL) < '4'
OR NVL (a.flag_state, NULL) > '4')
AND (NVL (a.flag_state, NULL) < '102'
OR NVL (a.flag_state, NULL) > '102')

----alternative 1 ------------
select * from test1 a where
a.flag_state<> '7'
AND a.flag_state <> '14'
AND a.flag_state <> '4'
AND a.flag_state <> '102'
----------alternative 2
select * from test1 a where
a.flag_state not in ( '7','14','4' ,'102')
-----
regards
Ganesh

Report message to a moderator

Re: Sol req : Alternative way of rewriting the query [message #514858 is a reply to message #514857] Wed, 06 July 2011 23:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

We don't have your data.
We don't have your tables.
We don't have your requirements.
We don't have your results.

Why do you expect answer when you provide NO useful details?

Report message to a moderator

Re: Sol req : Alternative way of rewriting the query [message #514860 is a reply to message #514857] Thu, 07 July 2011 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
NVL (a.flag_state, NULL) < '7'

Do you know the meaning/purpose of NVL function?
'7' is intended to be a string or a number?
Do you understand that '61' is less than '7'?

Regards
Michel

Report message to a moderator

Re: Sol req : Alternative way of rewriting the query [message #514983 is a reply to message #514860] Thu, 07 July 2011 09:41 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
It could be written many, many other ways such as:
select * from test1 a where 1=1 and
(NVL (a.flag_state, NULL) < '7'
OR NVL (a.flag_state, NULL) > '7') 
AND (NVL (a.flag_state, NULL) < '14'
OR NVL (a.flag_state, NULL) > '14')
AND (NVL (a.flag_state, NULL) < '4'
OR NVL (a.flag_state, NULL) > '4')
AND (NVL (a.flag_state, NULL) < '102'
OR NVL (a.flag_state, NULL) > '102')

But that, just as most of your above queries (particularly that ridiculous first one) doesn't make much sense to use.

Report message to a moderator

Re: Sol req : Alternative way of rewriting the query [message #515147 is a reply to message #514983] Fri, 08 July 2011 04:46 Go to previous messageGo to next message
ganeshkn21
Messages: 36
Registered: June 2011
Location: bangalore
Member
hi mike,
thanks for looking into this :
flag state is a varchar2 field
requirement is that we want to include the rows without the values ('14','7','4','102') and also include the rows which have null as flag state
actually the query was like the below and it went a lot of changes to become here is the changes .
I coulnot post the actual query of which this is a part since iam not permitted to do so. Anyway i isolated this part from the query which i know is the bottleneck of the issue.
The index on flag state is not being used by the queries below.
Cool The ridiculous query with nvl allover the query was suggested by the sql optimizer tool. Anyway the cost of the query came down and did help the performance a bit, but worked better than the <> & not in queries(as in alternative qry 1&2)

actual qry:
alternative 1
select * from test1 a where
a.flag_state<> '7'
AND a.flag_state <> '14'
AND a.flag_state <> '4'
AND a.flag_state <> '102'


then it was changed to this for performance issues
alternative 2
select * from test1 a where
a.flag_state not in ( '7','14','4' ,'102')

then now finally it is like this : Sad

select * from test1 a where
(NVL (a.flag_state, NULL) < '7'
OR NVL (a.flag_state, NULL) > '7')
AND (NVL (a.flag_state, NULL) < '14'
OR NVL (a.flag_state, NULL) > '14')
AND (NVL (a.flag_state, NULL) < '4'
OR NVL (a.flag_state, NULL) > '4')
AND (NVL (a.flag_state, NULL) < '102'
OR NVL (a.flag_state, NULL) > '102')

My request is only that if this piece of logic alone can we written differently i can check the timing and get back to u.
Thanks
Mike

Report message to a moderator

Re: Sol req : Alternative way of rewriting the query [message #515149 is a reply to message #515147] Fri, 08 July 2011 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
requirement is that we want to include the rows without the values ('14','7','4','102') and also include the rows which have null as flag state

Both queries fail as NULL is equal or not equal to nothing including NULL.
Correct way is (to directly convert what you say in SQL):
"( a.flag_state NOT IN ('7',...) OR a.flag_state IS NULL )"
or
"NVL(a.flag_state, '<something that can''t exist>') NOT IN ('7',...)"

"NVL(X, NULL)" is silly because it means if X is NULL then returns NULL.

Regards
Michel

Report message to a moderator

Re: Sol req : Alternative way of rewriting the query [message #515155 is a reply to message #514983] Fri, 08 July 2011 05:18 Go to previous messageGo to next message
ganeshkn21
Messages: 36
Registered: June 2011
Location: bangalore
Member
hi Mike,
just tried what u suggested :
Pls see the results:

old query( with NVL allover): turnaround time : 12 secs

sol 1:
and (flag_state not in ('7','14','4','102') or flag_state is null)
turnaround time : 12 secs

sol 2:
and (nvl(flag_state,'*') not in ('7','14','4','102')
turnaround time : 17 secs

A turnaround time of about 3-4 secs would be acceptible mike.

thanks
Ganesh Sad

Report message to a moderator

Re: Sol req : Alternative way of rewriting the query [message #515160 is a reply to message #515155] Fri, 08 July 2011 05:39 Go to previous messageGo to next message
ganeshkn21
Messages: 36
Registered: June 2011
Location: bangalore
Member
hi mike ,
just now tried another thing
---
and 'TRUE'=decode(flag_state, '7','FALSE','14','FALSE','4','FALSE','102','FALSE','TRUE')
---
also created a test_index on this expression (decode(flag_state, '7','FALSE' ......,'TRUE')
The explain plan uses this index with range scan but it seems to degrade the performance
turnaround time : 20 secs (degrading the perf)

thanks
Ganesh

Report message to a moderator

Re: Sol req : Alternative way of rewriting the query [message #515167 is a reply to message #515155] Fri, 08 July 2011 05:59 Go to previous message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
For all performances questions, Please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel

Report message to a moderator

Previous Topic: query optimization
Next Topic: Reg Segment advisor
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Nov 24 21:37:55 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.