Использовать Oracle без изменений VARRAY вместо оператора IN
Предположим, что у пользователей есть учетные записи 1 - n в системе. Когда они запрашивают базу данных, они могут выбрать выбор из m acounts, m between 1 and n. Обычно SQL, сгенерированный для извлечения их данных, похож на
SELECT ... FROM ... WHERE account_id IN (?, ?, ..., ?)
Таким образом, в зависимости от количества учетных записей, которые пользователь имеет, это вызовет новый жесткий анализ в Oracle, а также новый план выполнения и т.д. Теперь есть много таких запросов, и, следовательно, разборки, и, возможно, кеш курсора/плана будет заполнен довольно рано, что приведет к еще большему анализу.
Вместо этого я мог бы написать что-то вроде этого
-- use any of these
CREATE TYPE numbers AS VARRAY(1000) of NUMBER(38);
CREATE TYPE numbers AS TABLE OF NUMBER(38);
SELECT ... FROM ... WHERE account_id IN (
SELECT column_value FROM TABLE(?)
)
-- or
SELECT ... FROM ... JOIN (
SELECT column_value FROM TABLE(?)
) ON column_value = account_id
И используйте JDBC для привязки java.sql.Array (т.е. an oracle.sql.ARRAY) к единственной переменной привязки. Понятно, что это приведет к менее жестким параметрам и меньшим курсорам в кеше для функционально эквивалентных запросов. Но есть ли что-то вроде общего недостатка производительности или любых других проблем, с которыми я мог бы столкнуться?
Например: обрабатывает ли переменная peeking аналогичным образом для varrays или вложенных таблиц? Поскольку количество данных, связанных с каждой учетной записью, может сильно различаться.
Я использую Oracle 11g в этом случае, но я думаю, что этот вопрос интересен для любой версии Oracle.
Ответы
Ответ 1
Я предлагаю вам попробовать обычное старое соединение, например, в
SELECT Col1, Col2
FROM ACCOUNTS ACCT
TABLE TAB,
WHERE ACCT.User = :ParamUser
AND TAB.account_id = ACCT.account_id;
Альтернативой может быть подзапрос таблицы
SELECT Col1, Col2
FROM (
SELECT account_id
FROM ACCOUNTS
WHERE User = :ParamUser
) ACCT,
TABLE TAB
WHERE TAB.account_id = ACCT.account_id;
или где подзапрос
SELECT Col1, Col2
FROM TABLE TAB
WHERE TAB.account_id IN
(
SELECT account_id
FROM ACCOUNTS
WHERE User = :ParamUser
);
Первый должен быть лучше для исполнения, но лучше проверить их все с помощью плана объяснения.
Ответ 2
Глядя на V $SQL_BIND_CAPTURE в базе 10g, у меня есть несколько строк, где тип данных VARRAY или NESTED_TABLE; фактические значения привязки не были зафиксированы. В базе данных 11g имеется только одна такая строка, но она также показывает, что значение привязки не фиксируется. Поэтому я подозреваю, что значение привязки значения, по-видимому, не выполняется для пользовательских типов.
По моему опыту, основная проблема, с которой вы сталкиваетесь с использованием вложенных таблиц или varrays таким образом, заключается в том, что оптимизатор не имеет достаточной оценки мощности, что может привести к созданию плохих планов. Но есть (недокументированный?) подсказка CARDINALITY, которая может быть полезна. Проблема заключается в том, что если вы подсчитаете фактическую мощность вложенной таблицы и включите ее в запрос, вы вернетесь к множеству различных текстов запросов. Возможно, если вы ожидаете, что большинство или всех пользователей будут иметь не более 10 учетных записей, используя подсказку, чтобы указать, что, поскольку мощность будет полезна. Конечно, я бы попробовал это без подсказки сначала, у вас может не быть проблемы здесь вообще.
(Я также думаю, что, возможно, ответ Мигеля - это правильный путь.)
Ответ 3
Это связывание переменной количества элементов в проблему с списком, похоже, очень много в различной форме. Один из вариантов - объединить идентификаторы в строку, разделенную запятой, и связать ее, а затем использовать немного трюка, чтобы разбить его на таблицу, к которой вы можете присоединиться, например:
with bound_inlist
as
(
select
substr(txt,
instr (txt, ',', 1, level ) + 1,
instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
as token
from (select ','||:txt||',' txt from dual)
connect by level <= length(:txt)-length(replace(:txt,',',''))+1
)
select *
from bound_inlist a, actual_table b
where a.token = b.token
Приоритет переменной переплета будет проблемой.
Действительно ли план запроса изменяется для большего количества учетных записей, т.е. было бы более эффективным переходить от индекса к полному сканированию таблицы в некоторых случаях, или это граница? Как и кто-то другой, вы можете использовать подсказку CARDINALITY, чтобы указать, сколько идентификаторов привязано, следующий тестовый пример доказывает, что это действительно работает:
create table actual_table (id integer, padding varchar2(100));
create unique index actual_table_idx on actual_table(id);
insert into actual_table
select level, 'this is just some padding for '||level
from dual connect by level <= 1000;
explain plan for
with bound_inlist
as
(
select /*+ CARDINALITY(10) */
substr(txt,
instr (txt, ',', 1, level ) + 1,
instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
as token
from (select ','||:txt||',' txt from dual)
connect by level <= length(:txt)-length(replace(:txt,',',''))+1
)
select *
from bound_inlist a, actual_table b
where a.token = b.id;
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 840 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 10 | 840 | 2 (0)| 00:00:01 |
| 3 | VIEW | | 10 | 190 | 2 (0)| 00:00:01 |
|* 4 | CONNECT BY WITHOUT FILTERING| | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | ACTUAL_TABLE_IDX | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | ACTUAL_TABLE | 1 | 65 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Ответ 4
Другая опция - всегда использовать n связывать переменные в каждом запросе. Используйте null для m+1 до n.
Oracle игнорирует повторяющиеся элементы в expression_list. Ваши запросы будут выполняться одинаково, и будет меньше жестких разбор. Но для привязки всех переменных и переноса данных будут дополнительные накладные расходы. К сожалению, я понятия не имею, каково общее влияние на производительность, вам придется ее протестировать.
