The Power of Inline Views
Overview
The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.
OK, so why use the complicated select in the first place? Why not just create the view? Well, one good reason is that creating a view gives you another database object to maintain, and adds more complexity to your system. By placing the view "inside" your main select, you have all of the code needed to support the query in one place.
If you have a query as the following ...
SELECT a
FROM table
WHERE id = :id
AND b = (SELECT MAX (b)
FROM table
WHERE id = :id)... it can be worth to check if an inline view, instead of the subquery will be faster.
Example 1 (Replace Subquery for MAX)
With Subquery
CREATE TABLE test (id INT, height INT, acc_date DATE);
INSERT INTO test (id, height, acc_date)
SELECT MOD(ROWNUM,1000), DBMS_RANDOM.RANDOM,
SYSDATE-1000+DBMS_RANDOM.VALUE(0,1000)
FROM all_objects;
6357 rows created.
COMMIT;
CREATE INDEX test_idx on test (id, acc_date, height);
Index created.
ANALYZE TABLE test COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMNS;
Table analyzed.
alter session set timed_statistics=true;
alter session set sql_trace=true;
VARIABLE b1 NUMBER
exec :b1 := 10
ALTER SESSION SET TIMED_STATISTICS=TRUE;
ALTER SESSION SET SQL_TRACE=TRUE;
SELECT max(height)
from test
WHERE id = :b1
AND acc_date = (SELECT MAX(acc_date)
FROM test
WHERE id = :b1);
MAX(HEIGHT)
-----------
1480603530
Elapsed: 00:00:00.12
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=2 Card=1 Bytes=17)
3 2 SORT (AGGREGATE)
4 3 FIRST ROW (Cost=2 Card=6 Bytes=60)
5 4 INDEX (RANGE SCAN (MIN/MAX)) OF 'TEST_IDX' (NON-UNIQUE) (Cost=2 Card=1060)
tkprof gek1_ora_16520.trc gek1_ora_16520.out explain=scott/tiger sort=exeela sys=no
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 2 0 0
Fetch 2 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 1
With Inline View
VARIABLE b1 NUMBER
exec :b1 := 10
SELECT height
FROM (SELECT height
FROM test
WHERE id = :b1
ORDER BY id DESC, acc_date DESC, height DESC)
WHERE ROWNUM = 1;
HEIGHT
----------
1480603530
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=13)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=2 Card=6 Bytes=78)
3 2 INDEX (RANGE SCAN DESCENDING) OF 'TEST_IDX' (NON-UNIQUE) (Cost=2 Card=6 Bytes=102)tkprof gek1_ora_16521.trc gek1_ora_16521.out explain=scott/tiger sort=exeela sys=no
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.06 2 41 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.06 2 43 0 1
Example 2 (Replace Subquery for MAX)
Original Query from a trace session:
SELECT switch_time,rat_id
FROM tariff
WHERE effdate = (SELECT MAX(effdate)
FROM tariff
WHERE effdate <= TRUNC(:b1)
AND weekday = :b2
AND t_id = :b3)
AND TO_CHAR(switch_time,'HH24:MI') <= TO_CHAR(:b1,'HH24:MI')
AND weekday = :b2
AND t_id = :b3
ORDER BY TO_CHAR(switch_time,'HH24:MI') DESC
With Subquery
alter session set timed_statistics=true;
select value from v$parameter where name = 'user_dump_dest';
alter session set sql_trace=true;VARIABLE b1 VARCHAR2(19)
exec :b1 := '07.04.1999:13:30:31'
VARIABLE b2 NUMBER
exec :b2 := 2
VARIABLE b3 NUMBER
exec :b3 := 317
SELECT switch_time, rat_id
FROM tariff
WHERE effdate = (SELECT MAX(effdate)
FROM tariff
WHERE effdate <= TRUNC(TO_DATE(:b1,'DD.MM.YYYY:HH24:MI:SS'))
AND weekday = :b2
AND T_ID = :b3)
AND TO_CHAR(switch_time,'HH24:MI') <= TO_CHAR(TO_DATE(:b1,'DD.MM.YYYY:HH24:MI:SS'),'HH24:MI')
AND weekday = :b2
AND t_id = :b3
ORDER BY TO_CHAR(switch_time,'HH24:MI') DESC;
SWITCH_TI RAT_ID
--------- ----------
01-JAN-98 3
01-JAN-98 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=21)
1 0 SORT (ORDER BY) (Cost=4 Card=1 Bytes=21)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'TARIFF' (Cost=2 Card=1 Bytes=21)
4 3 SORT (AGGREGATE)
5 4 FILTER
6 5 INDEX (RANGE SCAN) OF 'PK_TARIFF' (UNIQUE) (Cost=2 Card=1 Bytes=12)
TKPROF:
tkprof xyz.trc xyz.out explain=user/pwd sort=exeela sys=no
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.00 0 38 8 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.00 0 38 8 4
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
With Inline View
VARIABLE b1 VARCHAR2(19)
exec :b1 := '07.04.2005:13:30:31'
VARIABLE b2 NUMBER
exec :b2 := 2
VARIABLE b3 NUMBER
exec :b3 := 317
SELECT switch_time, rat_iD
FROM (SELECT switch_time, rat_id
FROM tariff
WHERE effdate <= TRUNC(TO_DATE(:b1,'DD.MM.YYYY:HH24:MI:SS'))
AND weekday = :b2
AND t_id = :b3
ORDER BY effdate DESC)
WHERE TO_CHAR(switch_time,'HH24:MI') <= TO_CHAR(TO_DATE(:b1,'DD.MM.YYYY:HH24:MI:SS'),'HH24:MI');
SWITCH_TI RAT_ID
--------- ----------
01-JAN-98 3
01-JAN-98 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=22)
1 0 VIEW (Cost=4 Card=1 Bytes=22)
2 1 SORT (ORDER BY) (Cost=4 Card=1 Bytes=21)
3 2 FILTER
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TARIFF' (Cost=2 Card=1 Bytes=21)
5 4 INDEX (RANGE SCAN) OF 'PK_TARIFF' (UNIQUE) (Cost=2 Card=1)
TKPROF:
tkprof xyz.trc xyz.out explain=user/pwd sort=exeela sys=no
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 19 4 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 19 4 4
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Example 3 (cannot have join with CONNECT BY)
Have you ever tried to join to a hierarchical query (a query using CONNECT BY and PRIOR) only to get this message:
ORA-01437: cannot have join with CONNECT BY
One of the limitations of hierarchical queries is that you cannot join to them. However, there are often times you would like to join to them anyway. For instance, if the hierarchy table only has surrogate keys, and you would like to display the real value. This tip shows how you can use "Inline Views" to join tables to a hierarchical query.
SELECT level, LPAD(' ',2*level-2)||ename ename, empno, mgr, dept.deptno, dept.dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
CONNECT BY PRIOr empno = mgr
START WITH empno = 7839;ORA-01437: cannot have join with CONNECT BY
SELECT E.emplevel, SUBSTR(E.ename,1,15) "ENAME", E.empno, dept.deptno, dept.dname
FROM dept, (SELECT level emplevel, LPAD(' ',2*level-2)||ename ename, empno, mgr, deptno
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH empno = 7839) E
WHERE E.deptno = dept.deptno
/
EMPLEVEL ENAME EMPNO DEPTNO DNAME
---------- --------------- ---------- ---------- --------------
1 KING 7839 10 ACCOUNTING
2 CLARK 7782 10 ACCOUNTING
3 MILLER 7934 10 ACCOUNTING
2 JONES 7566 20 RESEARCH
3 SCOTT 7788 20 RESEARCH
4 ADAMS 7876 20 RESEARCH
3 FORD 7902 20 RESEARCH
4 SMITH 7369 20 RESEARCH
2 BLAKE 7698 30 SALES
3 ALLEN 7499 30 SALES
3 WARD 7521 30 SALES
3 MARTIN 7654 30 SALES
3 TURNER 7844 30 SALES
3 JAMES 7900 30 SALES
Example 3 (ROWNUM 1 Problem)
A rownum restriction starting with 1 works:
ROWNUM does not work for ranges that don't start at 1.
A ROWNUM restriction starting with 1 works:SELECT ROWNUM,ename from emp WHERE ROWNUM BETWEEN 1 and 3
/
ROWNUM ENAME
---------- ----------
1 SMITH
2 ALLEN
3 WARDHowever, if you try to use a range it will not work. For example:
SELECT ROWNUM,ename from emp WHERE ROWNUM BETWEEN 2 and 3
/
no rows selectedUsing an Inline View to get around this limitation:
SELECT t1.rn, t1.ename
FROM (SELECT ROWNUM rn, ename
FROM emp) t1
WHERE t1.rn BETWEEN 2 and 3
/The main trick to this query is the "internal" select statement. This select statement in the from clause, basically does a full query of the table, then returns the values (along with the psuedo-column ROWNUM) to the "outside" query. The outside query can then operate on the results of the internal query. In order to access the internal query's columns from the external query, you need to give the internal query an alias ("t1" highlighted below): This allows you to refer to the columns using the "t1" (highlighted below): Since "ROWNUM" is a psuedo-column and therefore a reserved word, you need to alias that column in the internal query in order to refer to it in the outside query:
Example 4 (ROWNUM and ORDER BY Problem, TOP-N Queries)
The following query form is almost wrong:
select * from emp where ROWNUM <= 5 order by sal desc; /* WRONG! */
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7369 SMITH CLERK 7902 17-DEC-80 800 20The users intention was most likely to get the the top-five paid people - a top-N query. What the will get is five random records (the first five we happen to hit), sorted by salary. If you use an inline view with the ORDER BY inside the inline view, you get the correct result.
select * from (select * from emp order by sal desc) where rownum <= 5;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
Example 5 (Pagination with ROWNUM)
Pagination with ROWNUM can be used to get rows N thru M of a result set. The general form of this is as follows:
SELECT *
FROM (SELECT a.*, ROWNUM rn
FROM (enter your query here) a
WHERE ROWNUM <= :MAX_ROW)
WHERE rn >= :MIN_ROW;SELECT *
FROM (SELECT a.*, ROWNUM rn
FROM (SELECT * FROM emp) a
WHERE ROWNUM <= 6)
WHERE rn >= 2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 2
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 3
7566 JONES MANAGER 7839 02-APR-81 2975 20 4
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 5
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 6
Example 6 (Simplify complex Inline View with an Object Type)
Sometimes, complex Inline Views can be simplified by an Object Type.
SELECT dname,
LTRIM(SUBSTR(IV, 1, 30 )) ename,
LTRIM(SUBSTR(IV, 31, 10 )) job,
TO_DATE(SUBSTR(IV, 40),'yyyymmddhh24miss') hiredate
FROM (
SELECT dname,
(
SELECT RPAD(ename,30)||RPAD(job,30)||TO_CHAR(hiredate,'yyyymmddhh24miss')
FROM emp
WHERE rownum = 1
AND emp.deptno = dept.deptno
) IV
FROM dept
)
/
DNAME ENAME JOB HIREDATE
-------------- ------------------------------ ---------- ---------
ACCOUNTING CLARK MANAGER 09-JUN-81
RESEARCH SMITH CLERK 17-DEC-80
SALES ALLEN SALESMAN 20-FEB-81
OPERATIONSWith an Object Type
CREATE OR REPLACE TYPE FormatType AS OBJECT
(
ename VARCHAR2(30),
job VARCHAR2(10),
hiredate DATE)
/
SELECT dname,
IV1.IV2.ename ename,
IV1.IV2.job job,
IV1.IV2.hiredate hiredate
FROM (
SELECT dname,
(
SELECT FormatType(ename,job,hiredate)
FROM emp
WHERE rownum = 1
AND emp.deptno = dept.deptno
) IV2
FROM dept
) IV1
/
DNAME ENAME JOB HIREDATE
-------------- ------------------------------ ---------- ---------
ACCOUNTING CLARK MANAGER 09-JUN-81
RESEARCH SMITH CLERK 17-DEC-80
SALES ALLEN SALESMAN 20-FEB-81
OPERATIONSThe select statement within the select statement must always return single row, otherwise we will get the following error
ORA-01427: single-row subquery returns more than one row.