Re: O9i: general index question

From: Andreas Mosmann <mosmann_at_expires-30-04-2007.news-group.org>
Date: Tue, 03 Apr 2007 15:14:32 +0200
Message-ID: <1175606072.87@user.newsoffice.de>

Thorsten Kettner schrieb am 03.04.2007 in <1175604630.405212.254610_at_n59g2000hsh.googlegroups.com>:

>> FROM
>> -- 500 000 rows, Main Table
>> BAUMD.TBBAEUME BAUM
>> -- 50 rows, most keys about 2%, one about 50% of BAUM
>> JOIN BAUMD.TSINSTITUTIONEN IU ON IU.CID = BAUM.CIDSMUNTERHALT
>> -- 50 rows, most keys about 2%, one about 50% of BAUM
>> JOIN BAUMD.TSINSTITUTIONEN IO ON IO.CID = BAUM.CIDSMORT
>> -- 500 rows, each key at 0 to 10% of BAUM
>> JOIN BAUMD.TZCODES CODE ON CODE.CID = BAUM.CIDCODE
>> -- 100 rows, most keys <2%, 2 key about 40% of BAUM
>> JOIN BAUMD.TSNUTZER NUTZER ON NUTZER.CID = BAUM.CIDNUTZER
>> -- actually about 5 rows, later about 20 rows
>> -- 1 key > 99%, each other 0.02% (now and later) of BAUM
>> JOIN BAUMD.TBNEBENANLAGEN NEBENANLAGE
>> ON NEBENANLAGE.CID = BAUM.CIDNEBENANLAGE
>> WHERE
>> -- '0000000000' is the 99% value
>> (CIDNEBENANLAGE <> '0000000000')
>> -- about 2% of BAUM
>> AND (
>> (NEBENANLAGE.cidsmort = 142108) OR
>> (NEBENANLAGE.cidsmunterhalt = 142108)
>> )

> As I read it, you need the value '0000000000' for a default record in
> NEBENANLAGE, yes? 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 :(

> CIDNEBENANLAGE containing only few records (because NULL is not in
> stored in an index) and the join would be extremely quick. As to now
> you use records with '0000000000', i.e. your index contains 500.000
> records and then you don't even specify what particular records you
> are looking for, but what you are not looking for (CIDNEBENANLAGE <>

If I specify an ID the result is the same, a full table scan of TBBAEUME

> '0000000000'). This is about the worst case. Using ANALYZE TABLE > tbBaeume COMPUTE STATISTICS may help, but I am not sure as in all I do that about twice an hour :(

> these years of Oracle experience I've never run into such problem as > yours.
Nice for you. Maybe I need some years ...

> 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

> CREATE INDEX idxBaeumeNebenanl ON tbBaeume > (decode(CIDNEBENANLAGE,'0000000000',0,1)); I will try this, thank you.

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

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

Thank you
Andreas Mosmann

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

Received on Tue Apr 03 2007 - 08:14:32 CDT