Re: O9i: general index question

Thorsten Kettner schrieb am 03.04.2007 in <1175607992.450446.92530_at_n76g2000hsh.googlegroups.com>:

> 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.

Ok, I will try this again. Maybe it was too late that night and I did not build a useful query.

>>> 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).
Sorry: replace('column','index')

>>> 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.

I wonder too. It will take some time to update the table. I think I made some maybe little mistakes, but the result was heavy. The decode- solution will not be useful, because I create the queries dynamic and the user could have chosen a special CIDNEBENANLAGE. So it will only help if there is no one choosen. And I have one more case.

btw: Can you tell me how to specify a driving table? I know that it is better to use TBNEBENANLAGEN instead of TBBAEUME as it, and there are some indexes on different queries (in test case), but it
always uses FULL TABLE SCAN on both tables, no matter whether I write FROM TBBAEUME B
JOIN TBNEBENANLAGEN NA
ON NA.CID=B.CIDNEBENANLAGE
or change to
FROM TBNEBENANLAGEN NA
JOIN TBBAEUME B
ON NA.CID=B.CIDNEBENANLAGE
or whatever I tried.

Andreas Mosmann

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de