Randomly Select 1000 records from MYTABLE

Very simple question: “Select 1000 random accounts from MYTABLE table”

This is very common question in analytical environments, yet most of the solutions I’ve seen are far from simple and random.

You can use DBMS_RANDOM or DBMS_CRYPTO packages – both deliver similar results although some might say DBMS_CRYPTO is “more” random. I’d say use DMBS_CRYPTO if you have the grant, if not use DBMS_RANDOM (really, you won’t see that much difference).

Query below is very simple and has 3 stages (3 subqueries) – first generate random number for each record, then sort the data by random number, after that select first 1000 records.

 

SELECT ACCOUNT_ID
 FROM ( SELECT ACCOUNT_ID,
               RANDOM_ORDER
          FROM ( SELECT ACCOUNT_ID,
                        DBMS_RANDOM.RANDOM RANDOM_ORDER
                        FROM MYTABLE )
      ORDER BY RANDOM_ORDER)
 WHERE ROWNUM < 1001      

 

It really cannot be easier than that.

custom in memory variables defined using SYS_CONTEXT in SQL and PL/SQL

This is interesting trick you probably won’t use that often, I used it few times in views in cases where I could not use parameter or because of aggregated complex views where I could not use WHERE clause. I did not find this anywhere on internet, I just had a moment of clarity.

Oracle Database has very useful function SYS_CONTEXT, which can be used as a source of all kinds of information about current session – from database username, OS username, IP address, … you name it. The primary use is for custom audit and security functions, but you can of course use it for whatever you wish.

for example this query returns client’s IP address

SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM DUAL;

What is really interesting on this function that it returns any parameter from any namespace (even custom namespace) and this is where the fun starts. You can create a parameter and use it in your queries and views, then by changing the value of this parameter you change the result returned by the query or view without modifying the query.

first let’s create some sample data

CREATE TABLE JIRI.ALL_USERS AS
SELECT USERNAME
  FROM SYS.DBA_USERS;

then let’s create a view with custom namespace and parameter

CREATE VIEW JIRI.SELECTED_USERS AS
SELECT COUNT(*) MY_COUNTER
  FROM JIRI.ALL_USERS
 WHERE USERNAME = SYS_CONTEXT ('MY_NAMESPACE', 'MY_PARAMETER');

Let me pause a bit before I assign custom value to my_parameter. As I mentioned above, Oracle created this for security purposes, usually custom application authentication thus assigning the value has one small jump. Basically you have to create stored procedure first, set the value in stored procedure and grant this procedure right to set the value. In another words you cannnot assign this parameter without being granted to.

 

CREATE OR REPLACE PROCEDURE SET_MY_PARAMETER (v_input_parameter varchar2) AS
BEGIN
  DBMS_SESSION.SET_CONTEXT ('MY_NAMESPACE', 'MY_PARAMETER', v_input_parameter);
END;

CREATE CONTEXT MY_NAMESPACE USING SET_MY_PARAMETER;

 

now let’s test it out

exec SET_MY_PARAMETER(v_input_parameter => 'JIRI');
select * from JIRI.SELECTED_USERS;

-- returns 1 

exec SET_MY_PARAMETER(v_input_parameter => 'DUMMY USERS WHICH DOES NOT EXIST');
select * from JIRI.SELECTED_USERS;

-- returns 0

 

As you can see it is very easy to create custom parameters and simply use then across multiple queries or views in one session in both PL/SQL and SQL.

INSERT ALL INTO SQL statement and normalization in Oracle

It is very surprising to me how much people don’t know about Oracle INSERT ALL INTO multiple tables statement and over complicate some of the inserts more than they should. Here is one interesting aggregate query and how it can be resolved using simple INSERT statement in one pass.

Let’s assume I have customer table and want to create transposed aggregate table.

this is my source table

CUSTOMER_ID	LOCATION_NAME	TOTAL_BALANCE	AVERAGE_BALANCE
234	Nevada	444.54	445.55
345	California	11.2	55.4
123	California	1000.5	800.2

CREATE TABLE JIRI.SOURCE_TABLE
(
  CUSTOMER_ID      NUMBER(10)                   NOT NULL,
  LOCATION_NAME    VARCHAR2(60 BYTE)            NOT NULL,
  TOTAL_BALANCE    NUMBER(10,2)                 NOT NULL,
  AVERAGE_BALANCE  NUMBER(10,2)                 NOT NULL
)

this is my summarized target table

LOCATION_NAME	MEASURE_NAME	MEASURE_VALUE
Nevada	TOTAL_BALANCE	444.54
Nevada	AVERAGE_BALANCE	445.55
California	TOTAL_BALANCE	1011.7
California	AVERAGE_BALANCE	855.6

CREATE TABLE JIRI.TARGET_TABLE
(
  LOCATION_NAME  VARCHAR2(60 BYTE)              NOT NULL,
  MEASURE_NAME   VARCHAR2(60 BYTE)              NOT NULL,
  MEASURE_VALUE  NUMBER(10,2)                   NOT NULL
)

Transformation from source to target is very simple SUM of individual measures with GROUP BY location_name. The only small tricky part is that we are normalizing this table by converting X columns to X rows. Let’s ignore the reasons for this normalization and let’s focus on the solution.