Archives of the TeradataForum
Message Posted: Thu, 18 Jul 2002 @ 11:58:18 GMT
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.
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?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|