| a lot of 'EXISTS' in query [message #482660] |
Mon, 15 November 2010 04:04  |
mape
Messages: 298
Registered: July 2006
Location: Slovakia
|
Senior Member |
|
|
Is a possible to re-write this query with a quite lot of EXISTS
in order to make a better performance ?
select co.customer,
case when exists (select 'X'
from the_table2
where contract_key = co.contract_key
and service_name = 'VPN') then 1
else 0 end VPN,
case when exists (select 'X'
from the_table2
where contract_key = co.contract_key
and service_name = 'VOICE MAIL') then 1
else 0 end VMS,
case when exists (select 'X'
from the_table2
where contract_key = co.contract_key
and service_name = 'E-mail Expres') then 1
else 0 end "Email Express",
case when exists (select 'X'
from the_table2
where contract_key = co.contract_key
and service_name = 'Medzi nami') then 1
else 0 end CUG,
case when exists (select 'X'
from the_table2
where contract_key = co.contract_key
and service_name = 'FunDial') then 1
else 0 end "Fun Dial",
case when exists (select 'X'
from the_table2
where contract_key = co.contract_key
and service_name = 'GPRS Intranet') then 1
else 0 end "GPRS Intranet",
case when exists (select 'X'
from the_table2
where contract_key = co.contract_key
and service_name = 'Instant Messaging') then 1
else 0 end inst_m,
case when exists (select 'X'
from the_table2
where contract_key = co.contract_key
and service_name = 'Kde je') then 1
else 0 end kde,
case when exists (select 'X'
from the_table2
where contract_key = co.contract_key
and service_name = 'MMS') then 1
else 0 end MMS,
case when exists (select 'X'
from the_table2
where contract_key = co.contract_key
and service_name = 'Notify Me') then 1
else 0 end "Notify Me",
case when exists (select 'X'
from the_table2
where contract_key = co.contract_key
and service_name = 'PC Messenger SMS') then 1
else 0 end "PC Messenger SMS",
case when exists (select 'X'
from the_table2
where contract_key = co.contract_key
and service_name = 'ROAMING') then 1
else 0 end Roaming,
case when exists (select 'X'
from the_table2
where contract_key = co.contract_key
and service_name = 'UMTS') then 1
else 0 end UMTS,
case when exists (select 'X'
from the_table2
where contract_key = co.contract_key
and service_name = 'MI3') then 1
else 0 end WAP,
case when exists (select 'X'
from the_table2
where contract_key = co.contract_key
and service_name = 'Zmeškané hovory') then 1
else 0 end "Who Called Me",
case when exists (select 'X'
from the_table2
where contract_key = co.contract_key
and service_name = 'BLACKBERRY') then 1
else 0 end Blackberry,
case when exists (select 'X'
from the_table2
where contract_key = co.contract_key
and service_name = 'MNP') then 1
else 0 end MNP
from the_table1 co
Thanks
[Updated on: Mon, 15 November 2010 04:07] Report message to a moderator |
|
|
|
|
|
| Re: a lot of 'EXISTS' in query [message #482662 is a reply to message #482661] |
Mon, 15 November 2010 04:22  |
mape
Messages: 298
Registered: July 2006
Location: Slovakia
|
Senior Member |
|
|
There is no unique key on THE_TABLE2 but it could be contract_key and service_name.
There is just index on THE_TABLE2(CONTRACT_KEY)
and index on THE_TABLE1 (CONTRACT_KEY).
|
|
|
|
|
|
| Re: a lot of 'EXISTS' in query [message #482673 is a reply to message #482660] |
Mon, 15 November 2010 05:03 |
 |
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Something like:
select co.customer,
nvl(max(decode(service_name, 'VNP', 1, 0)),0) VPN,
...
from the_table1 co, the_table2 c2
where c2.contract_key (+) = co.contract_key
and c2.service_name (+) in ('VPN',...)
group by co.customer
/
Regards
Michel
|
|
|
|
| Re: a lot of 'EXISTS' in query [message #482691 is a reply to message #482673] |
Mon, 15 November 2010 07:44 |
mape
Messages: 298
Registered: July 2006
Location: Slovakia
|
Senior Member |
|
|
When I put all of the service_name into the sql below:
and c2.service_name(+) IN ('VPN' , 'VOICE MAIL', 'E-mail Expres' , 'Medzi nami', 'FunDial' , 'GPRS Intranet',
'Instant Messaging' , 'Kde je' , 'MMS','Notify Me',
'PC Messenger SMS' , 'ROAMING', 'UMTS', 'MI3',
'Zmeškané hovory', 'BLACKBERRY' ,'MNP' )
it gives me an error:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
[Updated on: Mon, 15 November 2010 09:41] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: a lot of 'EXISTS' in query [message #482702 is a reply to message #482691] |
Mon, 15 November 2010 09:42 |
|
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:it gives me an error:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
This is the problem when you don't give a test case, we can't test our query and so give invalid answer.
Use standard OUTER JOIN syntax and not Oracle (+) one.
Regards
Michel
[Updated on: Mon, 15 November 2010 09:42] Report message to a moderator |
|
|
|
| Re: a lot of 'EXISTS' in query [message #482729 is a reply to message #482702] |
Mon, 15 November 2010 11:43 |
mape
Messages: 298
Registered: July 2006
Location: Slovakia
|
Senior Member |
|
|
Quote:
Is there any allowable value of service_name you want the query to ignore?
No, just table the_table1 consist of customers and the table the_table2
consists of services joined by contract_key.
There exists customers in the the_table1 which are have no any service from the_table2.
I just want to write select where result would be all customers from the_table1
with feature of service from the_table2. Something like:
contract_key VPN "VOICE MAIL" "E-mail Expres" ...
----------------------------------------------------
12345 1 0 1
[Updated on: Mon, 15 November 2010 11:44] Report message to a moderator |
|
|
|
| Re: a lot of 'EXISTS' in query [message #482732 is a reply to message #482729] |
Mon, 15 November 2010 11:49 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
If every allowable value of service_name is going to be covered in the select then you might be able to skip the in list.
However it might be safer to keep it (in case someone adds a new allowed service_name and doesn't update the query). Use the ANSI outer-join syntax as Michel suggested.
|
|
|
|
| Re: a lot of 'EXISTS' in query [message #482740 is a reply to message #482729] |
Mon, 15 November 2010 12:14 |
|
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:I just want to write select where result would be all customers from the_table1
with feature of service from the_table2. Something like:
Do as I said.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
