Re: Select from dual return 3 rows !
- From: stephen booth <stephenbooth.uk@xxxxxxxxx>
- To: norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx
- Date: Wed, 9 Nov 2005 10:59:17 +0000
On 09/11/05, Norman Dunbar <norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx> 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/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
