1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Tuning a simple query

Discussion in 'SQL PL/SQL' started by magesh_magi1, Nov 20, 2008.

  1. magesh_magi1

    magesh_magi1 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi ,

    Please help me in Tuning a simple query

    Code (Text):
    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
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Is there any reason for not having index for field col1.

    Anyway try using WHERE EXISTS clause.
     
  3. magesh_magi1

    magesh_magi1 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Tab2 is in live server, so we cannot add Index to it.
    I tried WITH clause , result is not drastic..
     
  4. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    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 ?
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    WITH clause or WHERE EXISTS cluase ?
     
  6. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    Try this

    Select * from tab1@dblink
    where exists ( selct col1 from tab2 where tab1.col1 = tab2.col1 )
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You did genrate a query plan to see how the work is being done and where the bottlenecks might be? Post that plan so we can all know where you should be directing your efforts.