SQL & PL/SQL

Hi,

We have a large Oracle database, v 10g. Two of the tables in the database have over one million rows.
We have a few queries which take a lot of time to execute. Not always though, it that seems when load is high the queries tend
to take much longer. Average time may be 1 or 2 seconds, but maxtime can be up to 2 minutes.
We have now used Oracle Grid to help us examine the queries. We have found that some of the queries require two or three full table scans.
Two of the full table scans are of the two large tables mentioned above.
This is an example query:


SELECT table1.column, table2.column, table3.column
FROM table1
JOIN table2 on table1.table2Id = table2.id
LEFT JOIN table3 on table2.table3id = table3.id
WHERE table1.id IN(
SELECT id
FROM (
(SELECT a.*, rownum rnum FROM(
SELECT table1.id
FROM table1,
table2,
table3
WHERE
table1.table2id = table2.id
AND
table2.table3id IS NULL OR table2.table3id = :table3IdParameter
) a
WHERE rownum <= :end))
WHERE rnum >= :start
)


Table1 and table2 are the large tables in this example. This query starts two full table scans on those tables.
Can we avoid this? We have, what we think are, the correct indexes.

/best regards, Håkan