Sample Oracle SQL Randomly - in absense of ROWID

Question

I have a weird problem in using SAMPLE clause. Why does the First SQL does not work, while the second one works fine.

SELECT * FROM SYS.ALL_TABLES SAMPLE(10)   

SELECT * FROM MIDAS.GERMPLASM SAMPLE(10)

I'm trying to SAMPLE a SQL query not just a table, but I could not figure out how I should use the SAMPLE clause. Is there any other way besides sample clause? Note: I want to do this in a random manner; not the first N rows.

Update:

First of all, thank you for reading this Q to help. But I already know that this SQL does not work because the SAMPLE clause is using a hidden column, ROWID. What I don't know is how to do this if ROWID does not exist in the table.

Here is a reproducible example SQL that I try to SAMPLE randomly:

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner, cons.constraint_type
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position

I want to get small random subset of data (from query), to compute statistical properties of table columns before fetching everything from DB.

Thank you

It does not work! I think of two potential problem: 1. There is no primary key to this table; 2. SAMPLE clause using hidden columns like ROWID to sample! — Mahdi Jadaliha
Instead of sampling views, try sampling the tables upon which the views are based. Best of luck. — Bob Jarvis - Reinstate Monica

Allan Allan · Accepted Answer · 2014-12-04T22:00:27

The error message you get when you try to run the first query is a pretty big clue:

ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.

It's pretty clear to me from this that the SAMPLE functionality requires access to ROWID to work. As ROWID is a pseudocolumn that the database uses to physically locate a row, any query where the ROWID is indeterminate (such as when the data is aggregated), cannot use SAMPLE on the outer query. In the case of ALL_ALL_TABLES, the fact that it is a view that combines two tables via UNION blocks access to the ROWID.

From your revised question, the first thing that jumps out at me is that the SAMPLE clause must be in the FROM clause, between the table name and any alias. I was able to sample in a query with joins like this:

SELECT *
FROM   table_a SAMPLE (10) a
       JOIN table_b SAMPLE (10) b 
       ON a.column1 = b.column1

Regarding your actual query, I tried using the tables (again, actually views) that you're trying to sample one at a time:

select * from all_constraints sample(10)

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

select * from all_cons_columns sample(10)

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

This message is pretty clear: none of the tables in these views are key-preserved (i.e. guaranteed to return each row no more than once), so you can't sample them.

The following query should work to manually create a random sample, using DBMS_RANDOM.

SELECT   *
FROM     (SELECT cols.table_name,
                 cols.column_name,
                 cols.position,
                 cons.status,
                 cons.owner,
                 cons.constraint_type,
                 DBMS_RANDOM.VALUE rnd
          FROM   all_constraints cons
                 JOIN all_cons_columns cols
                    ON     cons.constraint_name = cols.constraint_name
                       AND cons.owner = cols.owner)
WHERE    rnd < .1
ORDER BY table_name, position

Sample Oracle SQL Randomly - in absense of ROWID

1 Answers