Re: Counting number of rows, sequences with no sequences
- From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 18 Feb 2004 09:30:03 -0400
I don't think so, if you put in a insert ( +1 ) and in an update trigger
( -1) per record,
this will be automatic,
Or if you do mass updates, for example every night, you update x records,
you can update the total count.
I agree absolutely with you, obviously, if this table is heavily updated,
this is not the idea. A performance test is needed.
This is only an idea, I think we are trying to get ideas, maybe another
interesting idea con come from here.
Personally I wouldn't do this technique to get counts.
Aclaration about a joke for newbies.
The example down here, someone posted, I suppose no body had runed it,
because it truncates the table, this is only a joke. it erases all record
and then returns 0
(a fixed number), this will always work beacuse afeter you erase all record
obviously you only have 0 records.
create or replace function count_rec(tbl in varchar2) return
> integer
> 2 as
> 3 PRAGMA AUTONOMOUS_TRANSACTION;
> 4 trunc varchar2(256):='truncate table '||tbl;
> 5 begin
> 6 execute immediate trunc;
> 7 return(0);
> 8 end;
> 9 /
>
now
----- Original Message -----
From: "Jan Pruner" <JPruner@xxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, February 17, 2004 7:07 PM
Subject: Re: Counting number of rows, sequences with no sequences
Wrong.
1. RTFM - Function cannot write to the database, if the function is
being called from a SELECT statement.
2. your script is good for single user environment, but in multiuser
environment (like Oracle) will produce a BIG problem!!!
JP
Juan Cachito Reyes Pacheco wrote:
> What you can do is to create a one record table, the if the transaction
> rollbacks it will rollback
>
> create table sequence( value number);
>
> function getnewsequence
> select value+1 into variable from sequence;
> update table sequence value=variable ;
> return variable ;
> end;
>
> ----- Original Message -----
> From: "Mladen Gogala" <mladen@xxxxxxxxxxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> Sent: Tuesday, February 17, 2004 6:13 PM
> Subject: Re: Counting number of rows, sequences
>
>
> What happens if the transaction is rolled back?
>
> On 02/17/2004 05:03:14 PM, Juan Cachito Reyes Pacheco wrote:
>
>>The other choice if you have to frecuently do counts to that table is
>>a
>>sequence
>>
>>You create an addiatoinal field with a sequence
>>
>>CREATE SEQUENCE, etc.
>>
>>row sequence
>>
>>1 1
>>2 2
>>....
>>1234 1234
>>
>>Then you select from the sequence the currval, and this is immediate.
>>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
