Oracle IOT模拟SQL Server复合聚集索引

Question

我希望我的Oracle行按照组合主键(WORK_DATE，EMP_ID)的升序进行排序。在SQL Server中，创建聚集索引可以轻松而神奇地解决问题。乍一看，Oracle的组织索引(IOT)似乎提供了一个可行的解决方案，但事实并非如此。

以下DDL说明了该问题。我已经使数据尽可能真实，特别是我有一个相当大的VARCHAR字段，该字段的大小因行而异(这可能会阻碍Oracle重新排序行的存储)。

我正在寻找一种解决方案，使行以查询#2输出的方式存储，而无需特别用户添加"ORDER BY 1，2"。我有一些笨拙的用户忘记了添加ORDER BY语句，他们将CTRL+END添加到底部，以为他们正在获取最新的WORK_DATE。我不想通过创建一个包含嵌入ORDERBY语句的视图来解决这个问题。你能建议对DDL语句进行更正吗？也许我需要在"组织索引"关键字后面添加额外的子句或参数？谢谢你的帮助。

CREATE TABLE EMPLOYEE_HOURS (
WORK_DATE       DATE          NOT NULL 
, EMP_ID          VARCHAR2(15)  NOT NULL
, HOURS_WORKED    NUMBER(22)    NOT NULL
, WORK_COMMENT    VARCHAR2(150) NOT NULL    
, ROW_INSERT_DATE TIMESTAMP DEFAULT SYSTIMESTAMP
, CONSTRAINT EMPLOYEE_HOURS_PK PRIMARY KEY (WORK_DATE, EMP_ID)
) ORGANIZATION INDEX;

/* create test data that mimics my real world data */
BEGIN
FOR loop_id IN 1 .. 10000
LOOP
INSERT INTO EMPLOYEE_HOURS VALUES (
TRUNC(SYSDATE) - TRUNC(dbms_random.value(-150, 150))
, UPPER(dbms_random.string('A', 3)) 
|| TRUNC(dbms_random.value(1000, 999999)) 
, dbms_random.value(0.5, 18.5)
, regexp_replace(SUBSTR(LOWER(dbms_random.string('A', 100))
, 1
, TRUNC(dbms_random.value(4, 100))), '(.....)', '1 ')
, SYSTIMESTAMP);
COMMIT WORK; 
END LOOP;
END;

/* compare these queries and notice that the sort order in the first query does not
conform to the expected order of the IOT composite index (WORK_DATE, EMP_ID) */
/* Query #1 */
SELECT * FROM EMPLOYEE_HOURS;
/* Query #2 */
SELECT * FROM EMPLOYEE_HOURS ORDER BY 1, 2;

Answer 1

IOT不能保证连续的行存储在连续的块中。它也不能保证按排序顺序检索行。

在oracle中，最接近控制行存储方式的是集群表。

现在，我认为您的解决方案中存在一些基本问题：

1. 您声称索引对于检索有序结果-这并不完全正确。索引扫描需要单个IO完全扫描时的访问可以在一个IO中获取多个块。因此使用索引检索具有10个块的表将导致10个IO而具有multi_block_read_count = 32、FTS和散列排序将需要更多的CPU，但只有一个IO。

2. 除了根据执行排序之外，没有任何方法可以保证排序结果。您可能应该在表的顶部声明一个视图，然后在那里执行排序。