When the DBA sequels - simple (phfilosophical?) tuning question

Hello

I (the DBA) was asked by a colleague (developer) why this so simple query would run 29 sec when all involved columns are indexed and only one row is returned (V. 9.2.0.4EE/Solaris7):

SELECT * FROM quot a, quot_user au
WHERE a.quot_id = au.quot_id
AND flow_node_id = 711;

Both tables quot and quot_user are "large" (8 Mio. rows). The fields

quod_id used for the join of both tables are primary keys.
flow_node_id of quot is a foreign key to the primary key of the table
flow_node, which is small, but needs not to be referenced.

Taking a look at the execution plan, it turns out that the CBO decides to make a FTS on the table quot_user:

SELECT STATEMENT, GOAL = CHOOSE Cost=494 HASH JOIN Cost=494
  TABLE ACCESS BY INDEX ROWID quot Cost=188     INDEX RANGE SCAN fk_quot_flow_node Cost=30   TABLE ACCESS FULL quot_user Cost=294

When comparing the quot_id values to do the join, the CBO determines the quod_id on the left side right away, but then compares it with EVERY value of quot_user.quot_id (full scan) - bad. Don't understand why. If the value quot.quot_id is known (due to the condition flow_node_id=711), and since quot_user.quot_id is indexed (primary key), then it should be fetched directly.

So I thought that putting an appropriate hint would solve the problem. To my surprise, all hints I tried had no effect at all, something like: (and many other combinations of diff. hints) SELECT /*+ INDEX (quot_user pk_quot_user) */ *

Changing the session parameter OPTIMIZER_INDEX_COST_ADJ in a wide range had no effect either. I also tried the Outline Editor of the OEM, which does not seem to let you edit anything. After thinking for a while, I came up with a solution based on rewriting the query so to
"force" the CBO to do an index access on quot_user:

SELECT * FROM quot a
WHERE a.quot_id =
( SELECT quot_id FROM quot_user au WHERE au.quot_id =

   ( SELECT quot_id from quot WHERE flow_node = 711    )
)
This query returns exact the one same record and runs 0.02 sec!

The execution plan looks like this:

SELECT STATEMENT, GOAL = CHOOSE Cost=2
  TABLE ACCESS BY INDEX ROWID quot Cost=2     INDEX UNIQUE SCAN pk_quot Cost=2

      INDEX UNIQUE SCAN pk_quot_user Cost=3
        TABLE ACCESS BY INDEX ROWID quot Cost=188
          INDEX RANGE SCAN fk_quot_flow_node Cost=30

Sorry if this is too trivial for you.
What is wrong in the "slow" version of the query? Why didn't hints affect the execution plan? Isn't SQL a declarative language? (I always thought that procedural incluences are only necessary in order to overcome implementation deficiencies - Well, Oracle seems to be very deficient if this is true).

My colleague, who is much more proficient writing SQL than me, but who has no clue at all about how Oracle works internally, did not like the
"fast" version of the query. He usually writes queries one page long
at least, this one was just a sample - fiddling with the code like this is not feasible for him, specially if he does not see the logic to head for (perhaps that is why he always complains about performance - eh ).

By the way, in this DB there are lots of pairs of tables holding the same field as primary key (1:1 relationship). Is there a way to create an object coupling both tables - something like an index spanning both tables? This way, Oracle would always find corresponding rows without FTS. Unfortunately, this is a canned application, schema changes are tabu. Changes need to behave neutral to the application logic.

Bye
Rick Denoire    Received on Sat Jun 19 2004 - 10:15:24 CDT