Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Counting number of rows

Re: Counting number of rows

From: Jan Pruner <JPruner_at_email.cz>
Date: Tue, 17 Feb 2004 23:38:34 +0100
Message-ID: <403297EA.4070008@email.cz>


Looks like you posted a too much sophisticated joke, Mladen.

:-)

Mladen Gogala wrote:

> Oh boy! And I was considering putting PRAGMA AUTONOMOUS_TRANSACTION;
> into the code.  DON'T RUN IT!
> 
> On 02/17/2004 04:16:37 PM, TNguyen wrote:
> 

>>the function created, but failed to run. What is wrong?
>>
>>-----------------------------
>>SQL> connect scott/tiger
>>SQL> select count(*) from emp;
>>
>> COUNT(*)
>>----------
>> 14
>>SQL>
>>SQL> create or replace
>> 2 function count_rows(tbl in varchar2) return number deterministic
>> 3 as
>> 4 TRUNC VARCHAR2(128):='truncate table '||tbl;
>> 5 begin
>> 6 execute immediate trunc;
>> 7 return(0);
>> 8 end;
>> 9 /
>>
>>Function created.
>>
>>SQL>
>>SQL> select count_rows('emp') from dual;
>>select count_rows('emp') from dual
>> *
>>ERROR at line 1:
>>ORA-14552: cannot perform a DDL, commit or rollback inside a query or
>>DML
>>ORA-06512: at "SCOTT.COUNT_ROWS", line 5
>>
>>
>>
>>-----Original Message-----
>>From: oracle-l-bounce_at_freelists.org
>>[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mladen Gogala
>>Sent: Tuesday, February 17, 2004 4:15 PM
>>To: oracle-l_at_freelists.org
>>Subject: Re: Counting number of rows
>>
>>
>>The function below would count rows extremely quickly, and is
>>independent of the table size. Even better, it is completely accurate
>>and doesn't rely on the underlying statistics. The table
>>in question may not even be analyzed. It does have one minor
>>shortcoming, however.
>>
>><PRANK>
>>create or replace
>>function count_rows(tbl in varchar2) return number deterministic
>>as
>>TRUNC VARCHAR2(128):='truncate table '||tbl;
>>begin
>>execute immediate trunc;
>>return(0);
>>end;
>>/
>></PRANK>
>>On 02/17/2004 03:27:31 PM, "Potluri, Venu (CT Appl Suppt)" wrote:
>>
>>>Is there a quick way to count number of rows in a table? Don't want
>>>to
>>>do select count(*) from..... on a table (such as GL_BALANCES) with
>>>more than 250 million rows.


Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Tue Feb 17 2004 - 16:38:34 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US