Oracle Get Checksum Value for a Data Chunk Defined by a Select Clause

Oracle get checksum value for a data chunk defined by a select clause

You can use DBMS_SQLHASH.GETHASH for this. The query results must be ordered and must not contain any LOBs, or the results won't be deterministic.

select dbms_sqlhash.gethash(q'[select * from some_table order by 1,2]', digest_type => 1)
from dual;

Where digest_type 1 = HASH_MD4, 2 = HASH_MD5, 3 = HASH_SH1.

That package is not granted to anyone by default. To use it, you'll need someone to logon as SYS and run this:

SQL> grant execute on dbms_sqlhash to <your_user>;

---

The query results must be ordered, as described in "Bug 17082212 : DBMS_SQLHASH DIFFERENT RESULTS FROM DIFFERENT ACCESS PATH".

I'm not sure why LOBs don't work, but it might be related to the way the function ORA_HASH does not work well with LOBs. This Jonathan Lewis article includes some examples of ORA_HASH returning different results for the same LOB data. And recent versions of the SQL Language Reference warn that ORA_HASH does not support LOBs.

How to partially compare data between two remote tables in Oracle to avoid ORA-01652

You can split data into pieces and compare it piece by piece. But it will not be one query. Something like this:

declare
  diff number := 0;
  subdiff number;
  piece_size number := 1000;
  table_size number;
begin
  select count(*) 
    into table_size
    from table;

  for i in (select rownum r from dual connect by level <= ceil(table_size/piece_size)) loop
     select count(*)
       into subdiff
       from (select colmn1, ..., column10 from table
              where id between (i.r - 1) * piece_size and i.r * piece_size
              minus
             select colmn1, ..., column10 from remote_table@db_link
              where id between (i.r - 1) * piece_size and i.r * piece_size);
     diff := diff + subdiff;
  end loop;
  dbms_output.put_line('Total lines: ' + diff);
end;

Here you calculate count of rows in the local table, then split it into pieces with 1000 rows (variable piece_size), and compare table piece by piece, collecting total amount of different lines in the diff variable. Then you will see total amount of lines after the loop.

It could take a lot of time, so first you need to find maximal size of piece, which doesn't raise the error. It depends on your systems and could be a 100 000 rows, 1 000 000 rows or any other size.

If you need to see rows themselves, not only the amount, you can copy them into a temporary table in the same manner.

How to select the nth row in a SQL database table?

There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.

A really good site that talks about this and other things is http://troels.arvin.dk/db/rdbms/#select-limit.

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

(which I just copied from the site linked above since I never use those DBs)

Update: As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.

Update: SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.

How to request a random row in SQL?

See this post: SQL to Select a random row from a database table. It goes through methods for doing this in MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 and Oracle (the following is copied from that link):

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

---

Related Topics