How do you create an index across different tables

Question

    Table A consists of (id, fieldA, fieldB, fieldC, fieldD)
    Table B consists of (id, tableA-id, fieldE, fieldF, fieldG)

My queries looks like this:-

1. select * from tableB b, tableA a where a.fieldA=? and a.fieldB=? and a.fieldC=? and a.fieldD=? and (b.fieldF >= '09/01/10' and b.fieldF <= '09/30/10');

2. select * from tableB b, tableA a where a.fieldA=? and a.fieldB=? and a.fieldC=? and a.fieldD=? and b.fieldE=? and (b.fieldF >= '09/01/10' and b.fieldF <= '09/30/10');

Note: fieldE is the extra parameter for [2]

How should I define my indexes to accommodate these queries

Answer 1

A good start would, i think, be:

create index huge_index on tableA (fieldA, fieldB, fieldC, fieldD)
create index modest_index on tableB (fieldF, fieldE);

Probably also B.tableA-id. A guru might be able to suggest if some kind of index on B including both tableA-id and other values might help.