Re: artificial values vs null

• Original Message ----- From: "Niall Litchfield" <niall.litchfield_at_gmail.com>

> and use artificial values instead. This wasn't the focus of the
> original discussion, which is why I've renamed the subject line, but
> is an area where not using NULL when one should can seriously mess
> with the optimizer.

This is where I don't get it. I'm trying to understand where using a specific value in a column that is indexed can be worse than using a NULL (thereby for sure causing FTS).

> Not at all - there shouldn't be any *mismatched* datatypes in my
> queries (if there are I'll amend the script)

I tried to run it in wintel and it carked with missing "show_plan_9i". Not a problem here, but you might want to add that script somewhere?

> - it was designed to deal
> with those apps/design decisions that use character fields or numerics
> for dates. Here the problem can get even worse since the range of
> valid values for the field in business terms (i.e of the form
> YYYYMMDD) is so much less than the range of allowable values for the
> field.

Yeah, but you see: I'm trying to understand what the problem is that can get even worse. From running your script in my 9ir2, I got an index use where I expected it and a FTS where I expected it, and correct number of rows returned (with the date type columns). The others I understand (I think) the issue. But where I have a problem is with the date type queries: they are behaving exactly as I would expect and returning the right results? What did I miss?

>
> Not sure if you are reacting personally, or to the idea of using wrong
> datatypes as a bad idea. I didn't intend to single any individual out
> with the post, and I apologize if it reads like I thought you
> personally did bad designs.

Bugger moah, not at all! I'm sorry if it sounded that way. The point I was trying to (poorly) make is that there is a (I hope) safe way of implementing even a non-optimal solution. The artificial high date is not a perfect solution. But where it can be used, the correct way to do so is to make use of a function. To ensure that:
1- the value that replaces the NULL is always the correct one. 2- The value returned for comparison against a column is of the correct type. To avoid type mismatch and all its "nasties". 3- To ensure a consistent and unique way of replacing the NULL for that column.

At least with *this* app designer! :)

> query you are about to execute. I.E. I *think* that it gets stats
> based on your query rather than just randomly sampling tables in the
> query with no stats on them [1]. Thus it may be that having no stats
> is better than having insufficient stats.

Interesting. OT to this, but there was a recent thread in c.d.o.s. dealing with what happens with Global Temp Tables when joining them to others. To do with dynamic sampling of the GTT: does it happen and when? I wonder if 10g is radically different here? 9i appears to do a bad job of these.

Cheers
Nuno Souto
nsouto_at_bizmail.com.au

--
http://www.freelists.org/webpage/oracle-l