Archives of the TeradataForum
Message Posted: Tue, 11 May 2004 @ 07:04:38 GMT
| 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.
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.
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.
Regards,
Victor
| 
