| 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
Mladen, to say I'm disappointed in you is an understatement, putting
out code that has bugs in it. do you secretly work for oracle corp?
:)
Joe
Mladen Gogala wrote:
>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
>>-----------------------------------------------------------------
>>
>>
>>
>
>
>
-- 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 Wed Feb 18 2004 - 07:12:47 CST
 |
 |