Home » SQL & PL/SQL » SQL & PL/SQL » sql select query (Oracle 10.2.0.3)
sql select query [message #403979] Tue, 19 May 2009 23:25 Go to next message
mrpranab
Messages: 32
Registered: March 2005
Member
Hi All,

I have 4 tables named as tab1, tab2, tab3 and tab4. Table tab1 having record count 10 million records and table tab2, tab3 & tab4 having record count 1 million each.

I need to retrieve all data from table tab1 that does not exists in table tab2, tab3 and tab4 i.e. data exists in tab1 but does not exists in tab2, tab3 and tab4 tables.

I don't want to use NOT IN clause. Which other approach will be fine which gives the output faster and the performance of the query is better.

Thank you.
Re: sql select query [message #403984 is a reply to message #403979] Tue, 19 May 2009 23:31 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: sql select query [message #403986 is a reply to message #403979] Tue, 19 May 2009 23:37 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
You can use NOT EXISTS or rownum for this .The performance will depend on the type of data the table contains.
Re: sql select query [message #403988 is a reply to message #403979] Tue, 19 May 2009 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>The performance will depend on the type of data the table contains.

Provide reproducible benchmark test.
Re: sql select query [message #404003 is a reply to message #403979] Wed, 20 May 2009 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I don't want to use NOT IN clause

Why?

Regards
Michel
Re: sql select query [message #404058 is a reply to message #403979] Wed, 20 May 2009 04:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you enter a new topic, you have to enter a title. The purpose of this title is to make your post easier to find amidst the vast number of topics already available.
Now how self-explaining do you think your title is in a SQL forum?
Imagine your bookcase only having titles like "Book" "Lots of words", "Another book", "Sentences", etc. Would you know which is which?
Re: sql select query [message #404060 is a reply to message #403979] Wed, 20 May 2009 04:18 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

You can try out either minus operator or you can use not exists as compared to not in not exists works faster.

select a.field1 from tab1 a
where not exists ( select 1 from tab2 b
where b.field1 = a.field1)


similarly you can nest muliple tables inside each other.
Re: sql select query [message #404115 is a reply to message #403979] Wed, 20 May 2009 07:50 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
mrpranab wrote on Wed, 20 May 2009 00:25

I need to retrieve all data from table tab1 that does not exists in table tab2, tab3 and tab4 i.e. data exists in tab1 but does not exists in tab2, tab3 and tab4 tables.



Do you mean not in ALL the tables, or not in ANY of the tables? Irregardless of what you say, use NOT IN.
Re: sql select query [message #404123 is a reply to message #404115] Wed, 20 May 2009 08:14 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

select a.field1 from tab1 a
where not exists ( select 1 from tab2 b
where b.field1 = a.field1)
and not exists ( select 1 from tab3 c
where c.field1 = a.field1)
and not exists ( select 1 from tab4 d
where d.field1 = a.field1)


Try this out and let me know.This will solve your performance problem as i have practical experience .
Re: sql select query [message #404127 is a reply to message #404123] Wed, 20 May 2009 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This will solve your performance problem as i have practical experience .

Nice to see we have a great expert in SQL performances that can solve any problem without knowing anything on one's environment.

May I send you some of the SQL I received?

Regards
Michel
Re: sql select query [message #404227 is a reply to message #404123] Thu, 21 May 2009 00:55 Go to previous messageGo to next message
mrpranab
Messages: 32
Registered: March 2005
Member
Its working, thank you for all of your responses.
Re: sql select query [message #404237 is a reply to message #404227] Thu, 21 May 2009 01:38 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Michel dont write such sarcastic comments...i just wanted to help this guy out and i m trying my best to support him and its not a must that you should know environment properly...
Previous Topic: Cartesian Join
Next Topic: selecting random recordset
Goto Forum:
  


Current Time: Sat Dec 10 01:03:52 CST 2016

Total time taken to generate the page: 0.08489 seconds