- 论坛徽章:
- 0
|
以Oracle10g为例
Oracle10g优化就是CBO,比如optimizer_mode,在10g只有FIRST_ROWS_N, FIRST_ROWS, ALL_ROWS。FIRST_ROWS 主要追求的是数据返回的时间,ALL_ROWS追求的是系统资源使用的最小化。举例:
CREATE TABLE test1(
id NUMBER,
name VARCHAR2(10)
) tablespace users;
CRATE INDEX test1_id on test1(id) tablespace indx;
insert 测试数据,结果如下:
SQL> select id,name from test1;
ID NAME
---------- ----------
1 01
100 100
10 10
5 05
99 99
9 09
69 69
59 59
sqlplus>set autotrace on
sqlplus>alter session set optimizer_mode=all_rows;
SQL> select name from test1 where id between 10 and 50;
NAME
----------
10
Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST1 | 1 | 20 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">=10 AND "ID"<=50)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
511 bytes sent via SQL*Net to client
468 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
后续中
原帖由 寒鸦 于 2006-8-8 14:40 发表
optimizer mode=choose:
表在A列上有索引
select * from table_name where a=** or a=**;
select * from table_name where a in (**,**,**);
select * from table_name where a between ** and **;
以上三 ... |
|