Archives of the TeradataForum

Message Posted: Tue, 13 May 2003 @ 14:16:09 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- 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 V2R4.1.3.44

Transaction table: 123 million rows, total 119 columns
Look-up table: 100,000 rows, total 35 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.

	<Prev	Next>		<<First	<Prev	Next>	Last>>