SQL and PLSQL features for APEX Developers

Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
SQL and PL/SQL
The meanest, fastest thing out there
Connor McDonald
1

Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
Typical speaker ego slide
youtube tinyurl.com/connor-tube
blog connor-mcdonald.com
twitter @connor_mc_d

17
"fine-grained to perform a single function"
"Each service is ... minimal, and complete"
https://en.wikipedia.org/wiki/Microservices
select COUNT(*)
from PEOPLE
where GENDER = 'MALE'

20
"By abstracting the underlying implementation"
"describes the expected behaviour ... but can have multiple implementations"
https://en.wikipedia.org/wiki/Application_programming_interface
select NAME, STREET_NO, ZIP_CODE
from PEOPLE p,
ADDRESS a
where p.AGE > 50
and p.ADDRESS_ID = a.ADDRESS_ID;
procedure MY_PROC is
begin
open rc for
end;

this session is not about ...
26

SQL> with x( s, ind ) as
2 ( select sud, instr( sud, '.' )
3 from ( select replace(replace(
4 replace(replace(:board,'-'),'|'),' '),chr(10)) sud
5 from dual )
6 union all
7 select substr(s,1,ind-1)||z||substr(s,ind+1)
8 , instr(s,'.',ind+1)
9 from x
10 , ( select to_char( rownum ) z
11 from dual connect by rownum <= 9 ) z
12 where ind > 0
13 and not exists (
14 select null
15 from ( select rownum lp from dual
16 connect by rownum <= 9 )
17 where z = substr(s,trunc((ind-1)/9)*9+lp,1)
29

18 or z = substr(s,mod(ind-1,9)-8+lp*9,1)
19 or z = substr(s,mod(trunc((ind-1)/3),3)*3
20 +trunc((ind-1)/27)*27+lp
21 +trunc((lp-1)/3)*6,1)
22 )
23 ),
24 result as (
25 select s
26 from x
27 where ind = 0 )
28 select
29 regexp_replace(substr(s,(idx-1)*9+1,9),
30 '(...)(...)(...)',
31 '1|2|3')||
32 case when mod(idx,3)=0 then chr(10)||rpad('-',11,'-') end soln
33 from result,
34 ( select level idx
35 from dual
36 connect by level <= 9 )
30
Ack: Anton Scheffer,
https://technology.amis.nl

SQL> variable board varchar2(1000)
SQL> begin :board :=
2 '53.|.7.|...
3 6..|195|...
4 .98|...|.6.
5 -----------
6 8..|.61|..3
7 4..|8.3|..1
8 7..|.2.|..6
9 -----------
10 .6.|...|28.
11 ...|419|..5
12 ...|.8.|.79
13 ';
14 end;
31
5 3 7
6 1 9 5
9 8 6
8 6 1 3
4 8 3 1
7 2 6
6 2 8
4 1 9 5
8 7 9

SOLUTION
-----------
534|678|912
672|195|348
198|342|567
-----------
859|761|423
426|853|791
713|924|856
-----------
961|537|284
287|419|635
345|286|179
-----------
32
sud.sql

33
100%
% of developers that
will need to solve Sudoku
as part of their job

34
100%
% of developers that need
to get real sh#@t done

44
PL/SQL resolved expressions

47
SQL> create table MY_TABLE
2 (
3 MY_COLUMN_IS_BETTER_BECAUSE_IT_NOW_HAS_MORE_MEANING
4 );
Table created.
DATE

SQL> create or replace
2 procedure process_tab is
3 l_tab varchar2(30);
4 begin
5 select table_name
6 into l_tab
7 from user_tables
8 where ...

SQL> exec process_tab;
ORA-06502: PL/SQL: numeric or value error:
character string buffer too small

52
expression in place of literal

SQL> declare
2 c_var_length constant pls_integer := 30;
3 l_str varchar2(c_var_length);
4 begin
5 null;
6 end;
7 /
PL/SQL procedure successfully completed.

54
must be resolvable at compile time
SQL
function

SQL> declare
2 c_var_length constant pls_integer := to_number('30');
4 begin
5 null;
6 end;
7 /
l_str varchar2(c_var_length);
*
ERROR at line 3:
ORA-06550: line 3, column 25:
PLS-00491: numeric literal required

SQL> declare
2 c_var_length constant pls_integer := 30;

3 l_str varchar2(ora_max_name_len);
4 begin

