Re: O9i: general index question

On Mar 28, 3:05 am, Andreas Mosmann <keineema..._at_gmx.de> 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 -- Received on Wed Mar 28 2007 - 09:56:27 CDT