O9i: general index question

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
.

Relevant Pages

  • Re: Views - performance
    ... when u use stored object views oracle has to read the data dictionary ... this includes lots of steps for execution ... semantics of the query and fires it ... you'll possibly have any idea is to use explain plan to provide a base ...
    (comp.databases.oracle.server)
  • RE: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
    ... can pass strings as varchar's and have them inserted ... In theory I could write something to query the ... CLOB w/in Oracle is probably what is killing me... ...
    (perl.dbi.users)
  • Re: Equilevant of Oracle for optional records
    ... and a normalized t2 could be more useful, to build the query, and once this ... the Iqaluit Trauma: ... Oracle handles this data structure perfectly but I suspect it might be ...
    (microsoft.public.access.queries)
  • Re: Equilevant of Oracle for optional records
    ... this query is done, then flatten the result? ... example, the Iqaluit Trauma: ... Oracle handles this data structure perfectly but I suspect it might be ...
    (microsoft.public.access.queries)
  • DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
    ... can pass strings as varchar's and have them inserted ... In theory I could write something to query the ... CLOB w/in Oracle is probably what is killing me... ...
    (perl.dbi.users)