Re: URGENT! How many rows will be returned?

  "Kevin P. Fleming" <kfleming_at_access-NOSPAM-laserpress.com> wrote:
> The optimizer doesn't "work it out", it makes educated guesses.

Without working it out, what join order will be used to improve the performance for the query

SELECT *
FROM table1 t1, table2 t2, table3 t3
WHERE t1.column1 = t2.column2
AND t2.column2 = t3.column3;

I mean t1.column1 = t2.column2 first, or t2.column2 = t3.column3 first.

> And it can
> only do this when there are histograms available for all the columns
used in
> the WHERE clause, and those histograms were created with all the right
> parameters, etc.

What system table(s) store the infomation of histogram? If all histograms are available, how to get the number of rows from there?

If no histograms available, how DB optimize the query to get the best perfomance?

>
> In other words, you can't "know" how many rows a query will return
without
> looking at the actual data involved, unless you have some other
table(s)
> somewhere with counts of every relevant value in t1.column1 and
t2.column2.
> This is what histograms are for, but they will only provide an
> approximation.

An approximation is exactly what I want. I will get statistics regularly. I really want to know how DB itself uses histogram. This is my research topic.

Sue

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 22 1999 - 02:27:57 CDT