Как я могу ускорить запросы против огромных таблиц хранилища данных с эффективный датированными данными?

Один из подходов - использовать PL / SQL-функции во всем. В качестве примера

create table remote (user_id number, eff_date date, eff_seq number, value varchar2(10));

create type typ_remote as object (user_id number, eff_date date, eff_seq number, value varchar2(10));
.
/

create type typ_tab_remote as table of typ_remote;
.
/

insert into remote values (1, date '2010-01-02', 1, 'a');
insert into remote values (1, date '2010-01-02', 2, 'b');
insert into remote values (1, date '2010-01-02', 3, 'c');
insert into remote values (1, date '2010-01-03', 1, 'd');
insert into remote values (1, date '2010-01-03', 2, 'e');
insert into remote values (1, date '2010-01-03', 3, 'f');

insert into remote values (2, date '2010-01-02', 1, 'a');
insert into remote values (2, date '2010-01-02', 2, 'b');
insert into remote values (2, date '2010-01-03', 1, 'd');

create function show_remote (i_user_id_1 in number, i_user_id_2 in number) return typ_tab_remote pipelined is
    CURSOR c_1 is
    SELECT user_id, eff_date, eff_seq, value
    FROM
        (select user_id, eff_date, eff_seq, value, 
                        rank() over (partition by user_id order by eff_date desc, eff_seq desc) rnk
        from remote
        where user_id in (i_user_id_1,i_user_id_2))
    WHERE rnk = 1;
begin
    for c_rec in c_1 loop
        pipe row (typ_remote(c_rec.user_id, c_rec.eff_date, c_rec.eff_seq, c_rec.value));
    end loop;
    return;
end;
/

select * from table(show_remote(1,null));

select * from table(show_remote(1,2));

Вместо того, чтобы передавать user_id индивидуально в качестве параметров, вы можете загрузить их в локальную таблицу (например, глобальную временную таблицу). Затем PL / SQL будет перебирать таблицу, выполняя удаленный выбор для каждой строки в локальной таблице. Ни один запрос не может иметь как локальные, так и удаленные таблицы. Фактически вы бы написали свой собственный код соединения.

Один из вариантов - сначала материализовать удаленную часть запроса с использованием общего табличного выражения, чтобы вы могли быть уверены, что из удаленной базы данных извлекаются только релевантные данные. Другим улучшением было бы объединение двух подзапросов против удаленной базы данных в один подзапрос на основе аналитических функций. Такой запрос также может использоваться в вашем текущем запросе. Другие предложения я могу сделать только после того, как поиграю с db.

см. Ниже

with remote_query as
(
    select /*+ materialize */  st.* from sometable@remotedb st
    where st.user_id in ('123', '456', '789')
    and st.rowid in( select first_value(rowid) over (order by effective_date desc, 
                         effective_sequence desc ) from sometable@remotedb st1 
                      where st.user_id=st1.user_id)
)

select lt.*,st.* 
FROM local_table st,remote_query rt
where st.user_id=rt.user_id

Вы не упомянули требования к актуальности данных, но одним из вариантов было бы создание материализованных представлений (вы будете ограничены REFRESH COMPLETE, поскольку вы не можете создавать журналы моментальных снимков) в исходной системе), которые содержат данные только для текущей версированной строки таблиц транзакций. Эти материализованные таблицы представлений будут находиться в вашей локальной системе, и к ним можно добавить дополнительную индексацию для повышения производительности запросов.

Проблема с производительностью будет заключаться в доступе по ссылке. Что касается части запроса к локальным таблицам, все это выполняется локально, поэтому нет доступа к удаленным индексам, и он извлекает все удаленные данные обратно для тестирования lkocally.

Если бы вы могли использовать материализованные представления в локальной базе данных, обновляемые из базы данных peoplesoft на периодической (еженощной) основе для исторических данных, обращаясь к удаленной базе данных peoplesoft только для сегодняшних изменений (добавление Effective_date = today к вашему предложению where) и объединение двух запросов.

