Re: Help with query...

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

Does your Oracle installation have a version number? Are statistics current?
Were they created with DBMS_STATS?
On tables only or on tables and indexes? What is the setting for optimizer_index_cost_adj? What is the stting for optimizer_index_caching? Do you have your data types correct?
What percentage of the rows in the table will this retrieve?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)