http://blog.itpub.net/post/5073/30310索引组织表
下面分别就索引组织表和普通表的一些性能对比做一些试验，创建两张表，一个为普通的表，另外一个为索引组织表：
C:\>sqlplus
SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 5月 19 11:09:06 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
请输入用户名: wwf/wwf
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
create table heap_stocks
( symbol varchar2(4),
ticker_dt date,
price number,
constraint heap_stocks_pk primary key (symbol,ticker_dt)
);
create table iot_stocks
( symbol varchar2(4),
ticker_dt date,
price number,
constraint iot_stocks_pk primary key (symbol,ticker_dt)
)
organization index compress 1;
上面模仿股票，分别存放股票代码，日期，收盘价格三个字段。下面，我们插入分别对这两个表插入1000种股票200天的数据，看看其插入数据时的性能：
1 插入数据
SQL> set timing on
SQL> begin
2 for i in 1..200 loop
3 insert into heap_stocks
4 select to_char(rownum, 'fm0009'), trunc(sysdate)+i, rownum
5 from all_objects where rownum <= 1000;
6 end loop;
7 commit;
8 end;
9 /
PL/SQL 过程已成功完成。
已用时间: 00: 00: 18.06
SQL> set timing on
SQL> begin
2 for i in 1..200 loop
3 insert into iot_stocks
4 select to_char(rownum, 'fm0009'), trunc(sysdate)+i, rownum
5 from all_objects where rownum <= 1000;
6 end loop;
7 commit;
8 end
9 ;
10 /
PL/SQL 过程已成功完成。
已用时间: 00: 00: 31.07
可以看到，插入20万条数据，普通表用了18秒，而IOT表用了31秒，相差明显。这说明插入数据时，IOT表的速度是相当慢的。
2. 查询
我们重新启动一下数据库：
SQL> conn
请输入用户名: sys / nolog as sysdba
已连接。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中断开
然后重新登录：
SQL> conn
请输入用户名: wwf/wwf
已连接。
a. 使用autotrace测试
SQL> set autotrace traceonly
SQL> set timing on
SQL> set autotrace traceonly
SQL> select * from heap_stocks where symbol = '0001';
已选择200行。
已用时间: 00: 00: 00.08
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HEAP_STOCKS'
2 1 INDEX (RANGE SCAN) OF 'HEAP_STOCKS_PK' (UNIQUE)
Statistics
----------------------------------------------------------
239 recursive calls
0 db block gets
259 consistent gets
207 physical reads
0 redo size
5706 bytes sent via SQL*Net to client
646 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
200 rows processed
SQL> select * from iot_stocks where symbol = '0001';
已选择200行。
已用时间: 00: 00: 00.02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=82 Bytes=2132)
1 0 INDEX (RANGE SCAN) OF 'IOT_STOCK_PK' (UNIQUE) (Cost=2 Card=82 Bytes=2132)
Statistics
----------------------------------------------------------
299 recursive calls
0 db block gets
63 consistent gets
4 physical reads
0 redo size
5706 bytes sent via SQL*Net to client
646 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
200 rows processed
逻辑读分别为259和63，差别显著！说明，查询时，IOT表性能要远远优越于普通的表！
b 使用sql_trace测试:
SQL> conn
请输入用户名: wwf/wwf
已连接。
SQL> alter session set sql_trace = true;
会话已更改。
SQL> select avg(price) from heap_stocks where symbol = '0001';
AVG(PRICE)
----------
1
SQL> select avg(price) from iot_stocks where symbol = '0001';
AVG(PRICE)
----------
1
SQL> alter session set sql_trace = false;
会话已更改。
使用tkprof格式化输出文件，得到如下结果：
select avg(price) from heap_stocks where symbol = '0001'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.31 0.33 1 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.39 203 208 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.31 0.73 204 209 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
200 TABLE ACCESS BY INDEX ROWID HEAP_STOCKS
200 INDEX RANGE SCAN HEAP_STOCKS_PK (object id 30391)
select avg(price) from iot_stocks where symbol = '0001'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.03 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 2 0.00 0.07 3 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.11 3 4 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
200 INDEX RANGE SCAN IOT_STOCK_PK (object id 30393)
看看Tom怎么说：
So, we did 203 physical IO's to process the HEAP table. What that tells me is
that our data for stock symbol 0001 is spread out on 200 blocks (200 days, 200
blocks). In order to cache the results for this query, we need 200 block
buffers. We needed to do that IO to get the answer initially.
Now, looking at the IOT we did 3 physical IO's -- we cached 3 blocks -- and got
the same answer! Not only that but by using index key compression I was able to
remove he redudant 0001's from the data -- we can cache this much more
efficiently and getting it the first time takes seriously less IO. Very nice.
3 删除数据
SQL> set autotrace off
SQL> delete from heap_stocks;
已删除200000行。
已用时间: 00: 00: 26.02
SQL> delete from iot_stocks;
已删除200000行。
已用时间: 00: 00: 08.08
可以看到，删除数据时，普通表用了26秒，而IOT表用了8秒。差别显著！也许是普通表占的空间大的缘故吧！
