| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQL question
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:
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);
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:
declare
l_start number; --add any other variables you need here for the test... v_count integer; begin delete from run_stats; commit;end;
-- 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;
Now the results. Run 1 uses CONCAT, Run 2 uses an inline view with Group by.
22:13:02 sherlock - jkstill_at_ts01 SQL> @th
1691 hsecs
2032 hsecs
PL/SQL procedure successfully completed.
22:13:49 sherlock - jkstill_at_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 -1applications
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:
> Jared.Still_at_radisys.com 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: jkstill_at_cybcon.com 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: ListGuru_at_fatcity.com (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).Received on Thu Jan 30 2003 - 07:14:43 CST
 |
 |