> > <sybra@hccnet.nl> a crit dans le message de news: ndj433pb0t46mim6siqoq1et3cuciai@4ax.com...
> > | On Fri, 27 Apr 2007 18:30:27 +0200, "Michel Cadot"| <micadot{at}altern{dot}org> wrote:

> > |
> > | >
> > | >"sybrandb" <sybra@gmail.com> a crit dans le message de news: 1177681440.943124.58@r30g2000prh.googlegroups.com...
> > | >| On Apr 27, 3:23 pm, "pankaj_wolfhun@yahoo.co.in"
> > | >|
> > | >| Exactly.
> > | >| In fact the most efficient way to check for existence of a record is
> > | >| select 1
> > | >| from dual
> > | >| where exists
> > | >| (select 1
> > | >| from emp where empno = :1)
> > | >|
> > | >| Subqueries over DUAL: I love them!
> > | >|
> > | >| --
> > | >| Sybrand Bakker
> > | >| Senior Oracle DBA
> > | >|
> > | >
> > | >Is this faster than:
> > | >
> > | >select 1 from emp where empno = :1 and rownum = 1;
> > | >
> > | >-------------------------------------------------------------------------- ---
> > | >| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
> > | >-------------------------------------------------------------------------- ---
> > | >|   0 | SELECT STATEMENT   |        |     1 |     4 |     0   (0)| 00:00:01 |
> > | >|*  1 |  COUNT STOPKEY     |        |       |       |            |          |
> > | >|*  2 |   INDEX UNIQUE SCAN| PK_EMP |     1 |     4 |     0   (0)| 00:00:01 |
> > | >-------------------------------------------------------------------------- ---
> > | >
> > | >Predicate Information (identified by operation id):
> > | >---------------------------------------------------
> > | >
> > | >   1 - filter(ROWNUM=1)
> > | >   2 - access("EMPNO"=TO_NUMBER(:1))
> > | >
> > | >Your query:
> > | >
> > | >-------------------------------------------------------------------------- ---
> > | >| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
> > | >-------------------------------------------------------------------------- ---
> > | >|   0 | SELECT STATEMENT   |        |     1 |       |     2   (0)| 00:00:01 |
> > | >|*  1 |  FILTER            |        |       |       |            |          |
> > | >|   2 |   FAST DUAL        |        |     1 |       |     2   (0)| 00:00:01 |
> > | >|*  3 |   INDEX UNIQUE SCAN| PK_EMP |     1 |     4 |     0   (0)| 00:00:01 |
> > | >-------------------------------------------------------------------------- ---
> > | >
> > | >Predicate Information (identified by operation id):
> > | >---------------------------------------------------
> > | >
> > | >   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "EMP" "EMP" WHERE
> > | >              "EMPNO"=TO_NUMBER(:1)))
> > | >   3 - access("EMPNO"=TO_NUMBER(:1))
> > | >
> > | >
> > | >Regards
> > | >Michel Cadot
> > | >
> > |
> > | I don't see much difference, I must say.
> > | But I was implicitly referring to the approach I see many times:
> > | select count(*)
> > | into dummy
> > | from emp
> > | where empno = :emp;
> > | if dummy > 0 then
> > | select ename into
> > | ...
> > | from emp
> > | where empno = : emp;
> > |
> > | I even saw this when the predicate wasn't a PK but a FK.
> > |
> > | --
> > | Sybrand Bakker
> > | Senior Oracle DBA

> > I didn't have any opinion, that was just an open remark.
> > I pretty understand your point. :)

> > Regards
> > Michel Cadot

> I think that "fast dual" indicates the answer to this may vary by
> version.  Not to mention, vary by 1000 users doing it 10000 times in
> an update :-O

> jg
> --
> @home.com is bogus.
> "Now you're just screwing with my mind! While I was commenting on your
> last blog, you published a new blog about my blog, about ... I need to
> lie down! ;-)" - Doug Burns- Hide quoted text -

>