Re: Select Distinct with LOB fields

From: <fitzjarrell_at_cox.net>
Date: Thu, 06 Sep 2007 06:58:31 -0700
Message-ID: <1189087111.691276.268290@w3g2000hsg.googlegroups.com>

On Sep 6, 2:55 am, "Andy Fish" <ajf..._at_blueyonder.co.uk> wrote:
> > select pk, data from foo where pk in (select fk from bar);
>
> thanks for this hint (and to cleveridea) for the other one.
>
> any idea if either of these have significant performance deteriment compared
> to the basic select ?

With 10.2.0.3 I find this for similar selects from the demo tables:

SQL> select *
2 from dept
3 where exists (select null from emp where deptno = dept.deptno);

DEPTNO DNAME LOC
---------- -------------- -------------

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO

Execution Plan

Plan hash value: 1421890032

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |   120 |     4   (0)|
00:00:01 |
|*  1 |  FILTER            |      |       |       |
|          |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |   120 |     2   (0)|
00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     1 |    13 |     2   (0)|

00:00:01 |

Predicate Information (identified by operation id):

1 - filter( EXISTS (SELECT /*+ */ 0 FROM "EMP" "EMP" WHERE

"DEPTNO"=:B1))
3 - filter("DEPTNO"=:B1)

Note

dynamic sampling used for this statement

Statistics

          0  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        353  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL> select * from dept
2 where deptno in (select deptno from emp);