Re: URGENT! How many rows will be returned?

Sue Han wrote:
>
> "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.

Should'nt make any difference is you are using the COST optimiser - this is why its there - to let you write your sql in "any" fashion and still get a reasonable result.

Check out EXPLAIN PLAN in the Tuning manual - you can use some of the output from this to "guestimate" how many rows will be need to be processed by your query to produce a result.

HTH

-- 
===========================================
Connor McDonald
"These views mine, no-one elses etc etc"
connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue."