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