"Andrew Metcalfe" <chicagoandy_at_hotmail.com> wrote in message
news:a2588ec3.0405021120.7a9dfbff_at_posting.google.com...
> The following query is taking 10 minutes...
>
>
> SELECT a.*, b.id, t.tree_id
> FROM tbla a, tblb b, node_tree t
> where a.B_ID=b.id
> and t.node_id = b.node_id
> and h.fk1_id = '1030'
>
>
> An explain path shows:
>
> SELECT STATEMENT Hint=CHOOSE
> NESTED LOOPS
> NESTED LOOPS
> TABLE ACCESS FULL NODE_TREE
> TABLE ACCESS BY INDEX ROWID TblB
> INDEX UNIQUE SCAN tblB_pk
> TABLE ACCESS BY INDEX ROWID TblA
> INDEX RANGE SCAN TblA_ID_IDX
>
> The full table scan against NODE_TREE is a bad thing, this table has 4
> million records.
>
> node_tree.tree_id is fully indexed, but the optimizer is ignoring
> it....?
>
> Why would the optimiser skip a perfectly good index, and do a full
> tablescan against such a huge table?
>
> _Am
What is h.fk1_id = '1030' I don't see any table aliased with an h? Do you
mean t instead of h? If so is there an index on t.fk1_id and what exactly
is the index on it? (type and other columns in it. What version of Oracle
and have you analyzed the the tables and indexes?
Jim
Received on Sun May 02 2004 - 14:40:58 CDT
