Archives of the TeradataForum

Message Posted: Wed, 29 Jan 2014 @ 15:16:23 GMT

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

<-- Anonymously Posted: Wednesday, January 29, 2014 10:06 -->

Dear TD Gurus,

I have an issue with Join Index which I recently encountered at work.

Basically, here's the issue. I have a big table ( > 3T , 5B rows ) A. I have another table B ( 2 Million rows. Small.).

Let's say table A's schema is like this :

     Create multiset table A {
        integer a1,
        integer a2,
        integer a3,
        integer a4,
        integer a5,
        integer a6,
        integer a7,
        integer a8
     } primary index (a1);

     Create multiset table B {
        integer b1,
        integer b2,
        integer b3,
        integer b4,
        integer b5
     } primary index (b1,b2,b3);

The sql looks like this :

     Sel A.a5, A.a6, A.a7, A.a8 , B.b4, B.b5 from B left outer join A On B.b1 = A.a2 And B.b2 = A.a3 And b.b3 = A.a4;

The join index I created by using ROWID ( to save some space) looks like this :

     Create join index test.jidx_abtable1 as Sel a2,a3,a4, rowid
     From A
     Primary index (a2,a3,a4);

But the optimizer did not use this Join index for the sql I listed above. Instead, it chose a FTS on A.

But, when I changed the Left outer Join to inner Join , the join index is then chosen by the Optimizer.

I also created another join index :

     /* (this will use about 50% more space, and that's why I tried the ROWID,
     but it did not work for LOJ)   */
     Create join index test.jidx_abtable2 as
     Sel a2,a3,a4, a5,a6,a7,a8
     From A
     Primary index (a2,a3,a4);

I found out that this covered join index jidx_abtable2 is always picked by Optimizer , be the SQL is Left Outer Join or an Inner Join.

I am puzzled here regarding the ROWID Join Index. In TD manual , it did not mention anything about this.

Thanks a lot for your kind helps.

Appreciate all your answers.

Best

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