Help with query...

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 Received on Sun May 02 2004 - 14:20:36 CDT