Analytic function
This operation was introduced in Oracle 10.2
This operation is implemented in the following versions
|
This example was developed using Oracle 10.2.0.1 on Linux
This example requires the following table definitions
CREATE TABLE t1 (c1 NUMBER NOT NULL,c2 NUMBER); CREATE INDEX i1 ON t1 (c1);
The table must be analysed
ANALYZE TABLE t1 COMPUTE STATISTICS;
The statement
SELECT c2,r1 FROM ( SELECT c2, RANK () OVER ( ORDER BY c1 ) AS r1 FROM t1 ) WHERE r1 < 10;
generates the following execution plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 VIEW 2 1 WINDOW (NOSORT STOPKEY) 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T1' 4 3 INDEX (FULL SCAN) OF 'I1' (NON-UNIQUE)