Vladimir,
Thanks I hadn't considered || as a function, though it is.
At first, I was going to take your word for it, but then decided
this would be an interesting test. :)
But first, I agree, you must know what you're looking for, neither
of these would work in all situations.
First, I built some test data:
--------------------------------------
drop table emp;
create table emp( ename, job )
as
select table_name, column_name
from dba_tab_columns
where rownum < 1001
/
alter table emp add ( mydate date );
update emp set mydate = sysdate;
commit;
declare
v_date date;
begin
for f in 1 .. 5
loop
insert into emp
select ename, job, sysdate
from emp;
dbms_lock.sleep(1);
end loop;
insert into emp
select ename, job, null
from emp;
end;
/
create index emp_idx on emp(ename, job, mydate);
-----------------------------
This creates 64000 rows in emp.
For testing, I'm using Tom Kytes run_stats.sql and test_harness.sql.
The URL is something like govt.oracle.com/~tkyte/run_stats.html
Not sure, because my internet connection is down as I write this.
Below is the test harness code I used:
-----------------------------------
-- test_harness.sql
-- from Tom Kyte - asktom.oracle.com/~tkyte/runstats.html
-- see ~/oracle/dba/run_stats for all files
declare
l_start number;
--add any other variables you need here for the test...
v_count integer;
begin
delete from run_stats;
commit;
-- start by getting a snapshot of the v$ tables
insert into run_stats select 'before', stats.* from stats;
-- and start timing...
l_start := dbms_utility.get_time;
-- for things that take a very small amount of time, I like to
-- loop over it time and time again, to measure something "big"
-- if what you are testing takes a long time, loop less or maybe
-- not at all
for i in 1 .. 10
loop
select count(distinct(ename||job||mydate)) into v_count
from emp;
end loop;
dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
-- get another snapshot and start timing again...
insert into run_stats select 'after 1', stats.* from stats;
l_start := dbms_utility.get_time;
for i in 1 .. 10
loop
SELECT COUNT(*) into v_count
FROM (
SELECT DISTINCT
ename, job, mydate
FROM emp
);
end loop;
dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
insert into run_stats select 'after 2', stats.* from stats;
end;
/
----------------------------------
Now the results. Run 1 uses CONCAT, Run 2 uses
an inline view with Group by.
22:13:02 sherlock - jkstill@ts01 SQL> @th
1691 hsecs
2032 hsecs
PL/SQL procedure successfully completed.
22:13:49 sherlock - jkstill@ts01 SQL> @run_stats
NAME RUN1 RUN2 DIFF
---------------------------------------- ---------- ---------- ----------
STAT...consistent gets 3378 3379 1
STAT...db block changes 17 16 -1
LATCH.undo global data 3 4 1
STAT...calls to get snapshot scn: kcmgss 23 22 -1
STAT...parse time elapsed 0 1 1
STAT...parse time cpu 0 1 1
STAT...deferred (CURRENT) block cleanout 3 2 -1
applications
LATCH.active checkpoint queue latch 5 7 2
LATCH.virtual circuit queues 2 0 -2
LATCH.redo allocation 13 18 5
LATCH.redo writing 22 27 5
LATCH.checkpoint queue latch 27 34 7
LATCH.messages 33 44 11
LATCH.session allocation 22 38 16
STAT...free buffer requested 779 761 -18
LATCH.session idle bit 11 31 20
LATCH.shared pool 3 27 24
LATCH.multiblock read objects 312 338 26
STAT...prefetched blocks 607 578 -29
STAT...redo size 20964 21008 44
STAT...enqueue requests 441 544 103
STAT...enqueue releases 440 544 104
LATCH.sort extent pool 495 599 104
LATCH.library cache 241 389 148
LATCH.enqueue hash chains 880 1096 216
STAT...recursive cpu usage 1592 1908 316
LATCH.enqueues 1771 2211 440
STAT...db block gets 954 1494 540
STAT...session logical reads 4332 4873 541
LATCH.cache buffers chains 12988 14905 1917
STAT...physical reads 5927 8310 2383
STAT...physical writes 5159 7560 2401
STAT...physical writes non checkpoint 5159 7560 2401
STAT...physical reads direct 5159 7560 2401
STAT...physical writes direct 5159 7560 2401
35 rows selected.
Though not a dramatic difference, the CONCAT was faster
and less resource intensive than the inline view with GROUP BY.
:)
Jared
On Wednesday 29 January 2003 16:08, Vladimir Begun wrote:
> [EMAIL PROTECTED] wrote:
> >>I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be
> >
> > elegant
> >
> >>way of doing it.
> >
> > elegant = simple, concise, easy to understand.
> >
> > Looks elegant to me.
>
> Jared, it just looks that that...
>
> CONCAT = || yet another function call, yet another piece of
> code, yet another byte of memory... If you have more than
> two columns? If some of those are numeric, date? If ename
> is Smith and job is Smith and both can be nullable? :)
> NVLs? NVL2s? I think this approach is only valid when one
> really understands what she/he is looking for. Could be
> good for FBI, CHECK constraints but it's very risky and
> resource consuming (depends, can be neglected) for
> queries.
>
> It's better to write something that just looks ugly but
> works faster and reliably. Simple, fast, and covers all
> 'strange' cases:
>
> SELECT COUNT(*)
> FROM (
> SELECT DISTINCT
> ename
> , job
> FROM emp
> )
> /
>
> Regards,
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
