Archives of the TeradataForum
Message Posted: Tue, 13 May 2003 @ 14:16:09 GMT
<-- Anonymously Posted: Tuesday, May 13, 2003 10:13 -->
I have trouble making the Partially Covering Join Index work. What we are trying to do is pre-join 2 tables -- 1 transaction table and 1 look-up table. Since there is no way to include more than 16 columns from each table, we tried to include as many columns from the transaction table and leave the rest columns, if needed, to use ROWID.
Systems: 16-node 5300 production, 4-node 5300 QA/DEV, both V2R188.8.131.52
Transaction table: 123 million rows, total 119 columns
Sel t1.col1 ,t1.col2 ,t1.col3 -- PI ... ,t1.col17 ,t2.col1 -- NUPI c1 ,t2.col2 -- NUPI c2 ,t2.col3 ... t2.col9 from trans_tbl t1 ,lookup_tbl r2 where t1.col1 = t2.col1 and t1.col2 = t2.col2 and other residual conditions ;
The look-up table t2 has a NUPI (col1, col2) almost unique. I tried to create the JI as:
create join index ji_trans_lu as sel ( t1.col1 , t1.col2 , t2.col1 , t2.col2 , t2.col3 ... , t2.col9 ) -- fixed part ,( t1.rowid -- pointer to trans_tbl to get t1.col17 , t1.col3 , t1.col4 , t1,col5 ... , t1.col16 ) -- repeated part from trans_tbl t1 ,lookup_tbl t2 where t1.col1 = t2.col1 and t1.col2 = t2.col2 and other residual conditions Primary Index(t1.col1, t1.col2) ;
The JI was created successfully but the optimizer did not use the JI. Any suggestions?
In the manual, it seems to imply that this feature can work for multi-table join index. However, I read Carrie's powerpoint presentation titled "Join Index Tips and Techniques". It stated "Must be a single table Join Index (pre-V2R5)". Does anyone have an example that works, or this is not going to work until V2R5?
Any comments/suggestions/information are highly appreciated.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|