Archives of the TeradataForum

Message Posted: Thu, 18 Jan 2001 @ 18:51:27 GMT

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

Hi Rudel,

Oh how I hate reading generated SQL! Well, let's see. The first thing I notice is that the size of both spools is estimated with low confidence, which implies that statistics haven't been collected on the fields in the NUSI's and NUPI's. I also notice that in the first SQL, the second condition on the where clause starts with "1 = 2", which will never be true. This second observation is irrelevant, but just shows to go you how messed up generated SQL can be. Oh well.

Now on to the main point of the matter. With an or condition and the optimizer having to pick between using two NUSI's, I'm pretty sure that's it's just going to give up and do a full table scan. I also notice that the Between condition is generating a AND statement that won't use the NUSI on the date. The way around that one is to use a small table of dates and join to that.

I think there was a discussion on the list on that just a little while ago. As I understand it, the System Calendar is an in memory resident psuedo-table that will contain the set of dates that you need for an operation, such as a BETWEEN. I've used a hard-coded date table, and I've written code using the System Calendar, which seemed to work well, but I've never done a comparison of the two techniques. Somebody want to comment?

OK, I think I just thoroughly confused things.

iv

Frank C. Martinez IV

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