Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Help with query...
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...Received on Mon May 03 2004 - 02:37:19 CDT
>
> "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
>
>
![]() |
![]() |