Re: Counting number of rows

Jan, I am deeply shocked! I surrounded it with <PRANK> tags and I said that it has a "small shortcoming"! Yes, it destroys all the data in the table. That "0" that the function returns becomes the record count. I didn't think that somebody would actually try to destroy his own data! I'm not worried about Jonathan or Waleed, they're both extremely smart and started doing oracle approximately when the dinosaurs started evolving from lizards. But this guy is something else!

On 02/17/2004 05:38:34 PM, Jan Pruner wrote:
> 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
> -----------------------------------------------------------------
>

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
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
-----------------------------------------------------------------