Quick Search:
 
 The Oracle PL/SQL RANGE Keyword      [Return To Index] Jump to:  

Term: RANGE

Definition:
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.

Example Syntax:

[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>.

Example Usage:

SELECT COUNT(*) OVER (ORDER_BY SALARY RANGE 50 PRECEDING) 
FROM EMPLOYEE


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.

Example Syntax:

PARTITION BY RANGE (COLUMN) 
(
PARTITION [NAME] [EXPRESSION]
...
)


Example Usage:

The table ORDERS is partitioned based on

CREATE TABLE ORDERS
(
ORDER_ID NUMBER ,
ORDER_DATE DATE,
ORDER_NAME NUMBER)
PARTITION BY RANGE (ORDER_DATE)
(
PARTITION P_Q1 VALUES LESS THAN
(TO_DATE('2010-04-01', 'YYYY-MM-DD')),
PARTITION P_Q2 VALUES LESS THAN
(TO_DATE('2010-07-01', 'YYYY-MM-DD')),
PARTITION P_Q3 VALUES LESS THAN
(TO_DATE('2010-10-01', 'YYYY-MM-DD'))
);



Related Links:
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org