Re: O9i: general index question

On 3 Apr., 15:14, Andreas Mosmann <mosm..._at_expires-30-04-2007.news- group.org> wrote:
> > If not absolutely necessary, using NULL instead
> > would of course solve all your problems, as you would have an index on
>
> I tried this but my problem became bigger because the opposite query
> sounded like
> CIDNEBENANLAGE is NULL and if I remeber correct that query was now exaxt
> as slow as the other one :(

No, you wouldn't have to compare CIDNEBENANLAGE with any value at all, because the join with TBNEBENANLAGE sorts out the NULL records. As all your WHERE criteria would refer to TBNEBENANLAGE, TBNEBENANLAGE would be the driving table, i.e. Oracle should first look for the appropriate records in TBNEBENANLAGE and then join with TBBAEUME using the index.

> > If you must stay with '0000000000' logic, then an index based on
> > CIDNEBENANLAGE being '0000000000' or not can help:
>
> I want to stay with '0000000000' because Oracle does not store NULL into
> a column and so I had bad results using ... IS NULL

That sounds strange. What do you mean by Oracle doesn't store NULL into a column? I don't know of any issues with NULL (except of course that Oracle stores NULL instead of '' for empty strings thus making usage of Length() etc. rather inconvinient).

> > SELECT ... WHERE decode(CIDNEBENANLAGE,'0000000000',0,1) = 1
> > That said, this is just the second best solution. The best solution is
> > still to use NULL instead of '0000000000'.
>
> I don't agree, sorry. It doesn't seem to be the best solution if you use Oracle.

We have been using Oracle for many years and never experienced any problems with NULL, so I wonder what your particular problem with NULLs is.

> > PS: Stay with your join syntax. We _are_ experienced Oracle developers
> > and have all changed to new syntax for readability.
>
> Do you talk about
> FROM T0
> JOIN T1 ON T1.X0=T0.X1
> instead of
> FROM T0,T1 WHERE T1.X0=T0.X1
> ?
>
> I will do because I like it if the WHERE- clause is only filled by the
> where clause. So it is easier for me not to forget a join- condition.
>

Yes. That's exactly what I mean. Received on Tue Apr 03 2007 - 08:46:32 CDT