3
4 $if DBMS_DB_VERSION.VER_LE_12_1 $then
5 l_str varchar2(30);
6 $else
7 l_str varchar2(ora_max_name_len);
8 $end
9
10 begin

60
"Wasn't I just supposed to use ..."

3 l_str varchar2( );
4 begin
5 select owner||'.'||table_name
6 into l_str
7 from all_tables
8 where ...
9
?

3 l_str varchar2(2*ora_max_name_len+1);
4 begin
5 select owner||'.'||table_name
6 into l_str
7 from all_tables
8 where ...
9

66
So ... let's try that again

68
compile time resolvable sizes

SQL> desc STORE_SALES
Name Type
--------------------------------------------------------------------------------- ----------
SALES_PK NUMBER(38)
DATE_AT_WHICH_CUSTOMER_CAME_INTO_THE_STOP DATE
DATE_AT_WHICH_CUSTOMER_CAME_LEFT_THE_STOP DATE
DURATION_CUSTOMER_SPENT_BROWSING_SALES_CATALOG_BEFORE_ASKING_FOR_ASSISTANCE_MINS NUMBER(38)
ITEM_THAT_CUSTOMER_PURCHASED VARCHAR2(50)
AMOUNT_CUSTOMER_TENDERED_TO_CASHIER_IN_50DOLLAR_NOTES NUMBER(38)
AMOUNT_CUSTOMER_TENDERED_TO_CASHIER_IN_COINS_IN_TOTAL_CENTS NUMBER(38)
LENGTH_IN_MILLIMETERS_OF_THE_RECEIPT_WE_PRINTED_FOR_THE_CUSTOMER NUMBER(38)
AVERAGE_LENGTH_OF_SHOELACE_OF_STORE_ASSISTANT_THAT_HELPED_THE_CUSTOMER NUMBER(38)
...
...
...

SQL> select table_name,
2 listagg(column_name, ',') within group
3 (order by column_id) cols
4 from user_tab_columns
5 group by table_name;
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long

SQL> select
2 column_name,
3 ( select listagg(column_name,',') within group ( order by column_id )
4 from all_tab_columns
5 where table_name = 'EMP'
6 and owner = 'SCOTT'
7 and column_id <= a.column_id ) cols
8 from all_tab_columns a
9 where table_name = 'EMP'
10 and owner = 'SCOTT'
11 order by column_id;
COLUMN_NAME COLS
------------------------------ ----------------------------------------------------
EMPNO EMPNO
ENAME EMPNO,ENAME
JOB EMPNO,ENAME,JOB
MGR EMPNO,ENAME,JOB,MGR
HIREDATE EMPNO,ENAME,JOB,MGR,HIREDATE
SAL EMPNO,ENAME,JOB,MGR,HIREDATE,SAL
COMM EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM
DEPTNO EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
8 rows selected.

78
first principles
pipeline function

2 listagg(column_name, ',' on overflow truncate)
3 within group (order by column_id) cols
TABLE_NAME COLS
------------- ------------------------------------------------------------
STORE_SALES SALES_PK,DATE_AT_WHICH_CUSTOMER_CAME_INTO_THE_STOP,DATE_AT_W
HICH_CUSTOMER_CAME_LEFT_THE_STOP,DURATION_CUSTOMER_SPENT_BRO
WSING_SALES_CATALOG_BEFORE_ASKING_FOR_ASSISTANCE_IN_MINS,ITE
,LENGTH_IN_MILLIMETERS_OF_THE_RECEIPT_WE_PRINTED_FOR_THE_CUS
[snip]
TIME_SPENT_WHILST_REFOLDING_ITE...(2378)

2 listagg(column_name, ',' on overflow truncate '[more]')
TABLE_NAME COLS
------------- ------------------------------------------------------------
[snip]
TIME_SPENT_WHILST_REFOLDING_ITE[more](2378)

2 listagg(column_name,','
3 on overflow truncate '[more]' without count)
TABLE_NAME COLS
------------- ------------------------------------------------------------
[snip]
TIME_SPENT_WHILST_REFOLDING_ITE[more]

2 listagg(column_name,','
3 on overflow truncate '' without count)
TABLE_NAME COLS
------------- ------------------------------------------------------------
[snip]
TIME_SPENT_WHILST_REFOLDING_ITE

no correlation 
91
but its my fault

SQL> select surname
2 from names;
SURNAME
------------------------------
jones
brown
SMITH
sigh...

