oracle iot索引组织表(一)

最新推荐文章于 2024-09-14 22:08:32 发布

clg10051

最新推荐文章于 2024-09-14 22:08:32 发布

阅读量105

点赞数

文章标签：嵌入式数据库

oracle iot索引组织表(一)
---iot语法之 index_org_table_clause ::=
[ { mapping_table_clause
| PCTTHRESHOLD integer
| key_compression
}...
]
[ index_org_overflow_clause ]


---mapping_table_clause选项

---下为语义
如指定为mapping table,oracle创建本地到物理rowid的映射结构表,并把它存储到heap-organized table;
mapping_table_clauses Specify MAPPING TABLE to instruct the database to create a mapping of local to physical ROWIDs and
---这个映射结构表的用途是,在iot上创建一个位图索引
store them in a heap-organized table. This mapping is needed in order to create a bitmap index on the index-organized table.
---如iot分区,映射结构表也同样分区,且和iot的结构一致
If the index-organized table is partitioned, then the mapping table is also partitioned and its partitions have the same name and
physical attributes as the base table partitions.
-----oracle会创建和父iot表一样,为其映射结构表进行分区,且同属于相同表空间
Oracle Database creates the mapping table or mapping table partition in the same tablespace as its parent index-organized table
------不能修改映射结构表或其分区,也不能查询其存储参数分配
or partition. You cannot query, perform. DML operations on, or modify the storage characteristics of the mapping table or its partitions.


---PCTTHRESHOLD integer 选项
---下为语义
-----为iot表的记录指定用于过索引数据块保留的空间比例.
PCTTHRESHOLD integer Specify the percentage of space reserved in the index block for an index-organized table row.
---pctthreshold必须充足以容纳主键.如超过这个空间比例则存储在overflow segment(注:此子句后述会介绍)
PCTTHRESHOLD must be large enough to hold the primary key. All trailing columns of a row, starting with the column that
                   ----pctthreshold的值:1~50,默认是50
causes the specified threshold to be exceeded, are stored in the overflow segment. PCTTHRESHOLD must be a value from 1 to 50.
If you do not specify PCTTHRESHOLD, then the default is 50.

---操作限制:不能为iot分区指定此参数
Restriction on PCTTHRESHOLD You cannot specify PCTTHRESHOLD for individual partitions of an index-organized table.



---key_compressiont选项
--是否开启iot压缩功能
key_compression The key_compression clauses let you enable or disable key compression for index-organized tables.

------integer指定前缀长度
Specify COMPRESS to enable key compression, which eliminates repeated occurrence of primary key column values in index-organized tables.
Use integer to specify the prefix length, which is the number of prefix columns to compress. ---测试
     ---前缀长度值:1~主键列个数-1,默认是键列个数-1
      SQL> create table t_test(a int) organization index mapping table;
The valid range of prefix length values is from 1 to the number of primary key columns minus 1. The default prefix length is the number of
primary key columns minus 1.


create table t_test(a int) organization index mapping table
---默认是非压缩
Specify NOCOMPRESS to disable key compression in index-organized tables. This is the default.


----操作限制:iot分区可指定compress,但不能指定前缀长度
Restriction on Key Compression of Index-organized Tables At the partition level, you can specify COMPRESS,
but you cannot specify the prefix length with integer.


--[ index_org_overflow_clause ] 子句
--语义
---即:存储超过threshold参数指定值的数据
index_org_overflow_clause The index_org_overflow_clause lets you instruct the database that index-organized table data
rows exceeding the specified threshold are placed in the data segment specified in this clause.
----创建iot,oracle自动评估每列最大值计算得到最大行长度
When you create an index-organized table, Oracle Database evaluates the maximum size of each column to estimate the largest possible row.
----如需要overflow segment,但未创建;oracle报错返回
If an overflow segment is needed but you have not specified OVERFLOW, then the database raises an error and does not execute the CREATE TABLE
statement. This checking function guarantees that subsequent DML operations on the index-organized table will not fail because an overflow segment is lacking.

-----overflow之后指定的物理和存储参数属性仅用于overflow segment
All physical attributes and storage characteristics you specify in this clause after the OVERFLOW keyword apply only to the overflow segment of the table.
Physical attributes and storage characteristics for the index-organized table itself, default values for all its partitions, and values for individual
partitions must be specified before this keyword.

------ 如iot包含一或多个lob,lobs存储在行外;如指定overflow则反之;即便在行内可以容纳如不指定overflow也存储在行外;
If the index-organized table contains one or more LOB columns, then the LOBs will be stored out-of-line unless you specify OVERFLOW, even if they would
otherwise be small enough be to stored inline.

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

转载于:http://blog.itpub.net/9240380/viewspace-753206/