| Rewriting a sql with NOT to avoid the NOT and OR clause [message #640251] |
Sat, 25 July 2015 11:19  |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
Hi,
I am trying to rewrite a sql having a not and a OR clause to avoid both the OR and NOT clauses. Can someone help on it...it is more like an attempt to first have a rewrite and then to validate if that performs better. But the first thing is to have the rewrite done. To mimic the big sql that I have to rewrite into a simple but equally representative testcase, I have the following one:
SELECT *
FROM emp
WHERE NOT ( (deptno = 10 AND ename = 'KING')
OR (DEPTNO = 20 AND ENAME = 'SMITH'))
ORDER BY 2
Can somneone help on suggesting how to best rewrite this to avoid both NOT and the OR...the one way I could think of is as follows but I think there may be better ways to accomplish such rewrite.
SELECT * FROM emp
MINUS
( SELECT *
FROM emp
WHERE (deptno = 10 AND ename = 'KING')
union
select * from emp
WHERE
(DEPTNO = 20 AND ENAME = 'SMITH')
)
ORDER BY 2
Thanks,
OrauserN
[Updated on: Sat, 25 July 2015 11:22] Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
