oracle索引组织表----索引组织表性能测试

根据asktom上的例子,自己就索引组织表做了一些试验。

下面分别就索引组织表和普通表的一些性能对比做一些试验,创建两张表,一个为普通的表,另外一个为索引组织表:
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秒。差别显著!也许是普通表占的空间大的缘故吧!
再接下来的文章中,我将测试IOT表占有空间的情况!

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