ROWNUM, Random updates and the Alert Log – A trip down Memory Lane

I met up with an old friend recently. Deb and I were in Toronto, hometown of a certain Simon Jennings.
Apart from being a top bloke, Simon was also my first mentor in the mysterious ways of SQL, a sort of Obi-Wan Kenobi to my Anakin Skywalker, but without the light-sabers.

Ah those heady days when the world was young…and I first discovered ROWNUM.
That pseudo column was one I used often when investigating the structure and data within tables. It was also, the source of some confusion.

Let’s take an example in keeping with Simon’s adopted home…

CREATE TABLE hockey_teams( 
    club_id NUMBER, 
    team_name VARCHAR2(50)) 
/ 

INSERT INTO hockey_teams( club_id, team_name) 
VALUES( 1, 'TORONTO MAPLE LEAFS') 
/ 

INSERT INTO hockey_teams( club_id, team_name) 
VALUES( 2, 'MONTREAL CANADIENS') 
/ 

INSERT INTO hockey_teams( club_id, team_name) 
VALUES(3, 'OTTOWA SENATORS') 
/ 

INSERT INTO hockey_teams( club_id, team_name) 
VALUES(4, 'WINNIPEG JETS') 
/ 

INSERT INTO hockey_teams( club_id, team_name) 
VALUES(5, 'CALGARY FLAMES') 
/ 

INSERT INTO hockey_teams( club_id, team_name) 
VALUES(6, 'EDMONTON OILERS') 
/ 

INSERT INTO hockey_teams( club_id, team_name) 
VALUES(7, 'VANCOUVER CANUCKS') 
/

If I just wanted to have a look at a few rows of the table ( remember, this was in the days before IDEs that return only the first few rows of a query) …

SELECT *
FROM hockey_teams
WHERE rownum <= 5
/

Now let's list them in reverse-alphabetical order …

SQL>  SELECT ROWNUM, team_name 
  2  FROM hockey_teams 
  3* ORDER BY team_name DESC 
SQL> / 

ROWNUM TEAM_NAME 
------ -------------------- 
     4 WINNIPEG JETS 
     7 VANCOUVER CANUCKS 
     1 TORONTO MAPLE LEAFS 
     3 OTTOWA SENATORS 
     2 MONTREAL CANADIENS 
     6 EDMONTON OILERS 
     5 CALGARY FLAMES 

7 rows selected. 

SQL> 

Here we can see the “catch” with relying on ROWNUM. It's applied to the result set of the query before the ORDER BY. If we want to associate ROWNUM to the result set in order we'll have to do something like :

SQL> SELECT ROWNUM, team_name 
  2  FROM ( 
  3	 SELECT team_name 
  4	 FROM hockey_teams 
  5*	 ORDER BY team_name DESC) 
SQL> / 

ROWNUM TEAM_NAME 
------ -------------------- 
     1 WINNIPEG JETS 
     2 VANCOUVER CANUCKS 
     3 TORONTO MAPLE LEAFS 
     4 OTTOWA SENATORS 
     5 MONTREAL CANADIENS 
     6 EDMONTON OILERS 
     7 CALGARY FLAMES 

7 rows selected. 

SQL> 

Since the days of Oracle 6, analytical functions have come on leaps and bounds. Let's face it, since those days, dinosaurs have become extinct and this new-fangled interweb thing has taken over the planet.
In the intervening years, ROWNUM has all but disappeared from my SQL vocabulary.
Recently however, I've come across a couple of instances where this venerable pseudo-column has come in handy.

Sort of Random

Let's imagine for a moment that I'm planning my next trip to Canada.
I want to catch some NHL action whilst I'm over there but can't decide which teams I want to see.
I want to pick two teams to see, more or less at random, but I don't want to drop into PL/SQL...

SQL> SELECT 
  2    CASE MOD( ROWNUM, 3) 
  3	 WHEN 0 THEN 'Going to see ' 
  4	 ELSE 'Not going to see ' 
  5    END 
  6    || team_name 
  7* FROM hockey_teams 
SQL> / 

