| Help with searching in large tables [message #9891] |
Thu, 11 December 2003 17:11  |
rzkman
Messages: 2
Registered: December 2003
|
Junior Member |
|
|
Hi all,
I am a SQL novice and would appreciate any tips any tips and tricks for the following problem. I have two Oracle 9i instances on different servers, lets call them INSA and INSB.
I am running a select statement from INSA which is a join of three local tables and returns values including user_name, emp_id. This select returns 42,000 rows.
INSB contains HR information and I have another select statement using a dblink which brings back a list of emp_id's that are current employees. This list has 56,000 rows.
What I am trying to do is identify the list of users from INSA that are is not contained in the list of current employees from INSB. I have tried doing this using...
select user_name, emp_id, .....
from INSA_table1.........
where
conda and condb and condc
and emp_id not in (
select emp_id from INSB_table1@INASB
where cond
)
PROBLEM - As we are talking about so many rows, this SQL logic currently takes ages and doesn't even complete overnight.
RESTRICTION - I currently would like to get rid of the dblink and bring the data back into a local table on INSA, but getting the access to create tables on INSA is taking a while.
THOUGHTS - I tried to use a 'exists' statement, but can't get it to work. This should save some time in theory as it won't scan all the entries in the subselect if it has found a hit. But the 'exists' condition doesn't seem to accept 'not exists'
Any help would be appreciated. If you can think of any way to speed it up without having to do any data creation locally that would be great. Otherwise I will still be open to ideas utilising local views/tables which can be achieved when my access gets approved.
|
|
|
|
| Re: Help with searching in large tables [message #9892 is a reply to message #9891] |
Thu, 11 December 2003 20:50  |
MHE
Messages: 4
Registered: August 2003
|
Junior Member |
|
|
Try using a MINUS:select t.user_name
, t.emp_id, .....
from INSA_table1 <B>t</B>
....
<B>, ( select emp_id
from INSA_table1
minus
select emp_id
from INSB_table1@INSAB
where cond
) x</B>
where conda
and condb
and condc
<B>and t.emp_id = x.emp_id</B>
Another solution
Might be using an exist:select user_name, emp_id, .....
from INSA_table1 <B>t</B>.........
where
conda and condb and condc
and <B>not exists</B>( select emp_id
from INSB_table1@INASB
where <B>emp_id = t.emp_id</B>
and cond
)
HTH,
MHE
|
|
|
|
|
|
| Re: Help with searching in large tables [message #9924 is a reply to message #9891] |
Sun, 14 December 2003 07:23 |
jayesh
Messages: 6
Registered: December 2003
|
Junior Member |
|
|
take the minus of both of the result sets insted of
not in clause
select user_name, emp_id, .....
from INSA_table1.........
where
conda and condb and condc
and emp_id in
(
select emp_id from insta_table1
minus
select emp_id from INSB_table1@INASB
where cond
)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
