Как сделать выбор случайных строк в оракуле быстрее с таблицей с миллионами строк
Есть ли способ сделать выбор случайных строк быстрее в оракуле с таблицей с миллионом строк. Я попытался использовать sample (x) и dbms_random.value и потратил на это много времени.
Спасибо!
Ответы
Ответ 1
Использование соответствующих значений sample(x) является самым быстрым способом. Он случайный и случайный случайный в блоках, поэтому, если вам нужна только одна случайная строка:
select dbms_rowid.rowid_relative_fno(rowid) as fileno,
dbms_rowid.rowid_block_number(rowid) as blockno,
dbms_rowid.rowid_row_number(rowid) as offset
from (select rowid from [my_big_table] sample (.01))
where rownum = 1
Я использую подсеризованную таблицу, и я получаю довольно хорошую случайность, даже хватая несколько строк:
select dbms_rowid.rowid_relative_fno(rowid) as fileno,
dbms_rowid.rowid_block_number(rowid) as blockno,
dbms_rowid.rowid_row_number(rowid) as offset
from (select rowid from [my_big_table] sample (.01))
where rownum <= 5
FILENO BLOCKNO OFFSET
---------- ---------- ----------
152 2454936 11
152 2463140 32
152 2335208 2
152 2429207 23
152 2746125 28
Я подозреваю, что вы, вероятно, должны настроить свое предложение SAMPLE, чтобы использовать соответствующий размер выборки для того, что вы получаете.
Ответ 2
Сначала начните с ответа Адама, но если SAMPLE просто недостаточно быстро, даже с оптимизацией ROWNUM, вы можете использовать образцы блоков:
....FROM [table] SAMPLE BLOCK (0.01)
Это относится к выборке на уровне блока, а не для каждой строки. Это означает, что он может пропускать большие таблицы данных из таблицы, поэтому процент проб будет очень грубым. Это не необычно для SAMPLE BLOCK с низким процентом для возврата нулевых строк.
Ответ 3
Здесь тот же вопрос в AskTom:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6075151195522
Если вы знаете, насколько велика ваша таблица, используйте блок примеров, как описано выше. Если вы этого не сделаете, вы можете изменить подпрограмму ниже, чтобы получить сколько угодно строк.
Скопировано из: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6075151195522#56174726207861
create or replace function get_random_rowid
( table_name varchar2
) return urowid
as
sql_v varchar2(100);
urowid_t dbms_sql.urowid_table;
cursor_v integer;
status_v integer;
rows_v integer;
begin
for exp_v in -6..2 loop
exit when (urowid_t.count > 0);
if (exp_v < 2) then
sql_v := 'select rowid from ' || table_name
|| ' sample block (' || power(10, exp_v) || ')';
else
sql_v := 'select rowid from ' || table_name;
end if;
cursor_v := dbms_sql.open_cursor;
dbms_sql.parse(cursor_v, sql_v, dbms_sql.native);
dbms_sql.define_array(cursor_v, 1, urowid_t, 100, 0);
status_v := dbms_sql.execute(cursor_v);
loop
rows_v := dbms_sql.fetch_rows(cursor_v);
dbms_sql.column_value(cursor_v, 1, urowid_t);
exit when rows_v != 100;
end loop;
dbms_sql.close_cursor(cursor_v);
end loop;
if (urowid_t.count > 0) then
return urowid_t(trunc(dbms_random.value(0, urowid_t.count)));
end if;
return null;
exception when others then
if (dbms_sql.is_open(cursor_v)) then
dbms_sql.close_cursor(cursor_v);
end if;
raise;
end;
/
show errors
Ответ 4
Ниже решение этого вопроса не является точным ответом, но во многих сценариях вы пытаетесь выбрать строку и попытаться использовать ее для какой-либо цели, а затем обновить ее статус с помощью "использованного" или "сделанного", чтобы вы не выбирали снова.
Решение:
Ниже запрос полезен, но, если ваша таблица большая, я просто попробовал и вижу, что вы определенно сталкиваетесь с проблемой производительности с этим запросом.
SELECT * FROM
(SELECT * FROM table
ORDER BY dbms_random.value)
WHERE rownum = 1
Итак, если вы установите rownum, как показано ниже, вы можете обойти проблему производительности. Увеличивая rownum, вы можете уменьшить возможности. Но в этом случае вы всегда будете получать строки из тех же 1000 строк. Если вы получите строку из 1000 и обновите свой статус с помощью "USED", вы получите почти каждую строку при каждом запросе с помощью "ACTIVE"
SELECT * FROM
( SELECT * FROM table
where rownum < 1000
and status = 'ACTIVE'
ORDER BY dbms_random.value )
WHERE rownum = 1
обновить статус строк после его выбора. Если вы не можете обновить это, значит, еще одна транзакция уже использовала его. Затем вы должны попытаться получить новую строку и обновить ее статус. Кстати, получение одной и той же строки по двум различным возможностям транзакции составляет 0,001, поскольку rownum - 1000.
Ответ 5
Кто-то сказал, что образец (x) - это самый быстрый способ.
Но для меня этот метод работает немного быстрее, чем метод sample (x).
Он должен взять часть второго (0,2 в моем случае) независимо от размера таблицы. Если требуется больше времени использовать подсказки (- + leading (e) use_nl (e t) rowid (t)), может помочь
SELECT *
FROM My_User.My_Table
WHERE ROWID = (SELECT MAX(t.ROWID) KEEP(DENSE_RANK FIRST ORDER BY dbms_random.value)
FROM (SELECT o.Data_Object_Id,
e.Relative_Fno,
e.Block_Id + TRUNC(Dbms_Random.Value(0, e.Blocks)) AS Block_Id
FROM Dba_Extents e
JOIN Dba_Objects o ON o.Owner = e.Owner AND o.Object_Type = e.Segment_Type AND o.Object_Name = e.Segment_Name
WHERE e.Segment_Name = 'MY_TABLE'
AND(e.Segment_Type, e.Owner, e.Extent_Id) =
(SELECT MAX(e.Segment_Type) AS Segment_Type,
MAX(e.Owner) AS Owner,
MAX(e.Extent_Id) KEEP(DENSE_RANK FIRST ORDER BY Dbms_Random.Value) AS Extent_Id
FROM Dba_Extents e
WHERE e.Segment_Name = 'MY_TABLE'
AND e.Owner = 'MY_USER'
AND e.Segment_Type = 'TABLE')) e
JOIN My_User.My_Table t
ON t.Rowid BETWEEN Dbms_Rowid.Rowid_Create(1, Data_Object_Id, Relative_Fno, Block_Id, 0)
AND Dbms_Rowid.Rowid_Create(1, Data_Object_Id, Relative_Fno, Block_Id, 32767))
Ответ 6
Версия с повторениями, когда строки не возвращаются:
WITH gen AS ((SELECT --+ inline leading(e) use_nl(e t) rowid(t)
MAX(t.ROWID) KEEP(DENSE_RANK FIRST ORDER BY dbms_random.value) Row_Id
FROM (SELECT o.Data_Object_Id,
e.Relative_Fno,
e.Block_Id + TRUNC(Dbms_Random.Value(0, e.Blocks)) AS Block_Id
FROM Dba_Extents e
JOIN Dba_Objects o ON o.Owner = e.Owner AND o.Object_Type = e.Segment_Type AND o.Object_Name = e.Segment_Name
WHERE e.Segment_Name = 'MY_TABLE'
AND(e.Segment_Type, e.Owner, e.Extent_Id) =
(SELECT MAX(e.Segment_Type) AS Segment_Type,
MAX(e.Owner) AS Owner,
MAX(e.Extent_Id) KEEP(DENSE_RANK FIRST ORDER BY Dbms_Random.Value) AS Extent_Id
FROM Dba_Extents e
WHERE e.Segment_Name = 'MY_TABLE'
AND e.Owner = 'MY_USER'
AND e.Segment_Type = 'TABLE')) e
JOIN MY_USER.MY_TABLE t ON t.ROWID BETWEEN Dbms_Rowid.Rowid_Create(1, Data_Object_Id, Relative_Fno, Block_Id, 0)
AND Dbms_Rowid.Rowid_Create(1, Data_Object_Id, Relative_Fno, Block_Id, 32767))),
Retries(Cnt, Row_Id) AS (SELECT 1, gen.Row_Id
FROM Dual
LEFT JOIN gen ON 1=1
UNION ALL
SELECT Cnt + 1, gen.Row_Id
FROM Retries
LEFT JOIN gen ON 1=1
WHERE Retries.Row_Id IS NULL AND Retries.Cnt < 10)
SELECT *
FROM MY_USER.MY_TABLE
WHERE ROWID = (SELECT Row_Id
FROM Retries
WHERE Row_Id IS NOT NULL)
Ответ 7
Можете ли вы использовать псевдослучайные строки?
select * from (
select * from ... where... order by ora_hash(rowid)
) where rownum<100
