Query Design Question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Query Design Question

  1. #1
    Join Date
    Feb 2001
    Location
    Master Control
    Posts
    86

    Query Design Question

    i have two tables which have about 40 columns. the tables are identical. basically one is a copy of another. i can edit the copy, but not the original table. occasionally i will need to run a query which will see if there are any matching records. 4 of the columns are indexed, however the other 36 are not. i was going to write a query like this...

    select a.col1
    form table_a a, table_b b
    where a.index_col1 = b.index_col1
    and a.index_col2 = b.index_col2
    and a.index_col3 = b.index_col3
    and a.index_col4 = b.index_col4
    and a.non_index_col1 = b.non_index_col2
    and a.non_index_col2 = b.non_index_col2
    and a.non_index_col3 = b.non_index_col3
    etc... on to 36

    i have a feeling this is going to really tax the system so i want to understand 1) if my assumption is correct, 2) why and 3) what is a good alternative to this?

    thanks in advance!

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    If you are looking for fully matching rows you do not have a lot of alternatives.

    I'll let the query do FTS on one of the tables, lets say table_a and use table_b as a lookup table; try setting a hint on your most restrictive index on table_b.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,443

    Talking Intersect?

    Maybe you could use intersect to figure out the matching rows.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    mmmhhh... doesn't intersect returns all distinct rows selected for each one of the two intersected queries?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,443

    Cool

    Quote Originally Posted by PAVB
    mmmhhh... doesn't intersect returns all distinct rows selected for each one of the two intersected queries?
    The INTERSECT operator returns only those "matched" rows returned by both queries:

    Code:
    SQL> With T1 As (
      2  Select 1 From Dual Union
      3  Select 2 From Dual Union
      4  Select 3 From Dual Union
      5  Select 4 From Dual)
      6      ,T2 As (
      7  Select 7 From Dual Union
      8  Select 6 From Dual Union
      9  Select 5 From Dual Union
     10  Select 4 From Dual Union
     11  Select 9 From Dual Union
     12  Select 3 From Dual)
     13  Select * From T1
     14  Intersect
     15* Select * From T2
    SQL> /
    
             1
    ----------
             3
             4
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You are right, I stand corrected.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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



Click Here to Expand Forum to Full Width