| 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
Jared
Windows 2k 9.2.0.1
534 hsecs
214 hsecs
Query I've used:
SELECT COUNT(
DISTINCT(
RPAD(NVL(ename, ' '), 30 + NVL2(ename, 0, 1))
|| RPAD(NVL( job, ' '), 30 + NVL2( job, 0, 1))
|| NVL(TO_CHAR(mydate, 'DDMMYYYYHH24MISS'), '*NULL*')
)
) AS l
As you can see it's tightly bound to table definition one has to handle nulls for varchars/chars.
L
7000
Check the resources -- I have doubts that this query is a winner :)
So, the moral of this story:
. never trust Vladimir Begun, check everything what he's saying :) . never use the sql that looks cool but does not work properly . never tune a query that returns wrong result and compare its
performance with that one that works correctly but slowly.
Thanks!
Regards,
-- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Vladimir Begun wrote:Received on Thu Jan 30 2003 - 14:16:11 CST
> Jared
>
> Jared Still wrote:
>
>> Though not a dramatic difference, the CONCAT was faster >> and less resource intensive than the inline view with GROUP BY. >> >> :)
>
>
> Ok, let it be like that, but your test does not check some
> other things, like common sense, logic, and session memory.
> Performance can vary as I mentioned sometimes can be
> neglected, however let's consider the tricks you made before
> your test:
>
> 1. create index emp_idx on emp(ename, job, mydate); -- what for do
> you need it? It was not in the original problem definition. It's
> not used, however you created it, what's that for?
>
> 2. Both queries give different results, that's what I mentioned --
> you just proved my words :) One must be very careful with that.
> Even DISTINCT can lose sometimes like in your example, but it does
> not mean that the logic of the application works correctly in
> case of || = CONCAT is used. Think also about an artificial limit
> your create -- each and every varchar has to be padded to it's
> maximum length (become CHAR) -- that optional and case dependent,
> however; all date and numeric columns have to be formatted
> otherwise you can face the same case like your your example.
>
> SQL> SELECT COUNT(*) FROM emp;
>
> COUNT(*)
> ----------
> 64000
>
> SQL> select count(distinct(ename||job||mydate)) FROM emp;
>
> COUNT(DISTINCT(ENAME||JOB||MYDATE))
> -----------------------------------
> 2000
>
> SQL> SELECT COUNT(*)
> 2 FROM (
> 3 SELECT DISTINCT
> 4 ename, job, mydate
> 5 FROM emp
> 6 );
>
> COUNT(*)
> ----------
> 7000
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: Vladimir.Begun_at_oracle.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).
 |
 |