Archives of the TeradataForum

Message Posted: Fri, 19 Jan 2001 @ 16:19:03 GMT

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

Dear All,

I have a problem which I cannot seem to resolve.

Is it possible to build mulitple multi-table join indexes for a single base table which will join with each other to resolve a query?

For example: 1 atomic fact table (Primary Index of this table is a 6 part key created from the lowest levels of each of the 6 dimension tables)

6 dimension tables (average of 9 levels of data each)

6 Join Indexes which join the atomic fact table to each individual dimension table and stores all the levels of each of the dimension tables. Each of these join indexes will have the same Primary Index as the atomic fact table.

What I want to happen: At run time, when the user executes a query, the large fact table will not need to be joined to the dimension tables because a Join Index has already been created for each of the dimensions. At this point, a sort of 'Dynamic Bit-Map Index' could be created from the results of the resolution of each of the 6 Join Indexes. The 'Dynamic Bit-Map' result set would create a spool file which would contain only the 'Primary Indices' which correspond to the same Primary Index of the Atomic Fact Table. The spool file and the Atomic Fact table would be joined, and the resulting records could be aggregated and returned to the user.

However, the Teradata optimizer will not use any of the Join Indexes that I have created because they do not contain all of the fields requested by the queries that are being created (which I have no control of, these queries are generated by a sepereate front-end tool.)

Am I asking for too much? Probably, but I though it sounded kinda cool...

I have tested a Join Index solution which creates only 1 Join Index via joining the atomic fact table to all 6 dimension tables. This solution works wonderfully, however, I am limited by the 32 column restriction of a Join Index. Our new user requirements involve ~54 levels of data (6 dim * 9 levels per dimension.)

Any thoughts? I would love to hear something from any of the NCR folks working on V2R5.

Our fact tables contain an average of 10 million records, and the user requires a result in ~15 seconds. No 3rd normal form approach comes close to meeting this user requirement (atleast not on our box.)

Thanks,

Claybourne Barrineau

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