使用IOT在Oracle中创建聚簇索引?困惑 [英] Creating a clustered-index in Oracle using an IOT? Confusion

问题描述

我在理解如何将IOT用作oracle中的聚集索引时遇到了一些麻烦.假设我有一个表order(order_id，part_id，customer_id，order_date)，并且我想使用将要创建的IOT创建聚簇索引.

I'm having a little trouble understanding how to use an IOT as a clustered index in oracle. Say I have a table order(order_id,part_id,customer_id,order_date), and I want to create an clustered index using an IOT I would do.

CREATE TABLE clust_order(
    order_id number,
    part_id number,
    CONSTRAINT part_pk PRIMARY KEY (part_id)
)ORGANIZATION INDEx;

我得到的结果等同于聚集索引，但是我不了解它与原始表的关系.我是否必须从原始表插入物联网，然后用它代替原始表?

I get that is the equivalent of a clustered index, but I don't get how that relates to the original table. Do I have to insert from the original table into the IOT and then use it in place of the original table?

推荐答案

Oracle的概念指南

"...在索引组织的表中，行存储在定义的索引中 在表的主键上. B树中的每个索引条目也 存储非键列值.因此，索引是数据，而索引是 数据就是索引."

"... In an index-organized table, rows are stored in an index defined on the primary key for the table. Each index entry in the B-tree also stores the non-key column values. Thus, the index is the data, and the data is the index."

假设我们有您问题中所述的ORDER表.

Suppose we have an ORDER table as described in your question.

-- create table ORDER_, with test data
-- table name with trailing underscore avoids ORA-00903: invalid table name
create table order_
as
select 
  level * 10000 + trunc( dbms_random.value * 100 ) order_id
, trunc( dbms_random.value * 100000 )              part_id
, dbms_random.string( 'x', 10 )                    customer_id
, trunc( sysdate + level * 10 )                    order_date
from dual connect by level <= 10 ;

测试数据

SQL> select * from order_ ;
ORDER_ID  PART_ID  CUSTOMER_ID  ORDER_DATE  
10069     74711    KBGHAHWTL8   27-MAR-18   
20034     99571    7VUNFJER44   06-APR-18   
30038     64160    ORXP2RRA3K   16-APR-18   
40005     81247    B9N43NSVQ7   26-APR-18   
50019     90889    8H5G12D82E   06-MAY-18   
60017     34107    9O4OSETJ4H   16-MAY-18   
70078     53959    77MUCKJW82   26-MAY-18   
80015     9496     U5J6Z85KXR   05-JUN-18   
90081     88450    2LEUPZGFOS   15-JUN-18   
100031    38487    NX4BHBF3TN   25-JUN-18  

如果您现在仅创建一个IOT(索引组织表)，它将为空.

If you now just create an IOT (index organized table), it will be empty.

-- your original code
CREATE TABLE clust_order(
    order_id number,
    part_id number,
    CONSTRAINT part_pk PRIMARY KEY (part_id)
)ORGANIZATION INDEX;

Table CLUST_ORDER created.

SQL> select * from clust_order ;

no rows selected

您可以做的是:通过从原始表中进行选择来创建IOT(另请参见:并行组织索引组织的表创建

What you could do instead, is: create the IOT by SELECTing from the original table (see also: Parallelizing Index-Organized Table Creation here).

create table clust_order 
( 
  part_id constraint part_pk primary key
, order_id 
) 
organization index
parallel
as
select 
  part_id
, order_id 
from order_;

生成的物联网包含...

The resulting IOT contains ...

SQL> select * from clust_order;
PART_ID  ORDER_ID  
9496     80015     
34107    60017     
38487    100031    
53959    70078     
64160    30038     
74711    10069     
81247    40005     
88450    90081     
90889    50019     
99571    20034 

您可能会找到讨论有用.

这篇关于使用IOT在Oracle中创建聚簇索引?困惑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！