Re: Why this query is sooo slow?!

Table stats seem current. This is probably a case of data skew which is causing the optimizer to estimate wrong selectivity. It's only expecting 184 rows from HIGH whereas there should be around 10000 according to you.

Do consider creating a histogram on the relevant columns. You'll need to decide if the possibly better query plan as a result of this is worth the required overhead.

Also, in your follow-up regarding my suggestion to hint a merge join, you said it's still the same. What's still the same? Do you mean that the query plan did not change (still NL)? Or it changed to merge join but no performance improvement? I'm thinking that given your particular query, the merge join might be your best bet if we could make it work. Let the optimizer with histograms take a shot at it.

Cheers,

Romeo

ibm_97_at_yahoo.com (JZ) wrote in message news:<10bc841c.0405250648.2ae68572_at_posting.google.com>...
> >what does
> >select num_rows from user_tables where table_name='HIGH'
> >look like compared with
> >select count(*) from HIGH;
>
> THey're the same: 3797502
>
> Thanks!
Received on Tue May 25 2004 - 14:57:51 CDT