| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Counting number of rows
the function created, but failed to run. What is wrong?
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.
> --------------------------------------------------------
>
> If you are not an intended recipient of this e-mail, please notify
> the
> sender, delete it and do not read, act upon, print, disclose, copy,
> retain or redistribute it. Click here for important additional terms
> relating to this e-mail. http://www.ml.com/email_terms/
> --------------------------------------------------------
>
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_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 - 15:16:37 CST
 |
 |