Re: O9i: general index question
- From: "Mark D Powell" <Mark.Powell@xxxxxxx>
- Date: 28 Mar 2007 07:56:27 -0700
On Mar 28, 3:05 am, Andreas Mosmann <keineema...@xxxxxx> wrote:
hi ng,
I do not understand how indexes and SQL work together ...
minimal example, i hope it is to be understood
TABLE_DETAIL: 500 000 rows,
99.99% have ID_MASTER = 'R0', only a few have other, no NULL- values
TABLE_MASTER: 3 rows (IDs 'R0','R1','R2')
SELECT
TABLE_MASTER.CID,
TABLE_DETAIL.CID
FROM
TABLE_MASTER M
JOIN
TABLE_DETAIL D
ON
M.ID = D.ID_MASTER
WHERE
M.ID <> 'R0'
Whatever I do the query takes more than a minute to be answered.
All the times a FULL TABLE SCAN of TABLE_DETAIL is processed. Sometimes
if I had created an Index but not analyzed a INDEX RANGE SCAN was
processed ...
I tried index, bitmap index, changed M and D ...
the complete problem is more difficult as my question is:
if I have a query like
SELECT
#a fieldlist#
FROM
T1
JOIN
T2
ON
T1.F2=T2.FX
JOIN
T3
ON
T1.F3=T3.FX
WHERE
T1.F4='anything'
GROUP BY
T1.F5
ORDER BY
T1.F6
What an index I need? As far as I know order by is not to make faster by
index, so I need an Index like
CREATE (BITMAP?) INDEX XY ON T1 (F2, F3, F4, F5)
or
CREATE (BITMAP?) INDEX XY ON T1 (F5, F4, F2, F3)
or what else?
This query is generated by an application so that the where- clause
could be like
WHERE
(T1.F5='A' OR T1.F5='B') AND (T1.F6='C') ...
Is it enough to create an index over all the columns or do I need an
index for each situation? (Oracle 9.2.0.7) or is ist possible to create
indexes that are combined by Oracle like
CREATE BITMAP INDEX IDX_A ON T1 (F1,F2,F3);
CREATE INDEX IDX_B ON T1 (F4,F5,F6);
Most of the joined tables have only few rows (5 to 50), I tried BITMAP-
indexes for this, sometimes it worked, sometimes it did not. Sometimes
it skipped indexes and sometimes it prefered NON-UNIQUE- indexes.
If anyone can explain how to determine the matching index to a query (or
can tell me where I can read this - the Oracle- Documentation is nice
but I did not find my information in the lot of books, maybe you can
tell me a book/chapter) I would be very glad about it.
Many thanks
Andreas Mosmann
T1.F1=T2.F2
--
wenn email, dann AndreasMosmann <bei> web <punkt> de
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
the best option.
Each bitmap index entry cover potentially thousands of rows so their
use is unsuitable where concurrent DML operations exist on the table.
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.
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.
HTH -- Mark D Powell --
.
- Follow-Ups:
- Re: O9i: general index question
- From: Andreas Mosmann
- Re: O9i: general index question
- References:
- O9i: general index question
- From: Andreas Mosmann
- O9i: general index question
- Prev by Date: O9i: general index question
- Next by Date: Re: Best Oracle front end / Query tool for power users
- Previous by thread: O9i: general index question
- Next by thread: Re: O9i: general index question
- Index(es):
Relevant Pages
|