Другой вариант может заключаться в использовании INSERT INTO X SELECT FROM только для удаленных данных, чтобы вывести их во временную локальную таблицу или материализованное представление, а затем второй запрос, чтобы объединить их с вашими локальными данными ... аналогично предложению josephj1989

В качестве альтернативы (хотя могут быть проблемы с лицензированием) попробуйте RAC кластеризовать локальную базу данных с удаленной базой данных peoplesoft.

Вместо использования подзапросов вы можете попробовать это. Я не знаю, будет ли Oracle работать с этим лучше или нет, так как я мало использую Oracle.

SELECT
    ST1.col1,
    ST1.col2,
    ...
FROM
    Some_Table ST1
LEFT OUTER JOIN Some_Table ST2 ON
    ST2.user_id = ST1.user_id AND
    (
        ST2.effective_date > ST1.effective_date OR
        (
            ST2.effective_date = ST1.effective_date AND
            ST2.effective_sequence > ST1.effective_sequence
        )
    )
WHERE
    ST2.user_id IS NULL

Другое возможное решение:

SELECT
    ST1.col1,
    ST1.col2,
    ...
FROM
    Some_Table ST1
WHERE
    NOT EXISTS
    (
        SELECT
        FROM
            Some_Table ST2
        WHERE
            ST2.user_id = ST1.user_id AND
            (
                ST2.effective_date > ST1.effective_date OR
                (
                    ST2.effective_date = ST1.effective_date AND
                    ST2.effective_sequence > ST1.effective_sequence
                )
            )
    )

Будет ли вариантом создать базу данных, которую вы используете для нестандартных типов, которые вы могли бы обновлять на ночной основе? Если это так, вы можете создать ночной процесс, который будет перемещаться только по самым последним записям.Это избавит от MAX-вещей, которые вы делаете для повседневных запросов, и значительно уменьшит количество или записи.

Кроме того, зависит от того, можете ли вы иметь 1-дневный промежуток между самыми последними данными и тем, что доступно.

Я не очень хорошо знаком с Oracle, поэтому может быть способ получить улучшения, внеся изменения в ваш запрос...

Можете ли вы выполнить ETL строк с желаемыми user_id в вашей собственной таблице, создав только необходимые индексы для поддержки ваших запросов и выполнения ваших запросов по ней?

Является ли таблица PeopleSoft поставляемой или пользовательской? Вы уверены, что это физическая таблица, а не плохо написанное представление на стороне PS? Если речь идет о поставляемой записи (пример выглядит как PS_JOB или представление, которое на нее ссылается), то, возможно, вы могли бы указать это. PS_JOB - это чудовище с тоннами поставленных индексов, а большинство сайтов добавляют еще больше.

Если вы знаете индексы таблицы, вы можете использовать подсказки Oracle, чтобы указать предпочтительный индекс для использования; иногда это помогает.

Делали ли вы план объяснения, чтобы посмотреть, можете ли вы определить, где проблема? Может быть, там есть cartesian join, полное сканирование таблицы и т.д.?

Мне кажется, вы имеете дело с измерением типа 2 в хранилище данных. Существует несколько способов реализации измерения типа 2, в основном с такими столбцами, как ValidFrom, ValidTo, Version, Status . Не все они всегда присутствуют, было бы интересно, если бы вы могли опубликовать схему для своей таблицы. Вот пример того, как это может выглядеть (Джон Смит переехал из Индианы в Огайо 24.06.2010)

UserKey  UserBusinessKey State    ValidFrom    ValidTo   Version  Status
7234     John_Smith_17   Indiana  2005-03-20  2010-06-23    1     expired
9116     John_Smith_17   Ohio     2010-06-24  3000-01-01    2     current

Чтобы получить последнюю версию строки, обычно используют

WHERE Status = 'current'

или

WHERE ValidTo = '3000-01-01'

Примечание. что у этого есть некоторая константа в далеком будущем.

или

WHERE ValidTo > CURRENT_DATE

Похоже, что в вашем примере используется ValidFrom (эффективная_дата), поэтому вы вынуждены искать max () , чтобы найти последнюю строку. Взгляните на схему - есть ли в ваших таблицах эквиваленты Status или ValidTo ?