Re: O9i: general index question

From: Andreas Mosmann <mosmann_at_expires-31-03-2007.news-group.org>
Date: Wed, 28 Mar 2007 17:57:21 +0200
Message-ID: <1175097441.35@user.newsoffice.de>

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

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

Received on Wed Mar 28 2007 - 10:57:21 CDT