Re: Help - "Business Day" Problem

On Apr 28, 3:26 am, joel garry <joel-ga...@xxxxxxxx> wrote:
On Apr 27, 12:41 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:





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

|
| >
| >"sybrandb" <sybra...@xxxxxxxxx> a écrit dans le message de news: 1177681440.943124.58...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
| >| On Apr 27, 3:23 pm, "pankaj_wolfhun...@xxxxxxxxxxx"
| >|
| >| 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 -

- Show quoted text -

Thanks for all the replies.
Sybrand, I think I am not able to get your point

Query with dual will only give me the "existence" of the record.

SELECT 1
FROM dual
WHERE TO_CHAR(TO_DATE ('&2') - 1,'DY') NOT IN ('SAT','SUN')

Enter value for 2: 28-APR-2007
old 3: WHERE TO_CHAR(TO_DATE ('&2') - 1,'DY') NOT IN ('SAT','SUN')
new 3: WHERE TO_CHAR(TO_DATE ('28-APR-2007') - 1,'DY') NOT IN
('SAT','SUN')

1
----------
1

SELECT 1
FROM dual
WHERE TO_CHAR(TO_DATE ('&2') - 1,'DY') NOT IN ('SAT','SUN')

Enter value for 2: 30-APR-2007
old 3: WHERE TO_CHAR(TO_DATE ('&2') - 1,'DY') NOT IN ('SAT','SUN')
new 3: WHERE TO_CHAR(TO_DATE ('30-APR-2007') - 1,'DY') NOT IN
('SAT','SUN')

no rows selected

What I want is the business date prev to the date provided. So if I
enter 30-APR-2007, I should get 27-Apr-2007

Sorry if I am not able to get your point or If I was not clear with my
requirement.

.

Relevant Pages

  • Re: Help - "Business Day" Problem
    ... |>Predicate Information: ... | from emp ... | where empno =: emp; ... last blog, you published a new blog about my blog, about ... ...
    (comp.databases.oracle.misc)
  • Re: Help - "Business Day" Problem
    ... |>Predicate Information: ... | from emp ... | if dummy> 0 then ... | where empno =: emp; ...
    (comp.databases.oracle.misc)
  • Re: Help - "Business Day" Problem
    ... | from emp where empno =:1) ... Predicate Information: ... where empno =:emp; ... if dummy> 0 then ...
    (comp.databases.oracle.misc)