RE: how to write this sql?
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 20 Jul 2004 13:18:19 -0400
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@xxxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mark W. Farnham
Sent: Tuesday, July 20, 2004 11:59 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of shawn@xxxxxxxxxxxxxx
Sent: Tuesday, July 20, 2004 11:12 AM
To: oracle-l@xxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
