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).