Archives of the TeradataForum

Message Posted: Thu, 18 Jul 2002 @ 11:58:18 GMT

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

All,

I have a table (survey data) with over 1 Billion records and a 6 part UPI. Only 1 NUSI exists and stats only exist on the PI and NUSI. The user must specify all 6 parts of the UPI (enforced via the reporting tool) if they access this table.

Currently, if the user runs the below query, the Optimizer chooses to do a full table scan. This can take up to 30 minutes (even though only 4 distinct records have been selected.) As it turns out, the Optimizer has an issue with resolving mulitple in-lists into PI retrieval statements.

Select *
From TableA
Where ColumnA = (182)
and ColumnB in (1,6)
and Geo_Area_ID in (1049,1107)
and Csvy_Rspn_ID = (101954)
and Pop_Sub_Grp_ID = (100024)
and Csvy_Prod_ID = (100310)

We have worked around this problem (via the use of key tables embedded into a non-base table view of this 1 Billion record table) which "encourages" the optimizer to always perform a PI retrieveal versus a full-table scan. My concern is that this "encouragement" could stop working with future releases of Teradata.

Last year, I submitted an incident (RECA3C4S8) which resulted in DR 54401; however, as far as I know, this problem hasn't been prioritized to the top of Teradata's list so as to be included in V2R5.

The point...has anybody else experienced similiar problems with queries which fully quailify a table's PI; however, due to the existence of multiple in-lists, the Optimizer opts for a full-table scan instead of a PI retrieveal?

Thanks,

Claybourne Barrineau

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