QUESTION POSED ON: 10 February 2003
I'm on Oracle 9i Release 2 on XP.
I am having problems understanding exactly what the optimizer is trying to do.
I have a small query joining three tables.
Indexes were created for the primary keys.
They look something like:
table a (aid number, other data columns);
table b (aid number, version number, other data columns)
table c (aid number, version)
In reality, table c is a temp table, but the problem persists even if I create it as a 'real table' -- there is no index on the C table as temp, and it only has about 12 rows, and we use it to drive the query.
Table a has 25,000 rows.
Table B has in excess of 25,000 rows.
Table c has about 12 rows.
The query is:
SELECT A.aid, A.otherdatacolumns
FROM A, B, C
WHERE A.AId = C.AId
AND B.AId = C.AId
AND B.Version = C.Version
We run this with autotrace on, and we get a good query.
We
gather statistics for tables and run the query again and get full table scans appearing for either one or two of the tables A and B.
We put a rule hint in the select statement, and we get a good query again.
This query is part of a larger group of queries that I am trying to port from Sybase to Oracle - with left outer joins, etc. So any help you could give here would be appreciated. I also suspect from other work I have been doing that the optimzer can't cope with LEFT OUTER JOIN syntax and much prefers the Oracle (+) notation. I have a query where if I use the left outer join, I get a bad result from the optimizer, but if I use (+) syntax, I get a good plan from the optimizer. Any ideas?
Am I missing something? Is there something disabled that I should enable?
|
