Usage of Join indexes?

Database

Usage of Join indexes?

Hi Experts,

As we know that Join Index is helpful in reporting when we have huge tables & complex joins

as it avoids the base tables access.

But there is limitation that most of the TTUs dont support it (other than tpump) ( when the table to be loaded has the JI defined on it).

then how to use it?

Please share your thoughts on this or correct me if i am wrong.

cheers!

Nishant

Tags (1)
6 REPLIES

Re: Usage of Join indexes?

Hi Nishant,

a very interesting question, well, there are lot of trade offs when you are deciding about physical implementation alternativs. I beleive when you go for Join indexes, you will have to consider all of these limitations. One possible solution to your query is to drop the JI before loading, and recreate them after loading with the utilites prohibited with JI. But obvioulsy this process will also be consume system resource. 

So it all depends upon the scnario, utilties you have, data refresh period required, improvement you will get with JI, and lot of other things.

Khurram
N/A

Re: Usage of Join indexes?

Nishant,

We use quite a few JIs here. Fortunately we use staging tables to apply changes to our base tables. So when updating,inserting or deleting  the ji is alway supported.

Rglass

Re: Usage of Join indexes?

Thanks khurram & Rglass. 

so what i got from the above discussion is -->

a)  we can load the data from our source files to staging tables 

through TTU (mload,fload) ( as there in no JI defined on the staging tables) .

b) For loading from staging tables to base tables, we can use bteq ( as it allows JI).

Please correct me if i am wrong..

Cheers!

Nishant

Re: Usage of Join indexes?

Nishant,

You have made a great explaination. Yes this is a good methodology.

Khurram

Re: Usage of Join indexes?

Thanks Khurram..

Just wanted to know , when we would use Bteq to load from staging table to base tables,

there is a disadvantage that it would be slower in comprasion of directly loading from Source file to base table( using mload/fload) but this gives the flexibility for creating JI.

Is there any way to make it better or any work around?

Cheers!

Nishant

Re: Usage of Join indexes?

Nishant,

I believe within Teradata you have a universe of optimizations. There are plenty of ways avaialble to optimize BTEQ for loading purposes.

You can start optimizations from your SQL SELECT within BTEQ, make the use of exlain and optmize the joins as much as you can, you can use drived tables and volatile tables in order to avoid lot of spool files, one you SELECT is efficient you BTEQ will improve much. Other considrations can be to choose you indexes to make the insert efficient and avoid Secondary Indexes on the target table before loading.

Khurram