수백만 개의 행이 있는 테이블을 사용하여 Oracle에서 임의 행을 더 빠르게 선택할 수 있는 방법은 무엇입니까?

수백만 개의 행이 있는 테이블을 사용하여 Oracle에서 임의 행을 더 빠르게 선택할 수 있는 방법은 무엇입니까?

수백만 개의 행이 있는 테이블을 사용하여 Oracle에서 랜덤 행을 더 빠르게 선택할 수 있는 방법이 있습니까?sample(x)와 dbms_random.value를 사용하려고 했는데 실행하는 데 시간이 오래 걸립니다.

적절한 값 사용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가져올 항목에 적합한 샘플 크기를 사용하는 절입니다.

먼저 아담의 대답부터 시작하지만, 만약에.SAMPLEROWNUM 최적화를 사용하더라도 블록 샘플을 사용할 수 있습니다.

....FROM [table] SAMPLE BLOCK (0.01)

이렇게 하면 각 행 대신 블럭 수준에서 샘플링이 적용됩니다.즉, 테이블에서 많은 양의 데이터를 건너뛸 수 있으므로 표본 백분율이 매우 거칠어집니다.백분율이 낮은 SAMPLE BLOCK이 행을 0개 반환하는 것은 드문 일이 아닙니다.

다음은 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

이 질문에 대한 아래 솔루션은 정확한 답이 아니지만 많은 시나리오에서 행을 선택하고 특정 용도로 사용하려고 시도한 다음 다시 선택하지 않도록 "사용됨" 또는 "완료"로 상태를 업데이트합니다.

솔루션:

아래 쿼리는 유용하지만 테이블이 크면 이 쿼리로 인해 성능 문제가 발생할 수 있습니다.

SELECT * FROM ( SELECT * FROM table Order by dbms_dbms.value) 여기서 rownum = 1

따라서 아래와 같이 행을 설정하면 성능 문제를 해결할 수 있습니다.로우넘을 증가시키면 가능성을 줄일 수 있습니다.그러나 이 경우에는 항상 동일한 1000개 행의 행을 얻을 수 있습니다.1000에서 행을 얻고 "USED"로 상태를 업데이트하면 "ACTIVE"로 쿼리할 때마다 거의 다른 행이 표시됩니다.

SELECT * FROM
( SELECT * FROM table
where rownum < 1000
  and status = 'ACTIVE'
  ORDER BY dbms_random.value  )
WHERE rownum = 1

선택한 후 행 상태를 업데이트합니다. 업데이트할 수 없는 경우 다른 트랜잭션에서 이미 사용하고 있습니다.그러면 새 행을 가져와서 상태를 업데이트해야 합니다.그런데 같은 행을 두 개의 다른 거래 가능성으로 얻는 것은 rownum이 1000이기 때문에 0.001입니다.

누군가가 샘플(x)이 당신이 할 수 있는 가장 빠른 방법이라고 말했습니다.하지만 저는 이 방법이 샘플(x) 방법보다 조금 더 빨리 작동합니다.표의 크기에 관계없이 두 번째 값(나의 경우 0.2)의 일부를 사용해야 합니다.힌트(--+ 선행(e) use_nl(et) 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))

반환된 행이 없을 때 재시도하는 버전:

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)

의사 임의 행을 사용할 수 있습니까?

select * from (
  select * from ... where... order by ora_hash(rowid)
) where rownum<100

언급URL : https://stackoverflow.com/questions/3150550/how-to-make-selecting-random-rows-in-oracle-faster-with-table-with-millions-of-r