Re: Row Count

[Quoted] Connor McDonald wrote in message <34D83630.6E7A_at_bhp.com.au>...
>> The project I am currently working on has a requirement to provide
>> feedback to the user about the query that (s)he has sent. This
>> feedback consists of the number of rows that satisfy the query and
>> the amount of time to satisfy the query.
<snipped>

Typical dumb-ass user requirement... :-)

>This kind of functionality is provided in Discoverer 3 - I duuno how
>they have done but I know the doco says that all tables must be
>analyzed (and of course running CBO) for it to work

You can run an explain plan on the SQL beforehand and use the cost and number of rows in the explain plan to determine some kind of response time, but I personally find that particular data (cost and # of rows) from the explain plan to be useless most of the time.

I would love to see the SQL statements that Discoverer 3 makes to get this to work. Also the amount of time to run a query is dependant on a lot of other things - simply using the number of rows to retrieve to calculate response times seems stupid. And the CBO behaves so erratic with the same query that we had to hard code hints in most of our production SQL statements to get Oracle to do what we want it to do. So, IMHO Discoverer is probably just doing a thumbsuck - that's to say if Oracle has some unpublished tricks they pull to get this type of data (Discoverer is an Oracle product isn't it?).

regards,
Billy Received on Thu Feb 05 1998 - 00:00:00 CET