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.

oracle iot索引組織表(一)

相關文章