Re: O9i: general index question

Mark D Powell schrieb am 28.03.2007 in <1175093787.522082.132610@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>:

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
.

Relevant Pages

  • Re: O9i: general index question
    ... All of them are situated near a road and for each tree there are 2 ... A tree can sometimes stand near a parking. ... IDOFFICE1 2% each office ... Obviously you where clause would need the above predicate for the FBI ...
    (comp.databases.oracle.misc)
  • Re: O9i: general index question
    ... In some cases there is no selective filter and few if any rows are ... All of them are situated near a road and for each tree there are 2 ... A tree can sometimes stand near a parking. ... IDOFFICE1 2% each office ...
    (comp.databases.oracle.misc)
  • Re: Bastard Powerbook From Hell
    ... My father made me learn in a 1964 chevy pickup, 3 on the tree. ... We practiced hills, parking lots, curvy roads. ...
    (alt.sysadmin.recovery)
  • Re: [PATCH] Doc/stable rules: add new cherry-pick logic
    ... available in the sign-off area then hpa's script will filter them into ... Procedure for submitting patches to the -stable tree: ... Send the patch, after verifying that it follows the above rules, to ...
    (Linux-Kernel)
  • Re: Linkwitz-Riley response not perfectly flat
    ... substitution of the lpH filter for the apH filter in the Lo ... Starting with the 2-way crossover network ... appropriate allpass in the unsplit branch. ... allpasses as far as possible up the tree to minimize the number of ...
    (comp.dsp)