使用IOT表

最新推荐文章于 2022-11-11 17:44:05 发布

cuiguizhuo6372

最新推荐文章于 2022-11-11 17:44:05 发布

阅读量246

点赞数

文章标签：嵌入式数据库

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/75321/viewspace-619600/

cuiguizhuo6372

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
使用IOT表

IOT的物理存储结构首先，要理解IOT表的物理存储模式。以下面这个表为例CREATE TABLE SMSG_LOGS_ARCH_zxm_iot ( created_date timestamp (6) de...
复制链接

扫一扫