Oracle IOT для имитации составного кластерного индекса SQL Server

Я хотел бы, чтобы мои строки Oracle были отсортированы в точном порядке возрастания моего составного первичного ключа (WORK_DATE, EMP_ID). В SQL Server создание кластеризованного индекса легко и волшебным образом решает проблему. На первый взгляд кажется, что Oracle ORGANIZATION INDEX (или IOT) предлагает работоспособное решение, но это не так.

Следующий DDL иллюстрирует проблему. Я сделал данные как можно более реалистичными, особенно тот факт, что у меня есть значительное поле VARCHAR, размер которого варьируется от строки к строке (что, по-видимому, препятствует Oracle переупорядочивать хранение строк).

Я ищу решение, которое заставляет строки храниться так же, как и в выводе запроса № 2, без специальных пользователей, которые должны добавлять «ORDER BY 1, 2». У меня есть неуклюжие пользователи, которые забывают добавить инструкцию ORDER BY и CTRL+END в конец, думая, что берут самую последнюю WORK_DATE. Я не хочу решать эту проблему, создавая представление, содержащее встроенный оператор ORDER BY. Можете ли вы предложить исправление к оператору 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;

oracle clustered-index composite-primary-key ddl tablespace
person user3171851    schedule 08.01.2014    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Интернет вещей не гарантирует, что последовательные строки будут храниться в последовательных блоках. Это также не гарантирует, что строки будут получены в отсортированном порядке.

Самое близкое, что вы можете получить в оракуле для управления хранением строк, — это кластеризованная таблица.

Теперь, я думаю, у вас есть некоторые фундаментальные проблемы с вашим решением:

  1. Вы утверждаете, что индекс будет оптимален для получения упорядоченных результатов. Это не совсем правильно. Сканирование индекса требует доступа к одному вводу-выводу, в то время как полное сканирование может получить несколько блоков за один ввод-вывод. Таким образом, извлечение таблицы с 10 блоками с использованием индекса приведет к 10 операциям ввода-вывода, в то время как тот же запрос с multi_block_read_count = 32, FTS и хеш-сортировкой потребует немного больше ресурсов ЦП, но только одного ввода-вывода. Подумай об этом.

  2. Не существует никакого способа гарантировать отсортированные результаты, кроме выполнения заказа. Вероятно, вам следует объявить представление поверх вашей таблицы и выполнить там сортировку.

person haki    schedule 08.01.2014