Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103

    Unanswered: Exception Join - Query Tuning

    I'm doing a simple exception join query and it takes lot of time to compute the result.
    Query Used:
    select * from tab1
    where not exists
    (
    select col1 from tab2 where tab1.col1 != col1
    )

    I have indexes on col1 on both tables and statistics are updated but
    still it takes some to execute this query. tab1 has 7 Million rows and
    tab2 has 10,000 rows.

    The Access plan is as below
    Access Plan:
    -----------
    Total Cost: 4.99386e+06
    Query Degree: 1

    Rows
    RETURN
    ( 1)
    Cost
    I/O
    |
    1
    DTQ
    ( 2)
    4.99386e+06
    33855.8
    |
    1
    GRPBY
    ( 3)
    4.99386e+06
    33855.8
    |
    1.64536e+07
    NLJOIN
    ( 4)
    4.99211e+06
    33855.8
    /---+---\
    3.05148e+06 5.392
    IXSCAN FILTER
    ( 5) ( 6)
    62147.5 105.186
    33847.8 8
    | |
    3.05148e+06 134.8
    INDEX: CARDP1IN TBSCAN
    I2 ( 7)
    105.13
    8
    |
    134.8
    SORT
    ( 8)
    105.115
    8
    |
    1348
    IXSCAN
    ( 9)
    104.104
    8
    |
    1349
    INDEX: CARDP1IN
    I1


    Thanks,
    Vijesh

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Vijesh,
    Is that query correct? It looks like it will return all rows in tab1 unless all values for col1 in tab2 happen to match one roe in tab1.

    Andy

  3. #3
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    Yes Andy, This is my requirement. Is there a way to fine tune this query?

    Vijesh.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Lemme give a try :

    This is what I understand from the query -

    If there are no rows in tab2 - return all rows in tab1

    If there is only 1 distinct value of col1 in tab2 - return all rows in tab1 that match this distinct value (will return 0 rows if that col1 value is not in tab1)

    If there are more than 1 distinct value of col1 in tab2 - return 0 rows

    If this is correct - then

    given col1 in both tab1 and tab2 are indexed, the follwoing could be a solution

    with temp1(col1,c) as
    (
    select col1,count(1) from tab2 group by col1
    ),
    temp2(cnt) as
    (
    select count(*) from temp1
    )
    select * from tab1 where 0=(select cnt from temp2)
    union all
    select * from tab1 where 1=(select cnt from temp2)
    and col1 in (select col1 from temp1)
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    Yes, Your assumption is correct. Let me try your query and let you know.

    Thanks again.

  6. #6
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    Thanks guys, the suggested query works fine.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Would you mind posting the new access plan, please?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •