Re: Any way to get RowCount by not using Select count(*) fromtable

Jeremiah:

Agree with all of your points, plus there are others. For example, some front-end products (take Visual Basic for example), essentially
*require* a primary key to permit updates to a table.

Further, Oracle has cured the woe that Dan writes about in O8 with the construct
of Index Organized Tables. IOT's allow a single database object containing both the index value and the related data value...so only a single physical read will retrieve the data.

Craig

>On Mon, 4 Jan 1999, Dan Morgan wrote:
>>
>> I know this is a segue but as a DBA I would really appreciate it if
developers
>> would STOP putting primary keys on EVERY table.
>>
>> For small static tables it takes twice as long for the SQL Engine to read
the
>> index and then read the table when a single read of the table would
suffice.
>> This same situation occurs in some cases with larger tables too. And this
does
>> not exactly enhance performance. If your concern is keeping the table in
>> memory make it a cache table.
>
>People need primary keys on tables to insure data integrity. It is true
>that in some cases the number of reads required to do an index full or
>range san may exceed the number of reads required to perform a full table
>scan. In these cases, a properly maintained database will produce the
>most efficient query plan based on the cost of the operations. The
>cost-based optimizer should automatically make the right decision in these
>cases. There is nothing wrong with primary keys.
>
>--
>Jeremiah Wilton http://www.wolfenet.com/~jeremiah
>
Received on Thu Jan 07 1999 - 19:55:06 CST