Note in-lin
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
June 2004 UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar
"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message
news:e9clc.11887$IG1.394261_at_attbi_s04...
>
> "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'
> >
> >
> > node_tree.tree_id is fully indexed, but the optimizer is ignoring
> > it....?
> >
Not only (as Jim points out) do you have an alias in the
WHERE clause that does not reference any of the aliases
given in the table, the column that you describe as fully
indexed doesn't appear in the where clause either.
> > Why would the optimiser skip a perfectly good index, and do a full
> > tablescan against such a huge table?
> >
The fact that a column is indexed doesn't make it a perfectly
good index. How many different values are there, what
does the clustering factor look like, is it unique or non-unique,
what are the recorded low and high values.
see
http://www.dbazine.com/jlewis12.shtml
for further ideas.
> > _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 Mon May 03 2004 - 02:37:19 CDT
