From oracle-l-bounce@freelists.org Tue Jul 20 12:16:23 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6KHFwf15275 for ; Tue, 20 Jul 2004 12:16:08 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i6KHFl615252 for ; Tue, 20 Jul 2004 12:15:57 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B724572C66D; Tue, 20 Jul 2004 11:54:12 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 27199-15; Tue, 20 Jul 2004 11:54:12 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D5FD172C67F; Tue, 20 Jul 2004 11:54:11 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 20 Jul 2004 11:52:29 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4117372D191 for ; Tue, 20 Jul 2004 11:52:28 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 24765-79 for ; Tue, 20 Jul 2004 11:52:27 -0500 (EST) Received: from ihub.perceptron.com (ihub.perceptron.com [68.22.13.201]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9E01872D1CC for ; Tue, 20 Jul 2004 11:52:27 -0500 (EST) Received: from [192.168.4.7] (ineyman@perceptron.com) by Office-Logic InterChange; Tue, 20 Jul 2004 13:16:15 -0400 From: "Igor Neyman" To: Subject: RE: how to write this sql? Date: Tue, 20 Jul 2004 13:18:19 -0400 Message-ID: <000a01c46e7d$8d5546c0$0704a8c0@development.perceptron.com> MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.3416 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 In-Reply-To: Importance: Normal X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 5519 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: ineyman@perceptron.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Well, it's not: SQL> set echo on SQL> set timing on SQL> set autotrace on statistics SQL> select least(count(*),1000) from dba_objects; LEAST(COUNT(*),1000) -------------------- 1000 Elapsed: 00:00:00.46 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7073 consistent gets 0 physical reads 0 redo size 405 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select max(rownum) from dba_objects where rownum <= 1000; MAX(ROWNUM) ----------- 1000 Elapsed: 00:00:00.04 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 776 consistent gets 0 physical reads 0 redo size 396 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> spool off Igor Neyman, OCP DBA ineyman@perceptron.com -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Mark W. Farnham Sent: Tuesday, July 20, 2004 11:59 AM To: oracle-l@freelists.org Subject: RE: how to write this sql? only twenty distinct values? If that column is indexed (so that distinct runs quickly), then my generate the union all with rownum joke solution might actually be pretty tough to beat in practice. As a general solution, of course, it has the flaws that parse time will increase as the number of distinct values increases and that eventually it would exceed the length that a given version or Oracle is willing to parse at all. I don't believe that Oracle does the semantic termination of giving up if a max value is reached on count(*) or rownum functions using something like least, but Oracle will definitely use the stopkey count to bail out on rownum for all versions after about 6.0.35 (when Gary Hallmark fixed it). Before that, it WAS pretty amusing to watch it churn away against all the remaining rows of a table until it finally gave you the limited result. Now it would be really cool if Oracle did semantic optimization, and if it does I'll certainly be surprised (aside from the partition exclusion stuff). If I wasn't so lazy I guess I could run a test. Let's see.... If select least(count(*),100) from abc; and select max(rownum) from abc where rownum <= 100; run in the same amount of fetches when there are more than 100 rows, then Oracle is smarter than I think it is. mwf -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of shawn@virtualsmf.net Sent: Tuesday, July 20, 2004 11:12 AM To: oracle-l@freelists.org Cc: oracle-l@freelists.org Subject: RE: how to write this sql? > Looping using pl/sql is too slow. The current sql query is relatively > fast and it usually returns 20 ~ 30 rows. I just thought there might be > a way to make it a bit faster by not counting the extra rows I > don't need. You could do this using a materialized view, I think. (Not an MV expert) EG: SQL> create table x ( c1 varchar2(2) ); SQL> create index i on x ( c1 ); SQL> create materialized view log on x 2 with sequence, rowid ( c1 ) including new values; SQL> create materialized view x_mv 2 refresh fast on commit 3 as select c1, count(*) from x group by c1; SQL> insert into x values ('a'); SQL> insert into x values ('a'); SQL> insert into x values ('a'); SQL> insert into x values ('a'); SQL> insert into x values ('a'); SQL> insert into x values ('b'); SQL> insert into x values ('b'); SQL> insert into x values ('b'); SQL> insert into x values ('b'); SQL> insert into x values ('b'); SQL> insert into x values ('b'); SQL> insert into x values ('b'); SQL> commit; SQL> select * from x_mv; C1 COUNT(*) -- ---------- a 5 b 7 SQL> delete from x where c1='a' and rownum < 3; 2 rows deleted. SQL> commit; SQL> select * from x_mv; C1 COUNT(*) -- ---------- a 3 b 7 Anyone have comments? It works in this test, but are there drawbacks or it's not as ellegant as it looks? (Truncate would invalidate it, other than that it seems to be ok) Shawn ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 -----------------------------------------------------------------