Re: Help - "Business Day" Problem

On Fri, 27 Apr 2007 18:30:27 +0200, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

>
>"sybrandb" <sybrandb_at_gmail.com> a écrit dans le message de news: 1177681440.943124.58320_at_r30g2000prh.googlegroups.com...
>| On Apr 27, 3:23 pm, "pankaj_wolfhun..._at_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