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

0. 环境创建

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> 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> alter sessionset optimizer_mode=all_rows;

Session altered.

 

1.3 分析

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

2. 数据少时的情况

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> setautotrace traceonly

SQL> altersession set optimizer_mode=first_rows_10;

Session altered.

 

2.2 ALL_ROWS

SQL> setautotrace traceonly

SQL> altersession set optimizer_mode=all_rows;

Session altered.

 

2.3 分析

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