Re: Select from dual return 3 rows !

From: stephen booth <stephenbooth.uk_at_gmail.com>
Date: Wed, 9 Nov 2005 10:59:17 +0000
Message-ID: <687bf9c40511090259q207aa335s@mail.gmail.com>

On 09/11/05, Norman Dunbar <norman.dunbar_at_environment-agency.gov.uk> wrote:
>
> Unfortunately due to my laptop eating its own hard drive, I'm currently
> without Oracle 10g and cannot test against USER_USERS but I suspect the
> stats are somewhat similar to the above.

>From 10g2 XE:

SQL> explain plan for select 'a' from user_users;

Explained.

SQL> @expln

PLAN_TABLE_OUTPUT

---

Plan hash value: 2017594212

---

| Id  | Operation                        | Name                    |
Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                         |  
  1 |    80 |    10  (10)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN            |                         |  
  1 |    80 |    10  (10)| 00:00:01 |
|*  2 |   HASH JOIN                      |                         |  
  1 |    72 |     9  (12)| 00:00:01 |
|   3 |    NESTED LOOPS                  |                         |  
  1 |    69 |     7  (15)| 00:00:01 |
|   4 |     NESTED LOOPS                 |                         |  
  1 |    66 |     6  (17)| 00:00:01 |
|*  5 |      HASH JOIN OUTER             |                         |  
  1 |    63 |     5  (20)| 00:00:01 |

PLAN_TABLE_OUTPUT

---

|*  6 |       TABLE ACCESS BY INDEX ROWID| PROFILE$                |  
  1 |     8 |     1   (0)| 00:00:01 |
|   7 |        NESTED LOOPS              |                         |  
  1 |    38 |     2   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS CLUSTER     | USER$                   |  
  1 |    30 |     1   (0)| 00:00:01 |
|*  9 |          INDEX UNIQUE SCAN       | I_USER#                 |  
  1 |       |     0   (0)| 00:00:01 |
|* 10 |         INDEX RANGE SCAN         | I_PROFILE               |  
 17 |       |     0   (0)| 00:00:01 |
|* 11 |       TABLE ACCESS FULL          | RESOURCE_GROUP_MAPPING$ |  
  2 |    50 |     2   (0)| 00:00:01 |
|  12 |      TABLE ACCESS CLUSTER        | TS$                     |  
  1 |     3 |     1   (0)| 00:00:01 |
|* 13 |       INDEX UNIQUE SCAN          | I_TS#                   |  
  1 |       |     0   (0)| 00:00:01 |
|  14 |     TABLE ACCESS CLUSTER         | TS$                     |  
  1 |     3 |     1   (0)| 00:00:01 |
|* 15 |      INDEX UNIQUE SCAN           | I_TS#                   |  
  1 |       |     0   (0)| 00:00:01 |
|  16 |    TABLE ACCESS FULL             | USER_ASTATUS_MAP        |  
  9 |    27 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT

---

|  17 |   BUFFER SORT                    |                         |  
  1 |     8 |     8  (13)| 00:00:01 |
|* 18 |    TABLE ACCESS BY INDEX ROWID   | PROFILE$                |  
  1 |     8 |     1   (0)| 00:00:01 |
|* 19 |     INDEX RANGE SCAN             | I_PROFILE               |  
 17 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   2 - access("U"."ASTATUS"="M"."STATUS#")
   5 - access("CGM"."VALUE"(+)="U"."NAME")
   6 - filter("P"."RESOURCE#"=1 AND "P"."TYPE#"=1)

PLAN_TABLE_OUTPUT

---

   8 - filter("U"."TYPE#"=1)
   9 - access("U"."USER#"=USERENV('SCHEMAID'))
  10 - access("U"."RESOURCE$"="P"."PROFILE#")
  11 - filter("CGM"."STATUS"(+)='ACTIVE' AND "CGM"."ATTRIBUTE"(+)='ORACLE_USER')
  13 - access("U"."DATATS#"="DTS"."TS#")
  15 - access("U"."TEMPTS#"="TTS"."TS#")
  18 - filter("DP"."RESOURCE#"=1 AND "DP"."TYPE#"=1)
  19 - access("DP"."PROFILE#"=0)

41 rows selected.

Execution Plan

---

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=29 Card=8168 Bytes
          =16336)

   1    0   COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY' (PROCEDUR
          E)

Statistics

---

         62  recursive calls
         22  db block gets
        174  consistent gets
          0  physical reads
          0  redo size
       4055  bytes sent via SQL*Net to client
        521  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         41  rows processed

SQL> HTH Stephen

--
It's better to ask a silly question than to make a silly assumption.

http://stephensorablog.blogspot.com/
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 09 2005 - 05:01:20 CST