Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help with query...

Re: Help with query...

From: KevJohnP <nospam_at_nowhere.com>
Date: Mon, 03 May 2004 17:01:56 +1200
Message-ID: <Bmklc.1406$8J.51260@news.xtra.co.nz>


Andrew Metcalfe wrote:

> 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

Are the statistics up-to-date on all the tables and indexes concerned? (check last_analyzed column in user_tables and user_indexes) Received on Mon May 03 2004 - 00:01:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US