create table wxh_tbd1 as select * from dba_objects;
create table wxh_tbd2 as select * from dba_objects;
create index t_1 on wxh_tbd1(object_name);
create index t_2 on wxh_tbd1(object_id);
create index t_3 on wxh_tbd2(object_name);
insert into wxh_tbd1 select * from wxh_tbd1;
insert into wxh_tbd2 select * from wxh_tbd2;
commit;
select /*+ NO_NLJ_BATCHING(b) */
dbms_rowid.rowid_block_number(b.rowid) bl, a.object_id,b.object_type
from wxh_tbd1 a, wxh_tbd2 b
where a.object_name = b.object_name
and a.object_id > 899 and a.object_id<902;
sys@CRMG>select /*+ NO_NLJ_BATCHING(b) */
2 dbms_rowid.rowid_block_number(b.rowid) bl, a.object_id,b.object_type
3 from wxh_tbd1 a, wxh_tbd2 b
4 where a.object_name = b.object_name
5 and a.object_id > 899 and a.object_id<902;
BL OBJECT_ID OBJECT_TYPE
---------- ---------- --------------------------------------
23667 900 TABLE
32537 900 TABLE
23667 900 TABLE
32537 900 TABLE
23667 901 INDEX
32537 901 INDEX
23667 901 INDEX
32537 901 INDEX
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1274335627
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 4200 | 13 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | WXH_TBD2 | 6 | 534 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 25 | 4200 | 13 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| WXH_TBD1 | 4 | 316 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T_2 | 4 | | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T_3 | 6 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."OBJECT_ID">899 AND "A"."OBJECT_ID"<902)
5 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
166 consistent gets
0 physical reads
0 redo size
790 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
8 rows processed
sys@CRMG>select
dbms_rowid.rowid_block_number(b.rowid) bl, a.object_id,b.object_type
2 3 from wxh_tbd1 a, wxh_tbd2 b
4 where a.object_name = b.object_name
5 and a.object_id > 899 and a.object_id<902;
BL OBJECT_ID OBJECT_TYPE
---------- ---------- --------------------------------------
23667 900 TABLE
32537 900 TABLE
23667 900 TABLE
32537 900 TABLE
23667 901 INDEX
32537 901 INDEX
23667 901 INDEX
32537 901 INDEX
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 748461386
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 4200 | 13 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 25 | 4200 | 13 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| WXH_TBD1 | 4 | 316 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T_2 | 4 | | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T_3 | 6 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | WXH_TBD2 | 6 | 534 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."OBJECT_ID">899 AND "A"."OBJECT_ID"<902)
5 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
10 recursive calls
0 db block gets
166 consistent gets
0 physical reads
0 redo size
790 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
8 rows processed
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-703358/,如需转载,请注明出处,否则将追究法律责任。