-
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!
-
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.
-
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
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|