ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用IOT表

使用IOT表

原创 Linux操作系统 作者:mingyan926 时间:2009-11-13 17:02:06 0 删除 编辑

IOT的物理存储结构

首先,要理解IOT表的物理存储模式。以下面这个表为例

CREATE TABLE SMSG_LOGS_ARCH_zxm_iot
   (  
   created_date timestamp (6) default sysdate,
   station_id varchar2(20),
    log_id number
,
    src_id varchar2(22),
   src_terminal_id varchar2(22),
   dest_id varchar2(22), 
   dest_terminal_id varchar2(22),
   command_id number,
   MSG_ID NUMBER,
    SERVICE_ID VARCHAR2(10),
   TP_PID NUMBER,
 TP_UDHI NUMBER,
 MSG_FMT NUMBER,
 REGISTERED_DELIVERY NUMBER,
 MSG_LENGTH NUMBER,
 MSG_CONTENT VARCHAR2(300),
 STAT VARCHAR2(7),
 SUBMIT_TIME VARCHAR2(10),
 DONE_TIME VARCHAR2(10),
 SMSC_SEQUENCE NUMBER,
 PK_TOTAL NUMBER,
 PK_NUMBER NUMBER,
 MSG_LEVEL NUMBER,
 FEE_USERTYPE NUMBER,
 FEE_TERMINAL_ID VARCHAR2(22),
 FEETYPE NUMBER,
 FEECODE VARCHAR2(6),
 VALID_TIME VARCHAR2(10),
 AT_TIME VARCHAR2(10),
 DESTUSR_TL NUMBER,
 SITE_ID VARCHAR2(22),
 NODE_ID VARCHAR2(22),
 UP_MSG_ID NUMBER,
 SENT_MSG_ID NUMBER,
   MSG_SRC VARCHAR2(1000),
 constraint smsg_logs_arch_pk primary key (created_date,station_id,log_id)
 ) 
  organization index
  tablespace sale_stage
  including  command_id
  overflow tablespace sale_stage

   只需要关注其中的红字部分。这个表创建成功后,接下来看这个表的物理存储。通过下面视图的查询来发现存储结构。

1、创建的对象仍然是按照指定名称命名,但是这个对象的DATA_OBJECT_ID是空,也就是这个对象并不是一个真正的物理对象。

select object_name,object_type,object_id,data_object_id  from user_objects

OBJECT_NAME                     OBJECT_TYPE          OBJECT_ID DATA_OBJECT_ID
------------------------- --------------------------------------------------------------------------------- ---------- --------------
 SYS_IOT_OVER_59513            TABLE                      59514          59514
SMSG_LOGS_ARCH_ZXM_IOT TABLE                      59513                    
 SMSG_LOGS_ARCH_PK            INDEX                      59515          59515

2、看这个表在user_tables中的定义,用到了iot_type和IOT_NAME两列

select table_name,iot_name,iot_type  from user_tables

TABLE_NAME                               IOT_NAME                                        IOT_TYPE   
------------------------------------------------------------------------------------------------------------------------------------
SYS_IOT_OVER_59513                SMSG_LOGS_ARCH_ZXM_IOT      IOT_OVERFLOW
SMSG_LOGS_ARCH_ZXM_IOT                                                             IOT

3、如果察看user_segments视图,就无法看到表名对应的Segment。而只能看到如下两条记录,这才是IOT表最终的Segment。

select segment_name,segment_type from user_segments

SEGMENT_NAME           SEGMENT_TYPE
------------------------------           ----------------------------
SMSG_LOGS_ARCH_PK   INDEX
SYS_IOT_OVER_59513    TABLE

4、INDEX段的信息,我们可以从约束中找到
select constraint_name,constraint_type,index_name from user_constraints

CONSTRAINT_NAME                   CONSTRAINT_TYPE                        INDEX_NAME     
-----------------------------------------------------------------------------------------------------------------------------------------   
SMSG_LOGS_ARCH_PK                P                                            SMSG_LOGS_ARCH_PK

INDEX_NAME                         INDEX_TYPE            TABLE_NAME                          
------------------------------------------------------------------------------------------------------------------------------------
A_PK                                       NORMAL                    A                                              
SMSG_LOGS_ARCH_PK       IOT - TOP                  SMSG_LOGS_ARCH_ZXM_IOT

因此
   Oracle会用两个段来实现一个IOT表,第一个段其实是一个Index Segment,用来支持IOT的主键,这个段的名称是根据PK名称命名的。而另一个段由于支持Overflow部分,这个段的名称是由系统自动命名的。

   而create table语句指定的表的名称已经变成了一个桥梁,又来连接着两个表。

   利用下面语句,就可以同时获得这两个Segment。

select table_name   segment_name,'overflow' type
from user_tables
where iot_name='SMSG_LOGS_ARCH_ZXM_IOT'
union all
select index_name,'index' type from user_indexes
where index_type='IOT - TOP'
    and table_name='SMSG_LOGS_ARCH_ZXM_IOT'

