Oracle-L: Re: SQL question

From: <Jared.Still_at_radisys.com>
Date: Thu, 30 Jan 2003 13:53:56 -0800
Message-ID: <F001.0053FACF.20030130135356@fatcity.com>

> 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:

Well, I've never claimed to be common.

And I didn't ignore session memory. If the PGA and UGA memory numbers are the same between runs, they don't appear in the run_stats.sql query.

> 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?

An index seemed reasonable to me.

But in the cause of fairness, I dropped the index and reran it a couple times to allow for re-caching the blocks. Similar results.

13:17:17 SQL>@th
389 hsecs
257 hsecs

PL/SQL procedure successfully completed. 13:17:25 SQL>@run_stats

NAME                                           RUN1       RUN2       DIFF
---------------------------------------- ---------- ---------- ----------
LATCH.active checkpoint queue latch               2          1         -1
LATCH.redo writing                                6          5         -1
STAT...calls to get snapshot scn: kcmgss         12         11         -1
STAT...redo entries                               8          7         -1
STAT...messages sent                              1          0         -1
STAT...deferred (CURRENT) block cleanout          3          2         -1

applications

LATCH.library cache                              74         71         -3
STAT...db block changes                          17         14         -3
STAT...consistent gets                         3346       3343         -3
LATCH.redo allocation                             9         12          3
STAT...session logical reads                   3458       3462          4
LATCH.messages                                   11          6         -5
STAT...db block gets                            112        119          7
STAT...enqueue requests                          41         50          9
LATCH.sort extent pool                           90        100         10
STAT...enqueue releases                          40         50         10
LATCH.enqueue hash chains                        80        100         20
LATCH.cache buffers lru chain                    63         23        -40
LATCH.loader state object freelist               20         60         40
LATCH.enqueues                                  160        200         40
LATCH.checkpoint queue latch                     90          7        -83
LATCH.cache buffers chains                     7662       7783        121
STAT...redo size                              20888      20756       -132
STAT...recursive cpu usage                      391        258       -133
STAT...physical reads                           552        792        240
STAT...physical reads direct                    550        790        240
STAT...physical writes                          550        790        240
STAT...physical writes direct                   550        790        240
STAT...physical writes non checkpoint           550        790        240
STAT...session pga memory                         0      15848      15848
STAT...session pga memory max                     0      15848      15848

31 rows selected.

> 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

Interesting. My results correspond, I don't know why the difference.

13:11:04 SQL>set echo on
13:11:08 SQL>@q1
13:11:09 SQL>select count(distinct(ename||job||mydate))
13:11:09   2  from emp
13:11:09   3  /

COUNT(DISTINCT(ENAME||JOB||MYDATE))

1 row selected.

13:11:09 SQL>@q2
13:11:12 SQL>
13:11:12 SQL>
13:11:12 SQL>SELECT COUNT(*)
13:11:12   2  FROM (
13:11:12   3          SELECT DISTINCT
13:11:12   4                  ename, job, mydate
13:11:12   5          FROM emp
13:11:12   6  )
13:11:12   7  /

COUNT(*)

7000

1 row selected.

13:11:12 SQL>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.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 - 15:53:56 CST