Re: URGENT! How many rows will be returned?

Then you're trying to learn how the Oracle optimizer works, and I'd say good luck. It's probably the most complex, sophisticated and difficult to understand part of the Oracle DBMS product.

If this is a real application you're trying to develop that needs to "mimic" the behavior of the optimizer for some reason, I'd suggest that the application needs to be rethought. If you're just doing this as a research project, then you need to spend some time reading all the Oracle DBA documentation, where all the internal tables are described. This will at least get you started in the right direction.

Sue Han <sue_han_at_my-deja.com> wrote in message news:7up3lr$ebr$1_at_nnrp1.deja.com...
> "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 - 21:41:13 CEST