Oracle IOT to mimic SQL Server composite clustered index

Question

I would like my Oracle rows to be sorted in the exact ascending order of my composite primary key (WORK_DATE, EMP_ID). In SQL Server, creating a clustered index easily and magically solves the problem. At first glance, Oracle's ORGANIZATION INDEX (or IOT) seemed to offer a workable solution, but it does not.

The following DDL illustrates the problem. I have made the data as realistic as possible, particularly the fact that I have a sizable VARCHAR field that varies in size from row-to-row (which presumably discourages Oracle from reordering the storage of the rows).

I am seeking a solution that causes rows to be stored in the manner as that of Query #2's output without ad-hoc users having to add an "ORDER BY 1, 2". I have clumsy users that forget to add the ORDER BY statement and they CTRL+END to the bottom, thinking they are grabbing the most recent WORK_DATE. I do not want to solve this by creating a view that contains an embedded ORDER BY statement. Can you suggest a correction to the DDL statement? Perhaps I need additional clauses or parameters after the "ORGANIZATION INDEX" keywords? Thanks for you assistance.

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 does not guaranty that consecutive rows are stored in consecutive blocks. It also not guarantying that rows will be retrieved in a sorted order.

The closest you can get in oracle to controlling how rows are stored is a clustered table.

Now, I think you have some fundamental issues with you solution :

1. You claim that an index will be optimal for retrieving ordered results - That's not entirely correct. Index scans require single IO access while full scan can fetch multiple blocks in one IO. So retriving a table with 10 blocks using the index will result in 10 IO while the same query with multi_block_read_count = 32, an FTS and a hash sort will require a bit more CPU but only one IO. Think about that.

2. There is no way of guarantying sorted results other then performing a order by. You should probably declare a view on top of your table and perform the sort there.