I’m looking at the SQL Scripts that create and modify the database for VPXD. Specifically, I am looking at the oracle version stats[123] scripts. They included a query that uses the “OVER” keyword and I have not found a decent description of what this keyword does. This is the query:
SELECT
st.counter_id AS COUNTER_ID
,v_time_rowid AS TIME_ID
,ST.STAT_VAL AS STAT_VAL
,DENSE_RANK()
OVER (PARTITION BY ST.COUNTER_ID ORDER BY SM.SAMPLE_TIME DESC) AS DENSE_RANK
FROM vpx_sample_time1 sm,vpx_stat_counter sc, vpx_stat_def sd, vpx_hist_stat1 st
WHERE sm.rollup_counter=to_number(to_char(v_start_time_id)||to_char(v_cnt))
AND st.time_id = sm.time_id
AND st.counter_id=SC.COUNTER_ID
AND SC.STAT_ID=SD.ID
AND ((SC.DEVICE_ID =v_null_device_id and v_stat_rollup_level<3) or (v_stat_rollup_level>=3))
AND SD.STAT_LEVEL<=v_stat_rollup_level
AND SD.ROLLUP_TYPE=3)
WHERE DENSE_RANK=1
;
The one description of the Over command I have found has come from the Microsoft website:
“Determines the partitioning and ordering of the rowset before the associated window function is applied”
OK, so it is a union of two types of functionality: partitioning and ordering. The queries I am looking at use both. Let’s start with partitioning:
PARTITION BYDivides the result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
The window function in this place must somehow refer to DENSE_RANK. To me this looks like it is possibly trying to insert a subselect into the overall select. Here is how MSDN defines DENSE_RANK. I’d like to reiterate that these are Oracle functions I’m trying to understand, and Microsoft is doing all the work explaining them. When I tried executing this SQL Query in SQLPLUS via the Oracle 11 instant client, it balked:
SQL> 2 3 4 5 6 SQL> SP2-0734: unknown command beginning “OVER (PART…” – rest of line ignored.
SQL> SP2-0734: unknown command beginning “FROM vpx_…” – rest of line ignored. It parses fine when I do it as part of a plsql stored procedure.
Time to dig out the slightly larger guns: I picked up a copy of the SQL Cookbook by Anthony Moliaro from Our Friends in Sebastopol, CA. This covered both DENSE_RANK and the OVER keyword.
Over, it turns out, is one of a new set of functions added to SQL 2003 that the Standard calls Windowing functions. This means they operate on a subset of the results returned from the outside query. The best example I saw showed if you are selecting all employees from all departments, and next to each one you wanted to show how many employees were in that department, you could use count(*) with an over clause that specified PARTITION by department. This example is in the Cookbook.
DENSE_RANK () tells you how many rows come before the current row in some ordering scheme. The over clause is used to define that ordering scheme. If two rows have the same rank, they have equivalent values according to DENSE_RANK. For instance, if we have five people with five salaries, their DENSE_RANK of salaies will be 1 through five, highest to lowest. If the top two salaries were identical, top rank would return 1,1,2,3,4.
OK, so it looks like the original query wants to use DENSE_RANK on a subset of the returned rows of the query. The window will be defined by this portion of the clause: PARTITION BY ST.COUNTER_ID
And the order within this window will be determined by: ORDER BY SM.SAMPLE_TIME DESC
OK time to get serious about playing:
I’ve learned a little but since the above frustrations trying to run the stored procedure. Here’s what I do now:
run sqlplus. For me this is
/home/adyoung/apps/oracle/instantclient_11_1/sqlplus adyoung/adyoung@10.17.195.159/DRORACLE
Now make it so anything I print to the screen is visible:
set serveroutput on
Load my sql file in and execute it.
get myfile.sql
/
OK, I have a development method. To print debugging output:
DBMS_OUTPUT.PUT_LINE(‘Text Here’);
And we can debug. In the time honored tradition of always moving from success, I comment everything out but a simple output statement. In this case, I know that the real code uses a variable for the rollup level, so my File (minus commented out section, looks like this:
create or replace procedure stattest
is
v_stat_rollup_level int;
begin
v_stat_rollup_level := 2;
dbms_output.put_line(‘Value: ‘ || TO_CHAR(v_stat_rollup_level));
end stattest;
Which executes using the above method. Now I start adding in parts of the SELECT.
SELECT st.counter_id AS COUNTER_ID FROM vpx_hist_stat1 st;
Of course, it hates this. When I load it into sqlplus I get:
Warning: Procedure created with compilation errors.
Of course, in order to see these compilation errors, I have to jump through a couple more hoops:
SQL> set arraysize 1
SQL> set MAXDATA 6000
SQL> show errors procedure stattest
LINE/COL ERROR
——– —————————————————————–
6/1 PLS-00428: an INTO clause is expected in this SELECT statement
So I add a variable called maxcounter in the declare section and modify my select:
SELECT
max(st.counter_id) into maxcounter
FROM
vpx_hist_stat1 st;
dbms_output.put_line(‘Maxcounter: ‘ || TO_CHAR(maxcounter));
And I have a procedure that executes and does something. On to the rest of the select. After several iterations of trial and error, I have the SQL query wokring as a cursor. I’ve now figured out even a better way to work. I’ve modified my file like below, and can now cat it into the sqlplus interpreter:
set arraysize 1
set MAXDATA 6000
set serveroutput on
create or replace procedure stattest
is
v_stat_rollup_level int := 2;
maxcounter int := 0;
CURSOR MYCURSOR is SELECT
st.counter_id as COUNTER_ID,
st.STAT_VAL as STAT_VAL
FROM vpx_sample_time1 sm,
vpx_stat_counter sc,
vpx_stat_def sd,
vpx_hist_stat1 st
WHERE
st.time_id = sm.time_id
AND st.counter_id=SC.COUNTER_ID
AND SC.STAT_ID=SD.ID
AND SD.ROLLUP_TYPE=3
AND SD.STAT_LEVEL<=v_stat_rollup_level;
begin
open MYCURSOR;
FOR recname in MYCURSOR
LOOP
dbms_output.put_line(‘Value: ‘ || TO_CHAR(v_stat_rollup_level));
END LOOP;
dbms_output.put_line(‘Maxcounter: ‘ || TO_CHAR(maxcounter));
end stattest;
/
show errors procedure stattest
.
OK, I am going to bail here. This post has been half written for a while, and is not going to get completed anytime soon. I think I’d vote “Over” as something that would be useful in the next rev of postgres, if only for simplifying porting from Oracle.
