Oracle执行计划——all_rows和first_rows(n)优化器模式

0. 环境创建

[sql] 
SQL> create usertest identified by test  
  
  2 default tablespace users  
  
  3 temporary tablespace temp  
  
  4 quota unlimited on users;  
  
User created.  
  
SQL> grant createsession, resource, alter session to test;  
  
Grant succeeded.  
  
SQL> conntest/test;  
  
Connected.  
  
SQL> create tableemp(id number, name varchar2(10));  
  
Table created.  
  
SQL> insert intoemp values(100, 'tom');  
  
1 row created.  
  
SQL> insert intoemp values(200, 'mike');  
  
1 row created.  
  
SQL> insert intoemp values(300, 'jack');  
  
1 row created.  
  
SQL> insert intoemp values(400, 'rose');  
  
1 row created.  
  
SQL> commit;  
  
Commit complete.

1. 数据少时的情况

1.1 FIRST_ROWS_10

[sql] 
SQL> setautotrace traceonly;  
  
SQL> executedbms_stats.gather_table_stats('TEST', 'emp', cascade=>true);  
  
PL/SQL proceduresuccessfully completed.  
  
SQL> altersession set optimizer_mode=first_rows_10;  
  
Session altered.

1.2 ALL_ROWS

[sql] 
SQL> alter sessionset optimizer_mode=all_rows;  
  
Session altered.

1.3 分析

通过上面的简单举例比较，我们可以看到，在表上没有索引，当数据量很少，并且值唯一的情况下，两种模式的表现是一样的

2. 数据少时的情况

[sql] 
SQL> create indexemp_idx on emp(name);  
  
Index created.  
  
SQL> setautotrace off  
  
SQL> insert intoemp select * from emp;  
  
4 rows created.  
  
SQL> insert intoemp select * from emp;  
  
8 rows created.  
  
SQL> /  
  
16 rows created.  
  
SQL> /  
  
32 rows created.  
  
SQL> /  
  
64 rows created.  
  
SQL> /  
  
128 rows created.  
  
SQL> /  
  
256 rows created.  
  
SQL> /  
  
512 rows created.  
  
SQL> /  
  
1024 rows created.  
  
SQL> /  
  
2048 rows created.  
  
SQL> /  
  
4096 rows created.  
  
SQL> /  
  
8192 rows created.  
  
SQL> /  
  
16384 rows created.  
  
SQL> /  
  
32768 rows created.  
  
SQL> commit;  
  
Commit complete.  
  
SQL> executedbms_stats.gather_table_stats('TEST', 'emp', cascade=>true);  
  
PL/SQL proceduresuccessfully completed.

2.1 FIRST_ROWS_10

[sql] 
SQL> setautotrace traceonly  
  
SQL> altersession set optimizer_mode=first_rows_10;  
  
Session altered.

2.2 ALL_ROWS

[sql] 
SQL> setautotrace traceonly  
  
SQL> altersession set optimizer_mode=all_rows;  
  
Session altered.

2.3 分析

我们看到fisrt_rows走了索引，这显然不是一种理想的结果，而all_rows走了全表扫描，我们可以看到成本明显更低。