SQL> select initcap(surname)
2 from names;
SURNAME
------------------------------
Jones
Brown
Smith
Mcdonald

SQL> select *
2 from customers
3 where cust_name = 'ADAMS';
COUNTRY CREATED CUST_NAME
------------ --------- ------------
AUS 07-NOV-16 ADAMS

SQL> select *
2 from customers
3 where upper(cust_name) = 'ADAMS';
------------ --------- ------------
AUS 07-NOV-16 Adams
AUS 07-NOV-16 ADAMS
AUS 07-NOV-16 adams

98
SQL> select * from customers

SQL> select column_name
2 from user_ind_columns
3 where index_name = 'CUST_IX';
COLUMN_NAME
------------------------------
CUST_NAME
-------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
-------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 152 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 1 | 152 |
-------------------------------------------------------

SQL> create index cust_ix
2 on customers ( cust_name );
Index created.
SQL> create index cust_ix2
2 on customers ( upper(cust_name) );
Index created.

102
DML slower
more contention
more redo/undo

SQL> alter table customers shrink space;
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified

SQL> CREATE TABLE CUSTOMERS
2 (
3 COUNTRY VARCHAR2(128),
4 CREATED DATE,
5 CUST_NAME VARCHAR2(150) COLLATE BINARY_CI
6 );
Table created.
"case insenstive"

SQL> create index cust_ix
2 on customers ( cust_name);
Index created.
SQL> set autotrace traceonly explain
-----------------------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS | 1 |
|* 2 | INDEX RANGE SCAN | CUST_IX | 1 |
-----------------------------------------------------------------

------------ --------- ----------------
AUS 07-NOV-16 Adams
AUS 08-NOV-16 ADAMS
AUS 09-NOV-16 adams

113
binary_ci
------------ --------- ------------
AUS 07-NOV-16 Adams
AUS 08-NOV-16 ADAMS
AUS 09-NOV-16 adams

114
binary_ai
------------ --------- -----------
AUS 07-NOV-16 Adams
AUS 08-NOV-16 ADAMS
AUS 09-NOV-16 adams
AUS 10-NOV-16 adáms
adáms

SQL> alter table people default collation binary_ai;
new columns only

SQL> alter table people default collation binary_ai;
*
ERROR at line 1:
ORA-43929: Collation cannot be specified if parameter
MAX_STRING_SIZE=STANDARD

2 package customer_pkg as
3
4 cursor get_customers(p_name varchar2) is
5 select * from customers
6 where upper(customer_name) = upper(p_name);
7
8 ...

3
4 --
5 -- do not use
6 --
10
11 --
12 -- fixed with CI collation
13 --
14 cursor get_cust_ci(p_name varchar2) is
16 where customer_name = p_name;

6
7 pragma deprecate(get_customers,'Use get_cust_ci');
8
9 cursor get_cust_ci(p_name varchar2) is
11 where customer_name = p_name;

SQL> alter system set
2 plsql_warnings = enable:(6019,6020,6021,6022)';

SQL> alter procedure PROCESS_CUSTOMERS compile;
SP2-0804: Procedure created with compilation warnings
LINE/COL ERROR
-------- -----------------------------------------------------------
1/1 PLW-05018: unit PROCESS_CUSTOMERS omitted optional AUTHID
clause; default value DEFINER used
7/9 PLW-06020: reference to a deprecated entity:
GET_CUSTOMERS declared in unit CUSTOMER_PKG[2,10].
Use get_cust_ci instead

SQL> alter system set
2 plsql_warnings = error:(6019,6020,6021,6022)';

SQL> alter procedure PROCESS_CUSTOMERS compile;
SP2-0804: Procedure created with compilation errors
LINE/COL ERROR
-------- -----------------------------------------------------------
1/1 PLW-05018: unit PROCESS_CUSTOMERS omitted optional AUTHID
clause; default value DEFINER used
GET_CUSTOMERS declared in unit CUSTOMER_PKG[2,10].
Use get_cust_ci instead

{"stuff": {
"id": "123",
"description": "File",
"checkout": {
"metadata": [
{"created": "2018-04-01"},
]
}
"checkin": {
"metadata": [
{"updated": "2018-04-17 17:02:12"},
{"review": "TODAY+7"}
]
}
}}
137

