执行计划中的row

create table tab1 as select level as id, 'description of ' || level as name from dual connect by level <= 1000;
create table tab2 as select level as id, 'description of ' || level as name from dual connect by level <= 2000;
insert into tab2 select * from tab2; -- 连续执行三次
commit;
create index idx_tab2 on tab2(name);
-- 搜集两张表的统计信息
select * from user_tables where table_name like 'TAB%';


1. select * from tab1 a, tab2 b where a.name = b.name and b.id > 900;

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4409 | 198K| 24 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 4409 | 198K| 24 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TAB1 | 1000 | 23000 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TAB2 | 8804 | 197K| 20 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."NAME"="B"."NAME")
3 - filter("B"."ID">900)

select count(*) from tab2 b where b.id > 900; -- 8800行

可以看到table access full是过滤后的行。


2. select /*+use_nl(a,b)*/* from tab1 a, tab2 b where a.name = b.name and a.id > 100 and b.id > 900;

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3972 | 178K| 8115 (1)| 00:00:01 |
| 1 | NESTED LOOPS | | 3972 | 178K| 8115 (1)| 00:00:01 |
| 2 | NESTED LOOPS | | 7208 | 178K| 8115 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | TAB1 | 901 | 20723 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_TAB2 | 8 | | 1 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| TAB2 | 4 | 92 | 9 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("A"."ID">100)
4 - access("A"."NAME"="B"."NAME")
5 - filter("B"."ID">900)


可以看到INDEX RANGE SCAN只有8行，也就是通过第四步的name进行过滤返回的行，第五步只有4行，即在第四步的基础上过滤B.ID>900的行。


3. select /*+use_nl(a,b) cardinality(b 100000)*/ * from tab1 a, tab2 b where a.name = b.name and a.id > 100 and b.id > 900;

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45045 | 2023K| 8115 (1)| 00:00:01 |
| 1 | NESTED LOOPS | | 45045 | 2023K| 8115 (1)| 00:00:01 |
| 2 | NESTED LOOPS | | 45045 | 2023K| 8115 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | TAB1 | 901 | 20723 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_TAB2 | 8 | | 1 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| TAB2 | 50 | 1150 | 9 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("A"."ID">100)
4 - access("A"."NAME"="B"."NAME")
5 - filter("B"."ID">900)

加了cardinality之后，INDEX RANGE SCAN并没有发生变化，只是TABLE ACCESS BY INDEX ROWID的数量变多了。即cardinality并不影响索引的预估行，只影响通过索引回表的行。


4. select /*+cardinality(a 100)*/ * from tab1 a, tab2 b where a.name = b.name and a.id > 200 and b.id > 900;

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 441 | 20286 | 24 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 441 | 20286 | 24 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TAB1 | 100 | 2300 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TAB2 | 8804 | 197K| 20 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."NAME"="B"."NAME")
2 - filter("A"."ID">200)
3 - filter("B"."ID">900)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~