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!

Oracle Correlated Query Help

Discussion in 'SQL PL/SQL' started by shabir46, Sep 4, 2010.

  1. shabir46

    shabir46 Active Member

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

    Please can you help me in re-structuring this query? .Details are given below.

    I have 2 tables as shown below and data is like this.

    Position
    COD IND
    AAA N
    BBB N
    CCC N
    DDD Y
    EEE Y .....

    Distance
    orig dest
    AAA BBB
    BBB CCC
    AAA CCC

    I need to create the records like this

    start end
    DDD AAA
    DDD BBB
    DDD CCC
    EEE AAA
    EEE BBB
    EEE CCC
    AAA DDD
    BBB DDD...

    The query which i am using now for this is and its taking ages to complete since the table distance have lakhs of records.
    Code (SQL):

    SELECT   p.code AS START, P1.CODE AS END
      FROM   POSITION p, POSITION p1
     WHERE   P.CODE != P1.CODE AND (P.ind = 'Y' OR P1.IND = 'Y')
             AND NOT EXISTS
                   (SELECT   1
                      FROM   distance d
                     WHERE   (d.orig = p.code OR d.dest = p.code)
                             AND (d.orig = p1.code OR d.dest = p1.code))
    Is there some other way that we an use to remove this correlated query as this correlation makes this query time consuming.

    Please help.

    Thanks and Regards,
    Shabir
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Please help in re-write this co-related query?

    What indexes have been created on these tables?
     
  3. shabir46

    shabir46 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Re: Please help in re-write this co-related query?

    Hi Index is there for the columns orig and dest columns of the table distance.

    I am looking out for an alternative query avoiding the correlation
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Please help in re-write this co-related query?

    You really don't need to change the query, you should instead create an index on code in the position table since there are more records in that table than in distance, according to your post.