Tuning a simple query

magesh_magi1 · #1 11-20-2008, 01:41 PM

Hi ,

Please help me in Tuning a simple query

Code:

Select * from tab1@dblink
where tab1.col1 in ( selct col1 from tab2)

Tab2 is having very less number of records where as Tab1 is having millions of records.
Index is not there in col1 in both the tables where as index is there for some other columns

We cannot join the tables, we may have duplicates in tab2

Tried some Hints but with no Result.

I tried even Driving_site but the results are not drastic..

Thanks,
Mag

rajavu · #2 11-20-2008, 04:58 PM

Is there any reason for not having index for field col1.

Anyway try using WHERE EXISTS clause.

magesh_magi1 · #3 11-21-2008, 05:51 AM

Tab2 is in live server, so we cannot add Index to it.
I tried WITH clause , result is not drastic..

simply_dba · #4 11-21-2008, 06:39 AM

What percent of the data is selected in the dataset.?
Can you test the query with the index on col1 on tab1 in a test environment?
Try to implement rajavu's advice
Can you post the statistics of the tables ?

rajavu · #5 11-21-2008, 09:21 AM

Quote:

I tried WITH clause , result is not drastic

WITH clause or WHERE EXISTS cluase ?