Re: O9i: general index question

> 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 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 <> '0000000000'). This is about the worst case. Using ANALYZE TABLE tbBaeume COMPUTE STATISTICS may help, but I am not sure as in all these years of Oracle experience I've never run into such problem as yours.

If you must stay with '0000000000' logic, then an index based on CIDNEBENANLAGE being '0000000000' or not can help:

CREATE INDEX idxBaeumeNebenanl ON tbBaeume (decode(CIDNEBENANLAGE,'0000000000',0,1));

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

PS: Stay with your join syntax. We _are_ experienced Oracle developers and have all changed to new syntax for readability. Received on Tue Apr 03 2007 - 07:50:30 CDT