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: how to write this sql?

RE: how to write this sql?

From: <shawn_at_virtualsmf.net>
Date: Tue, 20 Jul 2004 09:12:09 -0600 (MDT)
Message-ID: <29907.168.215.22.23.1090336329.squirrel@168.215.22.23>


> 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_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 Jul 20 2004 - 10:32:14 CDT

Original text of this message

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