Re: O9i: general index question

On Mar 28, 1:05 pm, sybra...@xxxxxxxxx wrote:
On Wed, 28 Mar 2007 17:57:21 +0200, Andreas Mosmann





<mosm...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Mark D Powell schrieb am 28.03.2007 in
<1175093787.522082.132...@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 --

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

.

Relevant Pages

  • 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: 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 responsible offices. ... 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: THE NATION WILL HUNT YOU DOWN LIKE BIN LADEN DID !!
    ... Yea, um you know, the FBI is hunting Barry Bonds and would have him up ... a tree or swinging from a tree by this time, ... In other words, go f--- yourself, because the real law and justice as ...
    (alt.sports.baseball.ny-yankees)