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走了全表掃描，我們可以看到成本明顯更低。