CASEMOD(ROWNUM,3)WHEN0THEN'GOINGTOSEE'ELSE'NOTGOINGTOSEE'END||TEAM_NAME 
-------------------------------------------------------------------------------- 
Not going to see TORONTO MAPLE LEAFS 
Not going to see MONTREAL CANADIENS 
Going to see OTTOWA SENATORS 
Not going to see WINNIPEG JETS 
Not going to see CALGARY FLAMES 
Going to see EDMONTON OILERS 
Not going to see VANCOUVER CANUCKS 

7 rows selected. 

SQL> 

OK, so it's not that random. However, if we take another example, the value of ROWNUM becomes a bit more apparent.

Say I want to test some new functionality on the HR schema – one of the example schemas that ships with the Oracle database.
Here, I want to account for employees that are paid either weekly or monthly.
I've written some fancy Payroll routine to identify and process all of the appropriate employees at the appropriate time. This imaginary routine relies on a flag set on a new column in the employees table.
The value of the colum must be either 'W' for weekly or 'M' for monthly.
Most employees will be paid Monthly, but I do want some set to Weekly to make sure I test both possibilities. At this point, it doesn't really matter which records are assigned which value.

If you want to play along...

CREATE TABLE hr_employees AS SELECT * FROM hr.employees
/

ALTER TABLE hr_employees ADD ( pay_frequency VARCHAR2(1))
/

We should now have a table that looks like this :

SQL> desc hr_employees; 
 Name					   Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 EMPLOYEE_ID					    NUMBER(6) 
 FIRST_NAME					    VARCHAR2(20) 
 LAST_NAME				   NOT NULL VARCHAR2(25) 
 EMAIL					   NOT NULL VARCHAR2(25) 
 PHONE_NUMBER					    VARCHAR2(20) 
 HIRE_DATE				   NOT NULL DATE 
 JOB_ID 				   NOT NULL VARCHAR2(10) 
 SALARY 					    NUMBER(8,2) 
 COMMISSION_PCT 				    NUMBER(2,2) 
 MANAGER_ID					    NUMBER(6) 
 DEPARTMENT_ID					    NUMBER(4) 
 PAY_FREQUENCY					    VARCHAR2(1) 

SQL> 

The table contains 107 records. Now we want to set the PAY_FREQUENCY. We want to have around 10% of the rows to be set to 'W' and the rest to 'M'.
Now, we could mess around with PL/SQL and it's fancy cursor for loops...or we could just do this...

UPDATE hr_employees 
SET pay_frequency = 
    CASE MOD( ROWNUM, 10) 
        WHEN 0 THEN 'W' 
        ELSE 'M' 
    END 
/

Once we've run this, we can then see the effect...

SQL> SELECT pay_frequency, COUNT( employee_id) 
  2  FROM hr_employees 
  3  GROUP BY pay_frequency 
  4  / 

PAY COUNT(EMPLOYEE_ID) 
--- ------------------ 
W		    10 
M		    97 

SQL>

Alert Log as an External Table

For the other example of ROWNUMs continued usefulness, I'm going to use an external table. Yes, I know what you're thinking after my last post, when you've got a hammer, then everything looks like a nail.

Consider the DBA. First thing in the morning, the sugar rush from all that Maple Syrup has long since worn off and the caffeine from that first cup of coffee is still negotiating it's way from the mug to his or her brain.
Whilst in this state of chemical imbalance, our DBA needs to make sure that all is well after last night's batch runs.
Part of this morning ritual may well involve wading through the alert log.
Now wouldn't it make things just that little bit easier if we could get Oracle to do the thinking for us and just flag up any relevant alert log entries ?

Well, we know we can take a flat-file and treat it as an external table. Couldn't we do the same with the alert.log ?
Er, Yep.

CREATE OR REPLACE DIRECTORY bdump AS 
    '/usr/lib/oracle/xe/app/oracle/admin/XE/bdump' 
/

Now to create our external table :

CREATE TABLE alert_log_ext( 
	line VARCHAR2(4000)) 
	ORGANIZATION EXTERNAL 
	( 
		TYPE oracle_loader 
		DEFAULT DIRECTORY bdump 
		ACCESS PARAMETERS( 
			RECORDS DELIMITED BY NEWLINE 
			FIELDS TERMINATED BY '~' 
			MISSING FIELD VALUES ARE NULL 
			( 
				line CHAR(4000) 
			) 
		) 
			LOCATION('alert_XE.log') 
	) 
	REJECT LIMIT UNLIMITED 
