| View previous topic :: View next topic |
| Author |
Message |
rrv
Beginner
Joined: 01 Nov 2004
Posts: 19
Topics: 6
|
 Posted: Sat Jul 29, 2006 11:51 pm Post subject: Select from Multiple Tables on same Primary Key |
 |
|
Hi
Please can someone help me with the following query in DB2 V7/8 for z/OS?
I understand that a JOIN on two or more tables begins by creating a result table, which is a cross product of the base tables, and then applies the filtering criteria. I would like to know if it is possible to retrieve multiple columns from two or more tables with matching primary key WITHOUT involving a cross product of the base tables.
Consider the following example:
| Code: | Table Name Primary Key Other Columns Related To
MASTER INDEX TBL IDENT NBR CONTROL PARMS
ATTRIBUTES TBL 1 IDENT NBR DEMOGRAPHICS
ATTRIBUTES TBL 2 IDENT NBR HISTORICAL DATA |
For a given IDENT NBR, assuming it exists in all the three tables and given the fact that there exists an index on IDENT NBR in all three tables, is there any way a SELECT would retrieve the required columns from these tables without involving a FULL SCAN of ATTRIBUTES TBL 1 & TBL 2 for each record in MASTER INDEX TBL?
NOTE: Though primary key is the same across different tables, the number of columns in each table is in excess of 100 and the tables hold unrelated data, merging these tables together is NOT an option.
Thank You!
Ram |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
|
| Posted: Sun Jul 30, 2006 9:32 am Post subject: |
|
|
| Quote: |
I understand that a JOIN on two or more tables begins by creating a result table, which is a cross product of the base tables, and then applies the filtering criteria
|
rrv,
Not quite true. The result set is simply based on your select clause. Even though your tables have 100 columns each and you are selecting only 10 columns , then the result set will have a total of 30 columns(10 columns for each table). However the question of full tablespace scan will depend on whether the indexes are used or not.
Another alternative is to unload the tables using DSNTIAUL and then use Easytrieve the match the tables to get the desired results.
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
rrv
Beginner
Joined: 01 Nov 2004
Posts: 19
Topics: 6
|
| Posted: Sun Jul 30, 2006 10:54 am Post subject: |
|
|
Thanks Kolusu for your response !
I agree to what you mentioned on the number of columns in the result set. I guess I need to take care of the following for better performance:
1) Ensure that access path uses index scan only, and
2) As you suggested, provide unload files for batch applications that are NOT sensitive to real time updates (batch cut-over processing).
Thanks Again
Ram |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
