Re: artificial values vs null
- From: "Nuno Souto" <nsouto@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 24 Sep 2004 23:38:44 +1000
----- Original Message -----
From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
> 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@xxxxxxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
