Re: Help - "Business Day" Problem

On Apr 27, 12:41 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> <sybra..._at_hccnet.nl> a écrit dans le message de news: ndj433pb0t46mim6siqoq1et3cuciai..._at_4ax.com...
> | On Fri, 27 Apr 2007 18:30:27 +0200, "Michel Cadot"| <micadot{at}altern{dot}org> wrote:
>
> |
> | >
> | >"sybrandb" <sybra..._at_gmail.com> a écrit dans le message de news: 1177681440.943124.58..._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
>
> 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