Re: O9i: general index question

On Wed, 28 Mar 2007 17:57:21 +0200, Andreas Mosmann <mosmann_at_expires-31-03-2007.news-group.org> wrote:

>Mark D Powell schrieb am 28.03.2007 in
><1175093787.522082.132610_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