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  |
 |
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
|
|
|
|
|
|
|
|
| Re: Sol req : Alternative way of rewriting the query [message #514983 is a reply to message #514860] |
Thu, 07 July 2011 09:41  |
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.
|
|
|
|
| Re: Sol req : Alternative way of rewriting the query [message #515147 is a reply to message #514983] |
Fri, 08 July 2011 04:46 |
|
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.
 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 : 
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
|
|
|
|
| Re: Sol req : Alternative way of rewriting the query [message #515149 is a reply to message #515147] |
Fri, 08 July 2011 04:54 |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
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
|
|
|
|
|
|
| Re: Sol req : Alternative way of rewriting the query [message #515160 is a reply to message #515155] |
Fri, 08 July 2011 05:39 |
|
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
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 17:56:07 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
