当前位置:数据库 > Oracle >>

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

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走了全表扫描,我们可以看到成本明显更低。
 
Oracle
MySQL
Access
SQLServer
DB2
Excel
SQLite
SYBASE
Postgres
如果你遇到数据库难题:
请访问www.zzzyk.com 试试
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,