In Oracle PL/SQL, the RANGE keyword is used in two different contexts. First, in windowing clause specifications of Analytic functions. Second, while implementing RANGE PARTITION within a table.
1. The windows clause in Analytic functions
The windowing clause is used to define the sliding window for the analytic function. Data windows can be set based on RANGE and ROWS.
RANGE specifies the window size offset from the current row based on the WHERE clause. For example, a specification 'RANGE 10 PRECEDING' would consolidate a window of rows which follow and precede the current row by 5 units. The units must be of NUMBER or DATE data type only.
[ANALYTIC FUNCTION] OVER [ORDER BY RANGE BETWEEN <start expression> AND <end expression> ]
Here, <start expression> can be one of the following: UNBOUNDED PRECEDING, CURRENT ROW, <expression> [PRECEDING | FOLLOWING]. The same values are available for <end expression>.
SELECT COUNT(*) OVER (ORDER_BY SALARY RANGE 50 PRECEDING)
2. RANGE based partitioning
Range based partitioning is one of the partitioning methods. It splits a database table based on values of one of its own column.
PARTITION BY RANGE (COLUMN)
PARTITION [NAME] [EXPRESSION]
The table ORDERS is partitioned based on
CREATE TABLE ORDERS
ORDER_ID NUMBER ,
PARTITION BY RANGE (ORDER_DATE)
PARTITION P_Q1 VALUES LESS THAN
PARTITION P_Q2 VALUES LESS THAN
PARTITION P_Q3 VALUES LESS THAN