Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 11 May 2004 @ 07:04:38 GMT

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

Subj:   Re: Help on Join Indexes
From:   Victor Sokovin

On Mon, 10 May 2004 14:04:20 -0400, Anomy Anom wrote:

  I have a table with 250 million rows, and Im using ReportNet to display the output. It takes ages to display. So someone told me to use JOIN INDEX . Actually I dont know much about this subject. So could any one educate me on this subject :  

It sounds like you might be interested in the single-table Join Index. This is an object which stores aggregates and helps with queries which need exactly these aggregates or some part of them.

  a. Documents on Join Index  

Database Design and SQL Reference Vol. 4. Some examples there should not be read and understood "literally" so don't pay too much attention to the details in them. Instead, work in parallel on your own set of tables, JI and queries.

  b. Advantages  

Speeds up significantly queries which use the JI in their explain plans.

  c. Does it physically occupy space  

You bet. It is also necessary to collect statistics on JI. You cannot archive the JI and it can influence the archiving process for the master table, so you should always be able to restore the JI on your won, if need be. Fallback on JI can be defined and it can work quite well. Please see other recent (and less) threads in the archive for more information. There are lots of them!

  d. How does it get refreshed  

It will refresh with each DML. If all your DML is done in batches it might be a good idea to drop the JI before the batch job and rebuild it after all DML work is done.

  e. Can I partition  

Not in the current release. By definition (contains aggregates), though, the JI should be much smaller than the original table (this can depend on the requirements, of course) in the usual situations, so I think partitioning will not be required for a JI on the table of the size you mentioned.



  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019