Archives of the TeradataForum
Message Posted: Tue, 19 Sep 2006 @ 13:53:12 GMT
| Subj: | | Re: Partition Benchmarking Issues |
| |
| From: | | Geoffrey Rommel |
| | Issue 1 - Partition not considered in join | |
Your explains show "no confidence". Have you collected statistics? In particular, you need stats on the partitioning column.
Even with stats, however, the partitioning might not be used. The partitioning expression on ods1_shipment_status_test uses only 12 values, so
reading one partition would still involve 1/12 of the table -- probably no faster than a full-table scan.
| | When we hard code the date in the where clause then it's using partition and query response is fast. However, if we use 'CASE' expression
in where clause then it's not using partition and response is slower than the query without partition. | |
This is a common experience. If you code a literal, the optimizer can compare the timestamp with the statistics and come up with a good plan.
If you use a case expression, the optimizer doesn't know what dates are involved and has to play it safer.
Are all of the times in these timestamps '00:00:00'? If so, you could redefine them as dates.
The partitioning expression should really be based on a column, not a cast() of a column. Could event_dt be defined as a date, or could you
split it into a date and a time? This would make things easier for the optimizer.
| 
