Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Making A "Hash" of it. Why are indexes not used?
applicationz_at_paradise.net.nz (Stuart MacKinnon) wrote in message news:<56db38fe.0402251110.5f27cf4e_at_posting.google.com>...
> Using 8i, I have a complex query involving 2 views (one nested inside
> the other).
Hmmmmm, 8i but WHICH release and what patch level? Nothing before 8.1.7.3 will have a reliable CBO, IME.
Also:
2 *views*? What are they and what are they doing? I've got a funny bone telling me your SXV_LP_TRANSACTIONS_TOTALS view is doing aggregations with GROUP BY?
If so, there lies your problem: it will work fine with the first SQL as the WHERE predicate will be merged with the view BEFORE the aggregation takes place and will therefore filter the number of rows to aggregate.
But it will cause problems with the second SQL as the join takes place AFTER *all* aggregations are done, no filtering.
HTH
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam
Received on Thu Feb 26 2004 - 00:56:01 CST