{"stuff": {
"id": "123",
"checkout": {
"metadata": [
{"created": "2018-04-01"},
]
}
"checkin": {
"metadata": [
{"updated": "2018-04-17 17:02:12"},
]
}
}}
138

{"stuff": {
"id": "123",
"checkout": {
"metadata": [
{"created": "2018-04-01"},
]
}
"checkin": {
"metadata": [
{"updated": "2018-04-17 17:02:12"},
]
}
}}
139

SQL> select TO_DATE(CREATED_DATE)
2 from MY_JSON_AS_TABLE;
ERROR at line 1:
ORA-01858: a non-numeric character was found ...
SQL> select TO_DATE(CREATED_DATE)
3 where ????
142
MY_PLSQL_CHECKER(CREATED_DATE) = 'OK'

"don't call PLSQL from SQL !!!!"
145

SQL> select CREATED_DATE
CREATED_DATE
--------------------
01-FEB-2016
12-MAR-2012
54-AUG-2013
09-SEP-2014
23-OCT-2012
...
...
147

SQL> select to_date(created_date, 'dd-mon-yyyy')
2 from MY_JSON_AS_TABLE
3 where validate_conversion(
4 created_date as date, 'dd-MON-yyyy'
5 ) = 1;
TO_DATE(C
---------
01-FEB-16
12-MAR-12
09-SEP-14
23-OCT-12
148

SQL> select SALARY,
2 cast(SALARY as number
3 DEFAULT -1 ON CONVERSION ERROR) conv_sal
SALARY CONV_SAL
---------- ----------
120000 120000
172125 172125
128000 128000
125,000 -1
99500 99500
...
...
150
TO_DATE
TO_NUMBER
TO_TIMESTAMP
etc

151
18c improvements
select raw_data,
TO_UTC_TIMESTAMP_TZ(raw_data) as utc
from t;
RAW_DATA UTC
-------------------------- ---------------------------------------
20171122T07:14:47 22-NOV-17 07.14.47.000000000 AM +00:00

154
SQL> select *
2 from timeslots;
HR
--
8
9
10
11
12
13
14
15
16
SQL> select *
2 from bookings;
HR ROOM WHO
------- ---------- -------
8 Room2 PETE
9 Room1 JOHN
11 Room1 MIKE
14 Room2 JILL
15 Room2 JANE
16 Room1 SAM

bookings by hour
155
conventional outer join

156
SQL> SELECT hrs.hr, t1.room, t1.who
2 from timeslots hrs
3 left outer join bookings t1
4 on hrs.hr = t1.hr
HR ROOM WHO
------- ---------- ----------
8 Room2 PETE
9 Room1 JOHN
10
11 Room1 MIKE
12
13
14 Room2 JILL
15 Room2 JANE
16 Room1 SAM
156

158
HR ROOM WHO
------- ---------- ----------
8 Room2 PETE
9
10
11
12
13
14 Room2 JILL
15 Room2 JANE
16
HR ROOM WHO
------- ---------- ----------
8
9 Room1 JOHN
10
11 Room1 MIKE
12
13
14
15
16 Room1 SAM

159
SQL> select *
2 from timeslots;
HR
--
8
9
10
11
12
13
14
15
16
x "Room 1"
x "Room 2"
...
x "Room n"

161
SQL> SELECT hrs.hr, t1.room, t1.who
2 FROM bookings t1
3 PARTITION BY (t1.room)
4 RIGHT OUTER JOIN hrs ON (hrs.hr = t1.hr);
HR ROOM WHO
--------- ---------- ----------
8 Room1
9 Room1 JOHN
10 Room1
11 Room1 MIKE
12 Room1
13 Room1
14 Room1
15 Room1
16 Room1 SAM
8 Room2 PETE
9 Room2
10 Room2
11 Room2
12 Room2
13 Room2
14 Room2 JILL
15 Room2 JANE
16 Room2
161

164
"employees by hiredate, recent first"

SQL> select empno, ename, hiredate
2 from emp
3 where rownum <= 5
4 order by hiredate desc;
EMPNO ENAME HIREDATE
---------- ---------- -------------------
7654 MARTIN 28/09/1981 00:00:00
7566 JONES 02/04/1981 00:00:00
7521 WARD 22/02/1981 00:00:00
7499 ALLEN 20/02/1981 00:00:00
7369 SMITH 17/12/1980 00:00:00
165

SQL> select *
2 from (
3 select empno, ename, hiredate
4 from emp
5 order by hiredate desc
6 )
7 where rownum <= 5;
---------- ---------- ---------
7876 ADAMS 12-JAN-83
7788 SCOTT 09-DEC-82
7934 MILLER 23-JAN-82
7900 JAMES 03-DEC-81
7902 FORD 03-DEC-81
167

SQL> select *
2 from (
3 select
4 empno, ename, hiredate,
5 row_number() over ( order by hiredate desc) rn
6 from emp
7 )
8 where rn <= 5;
168

2 from emp
4 fetch first 5 rows only;
---------- ---------- ---------
7876 ADAMS 12-JAN-83
7788 SCOTT 09-DEC-82
7934 MILLER 23-JAN-82
7900 JAMES 03-DEC-81
7902 FORD 03-DEC-81
169

170
"TL;DR ... my app can do it"

public static void Paging(Connection conn ) throws Exception
{
PreparedStatement sql_stmt =
conn.prepareStatement(
"select empno, ename, hiredate
from emp
order by hiredate desc");
ResultSet rset = sql_stmt.executeQuery();
int i = 0;
while( rset.next() )
{
...
i = i + 1;
if (i > 5) {
break;
}
}
rset.close();
}
171

172
"TL;DR ... Apex can do it"

SQL> select *
2 from (
3 select empno, ename, hiredate
4 from emp
6 )
7 where rownum <= 5;
177
------------------------------------------------
------------------------------------------------
|* 1 | COUNT STOPKEY | | |
| 2 | VIEW | | 14 |
|* 3 | SORT ORDER BY STOPKEY| | 14 |
| 4 | TABLE ACCESS FULL | EMP | 14 |
------------------------------------------------

2 from emp
4 fetch first 5 rows only;
178
-------------------------------------------------
-------------------------------------------------
|* 1 | VIEW | | 14 |
|* 2 | WINDOW SORT PUSHED RANK| | 14 |
| 3 | TABLE ACCESS FULL | EMP | 14 |
-------------------------------------------------

you get other benefits
179
oc02.sql

180
"but what about the next page ?"

2 from emp
4 offset 5 rows fetch first 5 rows only;
---------- ---------- ---------
7839 KING 17-NOV-81
7654 MARTIN 28-SEP-81
7844 TURNER 08-SEP-81
7782 CLARK 09-JUN-81
7698 BLAKE 01-MAY-81
183

2 from emp
HIREDATE EMPNO ENAME
--------- ---------- --------
12-JAN-83 7876 ADAMS
09-DEC-82 7788 SCOTT
23-JAN-82 7934 MILLER
03-DEC-81 7902 FORD
03-DEC-81 7900 JAMES
17-NOV-81 7839 KING
28-SEP-81 7654 MARTIN
08-SEP-81 7844 TURNER
09-JUN-81 7782 CLARK
01-MAY-81 7698 BLAKE
02-APR-81 7566 JONES
22-FEB-81 7521 WARD
20-FEB-81 7499 ALLEN
17-DEC-80 7369 SMITH
185
fetch first 5 rows
offset 5 fetch next ...
17-FEB-91 7521 BROWN
Apex

2 from emp
HIREDATE EMPNO ENAME
--------- ---------- --------
12-JAN-83 7876 ADAMS
09-DEC-82 7788 SCOTT
23-JAN-82 7934 MILLER
03-DEC-81 7902 FORD
03-DEC-81 7900 JAMES
17-NOV-81 7839 KING
28-SEP-81 7654 MARTIN
08-SEP-81 7844 TURNER
09-JUN-81 7782 CLARK
01-MAY-81 7698 BLAKE
02-APR-81 7566 JONES
22-FEB-81 7521 WARD
20-FEB-81 7499 ALLEN
17-DEC-80 7369 SMITH
186
17-FEB-91 7521 BROWN
2 from emp
3 where hiredate < :last_shown

187
"an expensive query per page ?!?!

SQL> with first_200 as
2 ( select f.*, rownum r
3 from
4 ( select *
5 from t
6 order by owner, object_name desc
7 ) f
8 where rownum <= 200
9 )
10 select *
11 from first_200
12 where r <= 10
189
/*+ result_cache */ rownum r, f.*
oc03.sql
r between 11 and 20

Apex <= 5.1.4
190
Classic IR IG

Apex 18+
191
Classic IR IG
nearly...

"Employee salary list,
plus department total,
plus grand total"
194

select deptno, ename, sal
from emp
order by 1,2
call count cpu elapsed disk query current rows
------- ------ ------- --------- -------- -------- --------- -------
Parse 1 0.07 0.07 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 14 0.32 0.29 0 2 0 14
------- ------ ------- --------- -------- -------- --------- -------
total 14 0.40 0.37 0 2 0 14
197

SQL> select empno, ename, sal, deptno from emp
2 order by deptno;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7782 CLARK 2450 10
7839 KING 5000 10
7934 MILLER 1300 10
...
7900 JAMES 950 30
7698 BLAKE 2850 30
7654 MARTIN 1250 30
199
SQL> select deptno,
2 sum(sal)
3 from emp
4 group by deptno
5 order by deptno;
DEPTNO SUM(SAL)
---------- ----------
10 8750
20 10875
30 9400
SQL> select sum(sal) from emp;
SUM(SAL)
----------
29025

SQL> select empno, ename, sal, deptno from emp
2 order by deptno;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7782 CLARK 2450 10
7839 KING 5000 10
7934 MILLER 1300 10
...
7900 JAMES 950 30
7698 BLAKE 2850 30
7654 MARTIN 1250 30
201
SQL> select deptno,
2 sum(sal)
3 from emp
4 group by rollup(deptno)
5 order by deptno;
DEPTNO SUM(SAL)
---------- ----------
10 8750
20 10875
30 9400
29025

EMPNO SAL DEPTNO
---------- ---------- ----------
7782 2450 10
7839 5000 10
7934 1300 10
7566 2975 20
7902 3000 20
7876 1100 20
7369 800 20
203
DEPTNO SUM(SAL)
---------- ----------
10 8750
20 10875
30 9400
29025
7782 2450 10
7839 5000 10
7934 1300 10
7566 2975 20
10 8750

SQL> select deptno,
2 nvl2(rownum,max(empno),null) empno,
3 nvl2(rownum,max(ename),null) ename,
4 sum(sal)
5 from emp
6 group by rollup(deptno,rownum)
7 order by deptno,empno;
DEPTNO EMPNO ENAME SUM(SAL)
---------- ---------- ---------- ----------
10 7782 CLARK 2450
10 7839 KING 5000
10 7934 MILLER 1300
10 8750
20 7369 SMITH 800
20 7566 JONES 2975
...
30 7900 JAMES 950
30 9400
29025205

SQL> select deptno,job,sum(sal) from scott.emp
2 group by CUBE(deptno,job)
3 order by deptno,job;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
29025
207

SQL> select deptno, job, mgr, sum(sal) from emp
2 group by grouping sets (
3 (deptno),
4 (job,mgr), () ) ;
DEPTNO JOB MGR SUM(SAL)
---------- --------- ---------- ----------
CLERK 7902 800
PRESIDENT 5000
CLERK 7698 950
CLERK 7788 1100
CLERK 7782 1300
SALESMAN 7698 5600
MANAGER 7839 8275
ANALYST 7566 6000
10 8750
20 10875
30 9400
29025
209

212
"talking" to your database
... makes it faster

select prod_id, max(amount)
from stores st,
customers c,
sales s
where s.cust_id = c.cust_id(+)
and c.store_id = st.store_id
and s.amount > 10
group by prod_id
215
hash outer join ? nested loop ?
STORES first ?
sort merge ?

---------------------------------------------------
---------------------------------------------------
| 1 | HASH GROUP BY | | 100 |
|* 2 | HASH JOIN | | 990K |
| 3 | NESTED LOOPS SEMI | | 5000 |
| 4 | TABLE ACCESS FULL| CUSTOMERS | 5000 |
|* 5 | INDEX UNIQUE SCAN| STORE_IX | 50 |
|* 6 | TABLE ACCESS FULL | SALES | 990K |
---------------------------------------------------
216

217
can we do better ?
add indexes ?
rewrite query ?
result cache ?
materialized view ?

218
share your knowledge with the db
oc04.sql

Enjoy the conference !!!
youtube tinyurl.com/connor-tube
blog connor-mcdonald.com
twitter @connor_mc_d

SQL and PLSQL features for APEX Developers

More Related Content

What's hot

Similar to SQL and PLSQL features for APEX Developers

More from Connor McDonald

Recently uploaded

SQL and PLSQL features for APEX Developers

Editor's Notes