Re: O9i: general index question

On Mar 28, 1:05 pm, sybra..._at_hccnet.nl wrote:
> On Wed, 28 Mar 2007 17:57:21 +0200, Andreas Mosmann
>
>
>
>
>
> <mosm..._at_expires-31-03-2007.news-group.org> wrote:
> >Mark D Powell schrieb am 28.03.2007 in
> ><1175093787.522082.132..._at_d57g2000hsg.googlegroups.com>:
>
> >> If 99% of the data has one value then a normal index on this column is
> >> generally useless unless you want the 1% value. A full table scan is
> >Thats the thing I want. The 1%
>
> >> Each bitmap index entry cover potentially thousands of rows so their
> >> use is unsuitable where concurrent DML operations exist on the table.
> >But less than 1% of the rows change a day. And all the data are changed
> >row by row
>
> >> Generally speaking to get useful query performance help you need to
> >> identify the version of Oracle, the query, information about available
> >> indexes, and filter conditions, plus post the existing explain plan
> >> for the query.
> >I work with Oracle 9.2.0.7 and my question is the opposite direction. I
> >want to create matching indexes. Sorry, if I did not explain that
> >correct. What way I have to go to find out what indexes match to a
> >query?
>
> >> In general you want to enter the table chain with a filter condition
> >> on a selective column via an index access and drive from this table
> >> via the next table that filters out rows through the rest of the set
> >> of tables.
> >> In some cases there is no selective filter and few if any rows are
> >> eliminated by the join so run time is pretty a result of just the
> >> quantity of the data and the capability of the hardware.
> >I try to explain the situation (shortcut):
> >There is a table that contains trees (the real ones).
> >All of them are situated near a road and for each tree there are 2
> >responsible offices. A tree can be cut and a row has a person that
> >worked on it last time. A tree can sometimes stand near a parking. A
> >tree has a class name
>
> >NAME SELECTIVITY
> >IDOFFICE1 2% each office
> >IDOFFICE1 2% each office
> >IDUSER 2% each office
> >IDPARKING 0.01% on any Parking, 99.99% no parking
> >IDTREECLASS 0 to 10%
> >ISCUT 10% cut, 90% uncut
>
> >and the queries all the time is a combination of these rows, typically
> >IDOFFICE* is asked all the way.
> >if I ask for the 0.01% on parking places I get a very poor performance
> >that I have to change.
>
> >what indexes should I create, if f.e. I ask for
>
> >select OFFICENAME1,OFFICENAME2,PARKINGNAME,TREENAME
> >from TREES T
> >join OFFICES O1 on T.IDOFFICE1 = O1.ID
> >join OFFICES O2 on T.IDOFFICE1 = O1.ID
> >join PARKINGS P on T.IDPARKING = P.ID
> >join TREECLASSES C on T.IDTREECLASS = C.ID
> >where (T.ISCUT = 0) and (T.IDOFFICE in (1,2)) AND (T.IDPARKING <>0)
>
> >?
> >Do I have to change the direction of join- clauses?
>
> >> HTH -- Mark D Powell --
>
> function based index on id_master
> decode(id_master,'0','0',null)
>
> NULL values are not indexed.
> Obviously you where clause would need the above predicate for the FBI
> to be used (and query_rewrite should be set to true)
>
> --
>
> Sybrand Bakker
> Senior Oracle DBA- Hide quoted text -
>
> - Show quoted text -

Yes you want to create a sparse index. Using a FBI is one way. The other way that we used before FBI became available is not take the one value that is 90% (no parking) and represent it by the absence of a value, that is, column value is NULL. Only insert a value into the column for the other, very small percentage, condition(s). Both methods require some code changes.

HTH -- Mark D Powell -- Received on Wed Mar 28 2007 - 15:20:03 CDT