SEGMENT_NAME                TYPE   
-----------------------------------------------
SYS_IOT_OVER_59513     overflow
SMSG_LOGS_ARCH_PK    index   

比较IOT和堆表的性能

首先,创建两个结构相同的表,分别是IOT和堆表,然后用SQLLDR比较数据加载的速度。

CREATE TABLE SMSG_LOGS_ARCH_zxm
   (  
  
created_date timestamp (6) default sysdate,
    station_id varchar2(20),
    log_id number not null enable,
    src_id varchar2(22),
   src_terminal_id varchar2(22),
 dest_id varchar2(22), 
   dest_terminal_id varchar2(22),
   command_id number,
   MSG_ID NUMBER,
    SERVICE_ID VARCHAR2(10),
   TP_PID NUMBER,
 TP_UDHI NUMBER,
 MSG_FMT NUMBER,
 REGISTERED_DELIVERY NUMBER,
 MSG_LENGTH NUMBER,
 MSG_CONTENT VARCHAR2(300),
 STAT VARCHAR2(7),
 SUBMIT_TIME VARCHAR2(10),
 DONE_TIME VARCHAR2(10),
 SMSC_SEQUENCE NUMBER,
 PK_TOTAL NUMBER,
 PK_NUMBER NUMBER,
 MSG_LEVEL NUMBER,
 FEE_USERTYPE NUMBER,
 FEE_TERMINAL_ID VARCHAR2(22),
 FEETYPE NUMBER,
 FEECODE VARCHAR2(6),
 VALID_TIME VARCHAR2(10),
 AT_TIME VARCHAR2(10),
 DESTUSR_TL NUMBER,
 SITE_ID VARCHAR2(22),
 NODE_ID VARCHAR2(22),
 UP_MSG_ID NUMBER,
 SENT_MSG_ID NUMBER,
   MSG_SRC VARCHAR2(1000),
 constraint smsg_logs_arch_pk primary key (created_date,station_id,log_id)
 ) 
  organization index
  tablespace warehouse
  including  command_id
  overflow tablespace warehouse

  PARTITION BY RANGE (CREATED_DATE)
  SUBPARTITION BY LIST (STATION_ID)
  SUBPARTITION TEMPLATE (
    SUBPARTITION GUANGXI values ( 'GUANGXI' ),
    SUBPARTITION SHAN3XI values ( 'SHAN3XI' ),
    SUBPARTITION FUZHOU values ( 'FUZHOU' ),
    SUBPARTITION HANGZHOU values ( 'HANGZHOU' ),
    SUBPARTITION GUANGZHOU values ( 'GUANGZHOU' ),
    SUBPARTITION HEBEI values ( 'HEBEI' ),
    SUBPARTITION SHAN1XI values ( 'SHAN1XI' ),
    SUBPARTITION BEIJING values ( 'BEIJING' ),
    SUBPARTITION LIAONING values ( 'LIAONING' ),
    SUBPARTITION JILIN values ( 'JILIN' ),
    SUBPARTITION JIANGXI values ( 'JIANGXI' ),
    SUBPARTITION JIANGSU values ( 'JIANGSU' ),
    SUBPARTITION SICHUAN values ( 'SICHUAN' ),
    SUBPARTITION SHANGHAI values ( 'SHANGHAI' ),
    SUBPARTITION YUNNAN values ( 'YUNNAN' ),
    SUBPARTITION HENAN values ( 'HENAN' ),
    SUBPARTITION SHANDONG values ( 'SHANDONG' ) )
 (PARTITION P20091113  VALUES LESS THAN  to_date('2009-11-14 00:00:00','yyyy-mm-dd hh24:mi:ss')
 , PARTITION P20091114 VALUES LESS THAN to_date('2009-11-15 00:00:00','yyyy-mm-dd hh24:mi:ss')
 

第一个脚本遇到这个错误,也就是说IOT表不支持复合分区表结构。
 --ORA-25198
 00000, "only range, list, and hash partitioning are supported for index-organized table"
// *Cause:  System, or Composite partitioning schemes are not supported yet
// *Action: Select a different partitioning scheme

修改这个脚本

考虑:
分区是按照时间进行分区;
列的顺序或者说including 中列的顺序,是

按照 src_id varchar2(22),
   src_terminal_id varchar2(22),
  dest_id varchar2(22), 
   dest_terminal_id varchar2(22),
最后 是station_id为好呢,还是应该把station_id放在前面?

等测试下再说吧

CREATE TABLE SMSG_LOGS_ARCH_zxm
   (  src_id varchar2(22) default '999999999999999',
     dest_id varchar2(22) default '999999999999999', 
     created_date timestamp (6) default sysdate,
     station_id varchar2(20),
     log_id number ,
     command_id number,
     src_terminal_id varchar2(20),
     dest_terminal_id varchar2(20),
  ... ....
  constraint smsg_logs_arch_pk_2 primary key 
  (src_id,dest_id,created_date,station_id,log_id)
  ) 
  organization index
  tablespace warehouse
  including  command_id
  overflow tablespace warehouse
  PARTITION BY RANGE (CREATED_DATE)
 (PARTITION P20091116  VALUES LESS THAN  (to_date('2009-11-16 00:00:00','yyyy-mm-dd hh24:mi:ss') )
 , PARTITION P20091117 VALUES LESS THAN (to_date('2009-11-17 00:00:00','yyyy-mm-dd hh24:mi:ss') )
  , PARTITION P20091118 VALUES LESS THAN (to_date('2009-11-18 00:00:00','yyyy-mm-dd hh24:mi:ss') )
 )

加载性能

Total logical records skipped:          0
Total logical records read:      14489771
Total logical records rejected:         1
Total logical records discarded:        0

Run began on Wed Nov 18 11:55:41 2009
Run ended on Wed Nov 18 12:28:41 2009

Elapsed time was:     00:33:00.47
CPU time was:         00:05:10.96

               

结构相同的堆表,带有索引

drop table smsg_logs_arch_zxm_heap;

CREATE TABLE SMSG_LOGS_ARCH_zxm_heap
   (  src_id varchar2(22) default '999999999999999',
     dest_id varchar2(22) default '999999999999999', 
    created_date timestamp (6) default sysdate,
    station_id varchar2(20),
     log_id number ,
   command_id number,
   src_terminal_id varchar2(20),
   dest_terminal_id varchar2(20),
  ......,
  constraint smsg_logs_arch_pk_3 primary key (src_id,dest_id,created_date,station_id,log_id)
  ) 
  PARTITION BY RANGE (CREATED_DATE)
 (PARTITION P20091116  VALUES LESS THAN  (to_date('2009-11-16 00:00:00','yyyy-mm-dd hh24:mi:ss') )
 , PARTITION P20091117 VALUES LESS THAN (to_date('2009-11-17 00:00:00','yyyy-mm-dd hh24:mi:ss') )
  , PARTITION P20091118 VALUES LESS THAN (to_date('2009-11-18 00:00:00','yyyy-mm-dd hh24:mi:ss') )
 )


Total logical records skipped:          0
Total logical records read:      14489771
Total logical records rejected:         1
Total logical records discarded:        0

Run began on Wed Nov 18 12:32:02 2009
Run ended on Wed Nov 18 13:09:45 2009

Elapsed time was:     00:37:42.80
CPU time was:         00:05:08.68

load data                                             
infile '/backup/station_exp/hisdata/guangzhou/guangzhou_smsg_logs.exp' "str x'232323230A'"         
append into table smsg_logs_arch_zxm                           
FIELDS TERMINATED BY "," ENCLOSED BY '"'                   
(                                                  
LOG_ID INTEGER EXTERNAL ,                             
MSG_ID INTEGER EXTERNAL,                             
COMMAND_ID INTEGER EXTERNAL,                         
DEST_ID char   "9999999999999999",                                        
SERVICE_ID char,                                     
TP_PID INTEGER EXTERNAL,                             
TP_UDHI INTEGER EXTERNAL,                            
MSG_FMT INTEGER EXTERNAL,                            
SRC_TERMINAL_ID char,                                
REGISTERED_DELIVERY INTEGER EXTERNAL,                
MSG_LENGTH INTEGER EXTERNAL,                         
MSG_CONTENT char,                                    
STAT char,                                           
SUBMIT_TIME char,                                    
DONE_TIME char,                                      
DEST_TERMINAL_ID char,                              
SMSC_SEQUENCE INTEGER EXTERNAL,                      
PK_TOTAL INTEGER EXTERNAL,                           
PK_NUMBER INTEGER EXTERNAL,                         
MSG_LEVEL INTEGER EXTERNAL,                          
FEE_USERTYPE INTEGER EXTERNAL,                       
FEE_TERMINAL_ID char,                                
MSG_SRC char,                                        
FEETYPE INTEGER EXTERNAL,                            
FEECODE char,                                        
VALID_TIME char,                                     
AT_TIME char,                                        
SRC_ID char   "9999999999999999",                                         
DESTUSR_TL INTEGER EXTERNAL,                         
SITE_ID char,                                        
NODE_ID char,                                        
CREATED_DATE date "yyyy-mm-dd hh24:mi:ss",           
UP_MSG_ID INTEGER EXTERNAL,                          
STATION_ID char                                      
)
      

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/75321/viewspace-619600/,如需转载,请注明出处,否则将追究法律责任。

下一篇: IOT性能测试
请登录后发表评论 登录
全部评论

注册时间:2008-10-24

  • 博文量
    23
  • 访问量
    102207