[comp.databases] no. of rows

ericco@ssl.berkeley.edu (Eric C. Olson) (10/28/90)

I'm new to the SQL game, so bear with me.  Is there a way of
predicting the number of rows a query will return?  For example:

	select column1 from table1

Can I find out how many rows will be returned?  Do I have to make the
query twice?

Thanks in advance,
Eric

--
Eric
ericco@ssl.berkeley.edu

av@kielo.uta.fi (Arto V. Viitanen) (10/29/90)

>>>>> On 27 Oct 90 22:41:13 GMT, ericco@ssl.berkeley.edu (Eric C. Olson) said:

Eric> I'm new to the SQL game, so bear with me.  Is there a way of
Eric> predicting the number of rows a query will return?  For example:

Eric> 	select column1 from table1

Eric> Can I find out how many rows will be returned?  Do I have to make the
Eric> query twice?

I think that in every relational database is a thing called dictionary. It
holds (at least) name of all users, relations, attributes, views etc. They
can be used as any other relation, but only for querying, i.e. you can ask
how some view is formed. There can also be statistics kept by Database
Management System for query optimization, for example number of of rows in
relation. In your example, you could select statistics for table1 and receive
some prediction on number of rows.

If you have more complex query, you should make some assumptions about
distribution of values etc. i.e. the same job that query optimizer does.
Since you know your data, your guesses are better than query optimizers, but
still guesses.

--
Arto V. Viitanen				         email: av@kielo.uta.fi
University Of Tampere,				   	    av@ohdake.cs.uta.fi
Finland

tpg@n5pph007.UUCP (Peter Graham) (11/12/90)

In article <ERICCO.90Oct27154113@sdaf1.ssl.berkeley.edu> ericco@ssl.berkeley.edu (Eric C. Olson) writes:
>I'm new to the SQL game, so bear with me.  Is there a way of
>predicting the number of rows a query will return?  For example:
>
>	select column1 from table1
>
>Can I find out how many rows will be returned?  Do I have to make the
>query twice?
>
>Thanks in advance,
>Eric
>
>--
>Eric
>ericco@ssl.berkeley.edu


In Oracle, you can perform the following select:

     SELECT COUNT(*) FROM TABLE1;

This will return the number of rows in the table.  You can also supply an
expression to return the number of rows meeting a specified criterion.

Peter

-------------------------------------------------------------------------
reply to: ...!mcnc!rti!n5pph007!tpg
NTI may care what I say, but I'm not 
saying it for them...these opinions 
and statements are mine entirely