Home » SQL & PL/SQL » SQL & PL/SQL » Replacing IN with EXISTS
Replacing IN with EXISTS [message #273514] Wed, 10 October 2007 14:20 Go to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi, I'd like to know if you could help me with this:

I have a VPD function that returns a predicate like this:
v_agencias := pkgfuncldap.get_agency;
RETURN '(to_char(OFCAKCLIFAC) in ' || v_agencias || ')';

And I must replace the 'IN' operator with an 'EXISTS' operator, this is the code I've done, but I don't know how to make it work as an 'IN' would.

CREATE OR REPLACE FUNCTION vpd_ext (
p_schema IN VARCHAR2,
p_object IN VARCHAR2
)
RETURN VARCHAR2
IS
TYPE Agency_List IS TABLE OF VARCHAR2(100);
t_agency Agency_List;
element BINARY_INTEGER;
BEGIN
v_agency VARCHAR2(100):= pkgfuncldap.get_agency;
Agency_List := Agency_List(v_agency);
END vpd_ext;
/

Could you help me?. Thank you in advance.
Re: Replacing IN with EXISTS [message #273532 is a reply to message #273514] Wed, 10 October 2007 14:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I must replace the 'IN' operator with an 'EXISTS' operator

Why?

Regards
Michel
Re: Replacing IN with EXISTS [message #273716 is a reply to message #273514] Thu, 11 October 2007 08:41 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
Because my boss told me that having an IN operator makes the database check every value of that column with all the elements in the collection. So, he asked me to replace it with an EXISTS that supposedly doesn't. Is he right?. How could I change it?.
Re: Replacing IN with EXISTS [message #273721 is a reply to message #273716] Thu, 11 October 2007 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no functional difference between IN and EXISTS.
Oracle optimizer can choose the former or the latter, actually the best one, depending on statistics, you don't have to care about this.

Regards
Michel
Re: Replacing IN with EXISTS [message #273742 is a reply to message #273514] Thu, 11 October 2007 09:42 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
Yes, but looking through the tkprof's it seems to be that the query is consuming a lot having that IN operator, so, they asked me to change it for an EXISTS.
Re: Replacing IN with EXISTS [message #273747 is a reply to message #273514] Thu, 11 October 2007 09:52 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Did you do a simple test with exists and tkprof it to see the differences before you do a change just because.

Re: Replacing IN with EXISTS [message #273786 is a reply to message #273514] Thu, 11 October 2007 12:23 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi, I'll do that, but I don't know how to replace the IN operator for an EXISTS operator. Could you help me?.
Re: Replacing IN with EXISTS [message #273788 is a reply to message #273514] Thu, 11 October 2007 12:29 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/conditions012.htm#sthref2849
Re: Replacing IN with EXISTS [message #273791 is a reply to message #273786] Thu, 11 October 2007 12:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't have to make test.
Re-read that
Michel Cadot wrote on Thu, 11 October 2007 15:54

There is no functional difference between IN and EXISTS.
Oracle optimizer can choose the former or the latter, actually the best one, depending on statistics, you don't have to care about this.

You could write it with IN, you could write with EXISTS, optimizer doesn't care it will change what you wrote with the one it wants.

Regards
Michel
Re: Replacing IN with EXISTS [message #278812 is a reply to message #273514] Mon, 05 November 2007 14:52 Go to previous messageGo to next message
Seshagiri
Messages: 13
Registered: October 2007
Location: United Kingdom
Junior Member

Functionally there should be any difference anyway or else you don't want to rewrite in the first place.

IN can be replaced with EXISTS.

Oracle will make the decision which is optimal.

However be more careful while replacing

NOT EXISTS with NOT IN especially when the column is defined as nullable as there is a potential for changing the required answer.

Regards,
Sesha

Re: Replacing IN with EXISTS [message #278816 is a reply to message #278812] Mon, 05 November 2007 15:09 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

NOT EXISTS with NOT IN especially when the column is defined as nullable as there is a potential for changing the required answer.

This is fully out of topic and can just confuse OP.

Regards
Michel

[Updated on: Mon, 05 November 2007 15:10]

Report message to a moderator

Previous Topic: insufficient privileges !!!!!
Next Topic: single row subquery returns more than one row
Goto Forum:
  


Current Time: Fri Dec 02 20:32:04 CST 2016

Total time taken to generate the page: 0.28704 seconds