/

And do a select...

SELECT * 
FROM alert_log_ext
/
…
LINE 
-------------------------------------------------------------------------------- 
Sun Sep 18 13:23:23 2011 
Completed: ALTER DATABASE OPEN 

39243 rows selected. 

Lovely, we have the alert log in the database....except there's no key so just querying the bit you want is still a bit fiddly. On the plus side, the rows seem to be read in in correct order by default ( i.e. the order in which they are read from the file) so the result set from the query will be in the correct order, even though no ORDER BY clause is specified.
NOTE – that last bit is supposition on my part. That's the way it appears to be working, but I haven't found anything in the Oracle documentation to confirm it.

But where does ROWNUM fit into all this, I hear you ask ? Well, if you want to query the alert log for a specific time window, you can do something like this :

SELECT entries.line_no, entries.line 
FROM( 
	SELECT rownum as line_no, line 
	FROM alert_log_ext 
	) entries, 
	( 
	SELECT MIN(line_no) as line_no 
	FROM ( SELECT rownum as line_no, line FROM alert_log_ext) 
	WHERE line LIKE TO_CHAR(SYSDATE -1, 'Dy Mon DD')||'%' 
	) yesterday 
WHERE entries.line_no >= yesterday.line_no 
/

That's better. We can now just return the rows of the alert log that have been written since the start of yesterday. However, we may well want to rely on something a bit more precise that just yesterday's date.

At this point, it's probably worth looking at the incredibly useful KSDWRT.
Gesundheit ! I hear you cry. Thanks, but no, I haven't just sneezed, I'm referring to DBMS_SYSTEM.KSDWRT.

Despite having a name that looks like you've just leaned on the keyboard, this procedure does have the incredibly useful ability to write a custom message to the alert log...

BEGIN 
DBMS_SYSTEM.KSDWRT(2, 'Oooh, a message'); 
END; 
/

Now, if we look in the alert log for this message...

SELECT line_no, line 
FROM( 
    SELECT ROWNUM as line_no, line 
    FROM alert_log_ext) 
WHERE line LIKE '%Oooh, a message' 
/ 

   LINE_NO LINE 
---------- ---------------------------------------- 
     38989 Oooh, a message 

With an appropriate message, we can narrow down our query to just the bit of the alert log that we're interested in.

If we return to our bleary-eyed DBA, we could help out by simply writing a message to the alert log at the start of the overnight batch window.

DECLARE 
    l_msg VARCHAR2(30); 
BEGIN 
    l_msg := 'Batch Window Start - '||TO_CHAR(SYSDATE, 'DDMMRR'); 
    DBMS_SYSTEM.KSDWRT(2, l_msg); 
END; 
/

To make sure I've now got something after the message in the alert log, I've bounced the database.
Now let's see what entries we have in the alert log after our message :

SELECT entries.line 
FROM( 
	SELECT ROWNUM as line_no, line 
	FROM alert_log_ext 
	) entries 
WHERE entries.line_no >= 
    (SELECT line_no 
     FROM ( 
        SELECT ROWNUM as line_no, line 
        FROM alert_log_ext) 
    WHERE line LIKE '%Batch Window Start - '||TO_CHAR(SYSDATE, 'DDMMRR')||'%') 
/

If the batch window starts before midnight, you'll want to amend the line :

WHERE line LIKE '%Batch Window Start - '||TO_CHAR(SYSDATE, 'DDMMRR')||'%')

to

WHERE line LIKE '%Batch Window Start - '||TO_CHAR(SYSDATE -1, 'DDMMRR')||'%')

Simon and I have both come a long way since those heady days. He turned into Ewan McGregor and I ...well...turned into a Dark Lord of the Sith apparently.

About these ads

2 thoughts on “ROWNUM, Random updates and the Alert Log – A trip down Memory Lane

  1. On the plus side, the rows seem to be read in in correct order by default ( i.e. the order in which they are read from the file) so the result set from the query will be in the correct order, even though no ORDER BY clause is specified.

    NOTE – that last bit is supposition on my part. That’s the way it appears to be working, but I haven’t found anything in the Oracle documentation to confirm it.

    You might consider what happens with parallel access.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s