Re: Help - "Business Day" Problem


"sybrandb" <sybrandb@xxxxxxxxx> a écrit dans le message de news: 1177681440.943124.58320@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


.

Relevant Pages

  • Re: Help with Query
    ... When the original query is slid into an inline view and joined to the ... Predicate Information: ... Without allowing the automatic transformations in Oracle 10.2.0.2, ...
    (comp.databases.oracle.misc)
  • Re: Help with Query
    ... When the original query is slid into an inline view and joined to the ... Predicate Information: ... Without allowing the automatic transformations in Oracle 10.2.0.2, ...
    (comp.databases.oracle.misc)
  • Re: [long post] Re: exists or limit
    ... Bytes | Cost | Time | ... Predicate Information: ... why I really don't understand is that query B is ... If exists really stop at the first matching line, ...
    (comp.databases.oracle.misc)
  • Re: unexpected merge cartesian join
    ... One query has to visit tables ... This should force Oracle into using the ... > Predicate Information: ... > Note: cpu costing is off ...
    (comp.databases.oracle.server)