sql and pl/sql - aioug.org · sql> declare 2 c_var_length constant pls_integer := 30; 3 l_str...

7/17/2018

1

Copyright © 2017, Oracle and/or its affiliates. All rights reserved.

SQL and PL/SQL

The meanest, fastest thing out there

Connor McDonald

1

Connor McDonald

7/17/2018

2

3

4

7/17/2018

3


Typical speaker ego slide

youtube tinyurl.com/connor-tube

blog connor-mcdonald.com

twitter @connor_mc_d


6

https://asktom.oracle.com

7/17/2018

4


7https://asktom.oracle.com/officehours

80 hours of free access / Q&A

8

since Feb

7/17/2018

5

The biggest and best in the world !

9

July 195:30pm IST

https://tinyurl.com/biggest-office-hours

10

7/17/2018

6

10

why talk about this ?

11

# 1

7/17/2018

7

after all ...

12

13

7/17/2018

8

NoSQL

14

NoSQL

14

7/17/2018

9

NoSQL

14

non relational

15

7/17/2018

10

why talk about this

16

# 2

What is cool nowadays

17

7/17/2018

11

MICROSERVICES

18

SQL invented cool

19

7/17/2018

12

20

"fine-grained to perform a single function"

"Each service is ... minimal, and complete"

https://en.wikipedia.org/wiki/Microservices

20

"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'

7/17/2018

13

What is cooler nowadays

21

API

22

7/17/2018

14

23

"By abstracting the underlying implementation"

"describes the expected behaviour ... but can have multiple implementations"

https://en.wikipedia.org/wiki/Application_programming_interface

23




select NAME, STREET_NO, ZIP_CODE

from PEOPLE p,

ADDRESS a

where p.AGE > 50

and p.ADDRESS_ID = a.ADDRESS_ID;

7/17/2018

15

23




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;

key point

24

7/17/2018

16

this session is not about ...

25

being a smart-ass

26

7/17/2018

17

we can do anything ...

27

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)

28

7/17/2018

18

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 )

29

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;

30

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

7/17/2018

19

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;

30

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

-----------

31

sud.sql

7/17/2018

20

32

100%

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

33

100%

% of developers that needto get real stuff done

7/17/2018

21

real stuff

34

35

7/17/2018

22

some controversy...

36

37

7/17/2018

23

37

DBA

7/17/2018

24

39Public

39Public

7/17/2018

25

39Public

40Public

7/17/2018

26

40Public

40Public

7/17/2018

27

DBA stuff matters

41

oc00.sql

42

7/17/2018

28

43

PL/SQL resolved expressions

44

12c+

7/17/2018

29

45

longer names

46

SQL> create table MY_TABLE

2 (

3 MY_COLUMN_IS_BETTER_BECAUSE_IT_NOW_HAS_MORE_MEANING

4 );

Table created.

DATE

7/17/2018

30

46

SQL> create table MY_TABLE

2 (

3 MY_COLUMN_IS_BETTER_BECAUSE_IT_NOW_HAS_MORE_MEANING

4 );

Table created.

DATE

7/17/2018

31

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

7/17/2018

32

50

12.2

51

expression in place of literal

7/17/2018

33

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.

53

must be resolvable at compile time

SQL

function

7/17/2018

34

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;


7/17/2018

35



3 l_str varchar2(ora_max_name_len);

4 begin

57

start today

7/17/2018

36



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

59

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

7/17/2018

37

user_tables.table_name%type

61

yes ... but

7/17/2018

38



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( );

4 begin


6 into l_str

7 from all_tables

8 where ...

9

?

7/17/2018

39



3 l_str varchar2(2*ora_max_name_len+1);

4 begin


6 into l_str

7 from all_tables

8 where ...

9

64

7/17/2018

40

65

So ... let's try that again

66

longer names

7/17/2018

41

67

compile time resolvable sizes

68

7/17/2018

42

69

70

7/17/2018

43

71

72

7/17/2018

44

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

7/17/2018

45

75

tough to solve

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



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.

7/17/2018

46

SQL> select

2 column_name,

3 ( select listagg(column_name,',') within group ( order by column_id )

4 from all_tab_columns



7 and column_id <= a.column_id ) cols

8 from all_tab_columns a



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.

77

first principles

pipeline function

7/17/2018

47

78

12.2

79

improved overflow handling

7/17/2018

48


2 listagg(column_name, ',' on overflow truncate) 3 within group (order by column_id) cols




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)

7/17/2018

49

81

plus you get control


2 listagg(column_name, ',' on overflow truncate '[more]') 3 within group (order by column_id) cols



TABLE_NAME COLS

------------- ------------------------------------------------------------





[snip]

TIME_SPENT_WHILST_REFOLDING_ITE[more](2378)

7/17/2018

50


2 listagg(column_name,','

3 on overflow truncate '[more]' without count) 4 within group (order by column_id) cols



TABLE_NAME COLS

------------- ------------------------------------------------------------





[snip]

TIME_SPENT_WHILST_REFOLDING_ITE[more]


2 listagg(column_name,','

3 on overflow truncate '' without count) 4 within group (order by column_id) cols



TABLE_NAME COLS

------------- ------------------------------------------------------------





[snip]

TIME_SPENT_WHILST_REFOLDING_ITE

7/17/2018

51

85

86

column level collation

7/17/2018

52

every system ...

... I've worked on

88

7/17/2018

53

struggles with cAsE

no correlation ☺

90

7/17/2018

54

no correlation ☺

90

but its my fault

SQL> select surname

2 from names;

SURNAME

------------------------------

jones

brown

SMITH

sigh...

7/17/2018

55

SQL> select initcap(surname)

2 from names;

SURNAME

------------------------------

Jones

Brown

Smith

SQL> select initcap(surname)

2 from names;

SURNAME

------------------------------

Jones

Brown

Smith

Mcdonald

7/17/2018

56

and it just gets worse...

93

SQL> select *

2 from customers

3 where cust_name = 'ADAMS';

COUNTRY CREATED CUST_NAME

------------ --------- ------------

AUS 07-NOV-16 ADAMS

7/17/2018

57

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

96

7/17/2018

58

97

SQL> select * from customers


98

7/17/2018

59

SQL> select column_name

2 from user_ind_columns

3 where index_name = 'CUST_IX';

COLUMN_NAME

------------------------------

CUST_NAME

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 |

-------------------------------------------------------

7/17/2018

60

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.

101

DML slower

more contention

more redo/undo

7/17/2018

61

102

"not my problem"

103

7/17/2018

62

SQL> alter table customers shrink space;

SQL> alter table customers shrink space;

*

ERROR at line 1:

ORA-10631: SHRINK clause should not be specified

7/17/2018

63

105

a better way

106

column level collation

7/17/2018

64

107

?SQL> CREATE TABLE CUSTOMERS

2 (

3 COUNTRY VARCHAR2(128),

4 CREATED DATE,

5 CUST_NAME VARCHAR2(150) COLLATE BINARY_CI

6 );

Table created.

7/17/2018

65

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 |

-----------------------------------------------------------------

7/17/2018

66

110

"what is so special about that?"




------------ --------- ----------------

AUS 07-NOV-16 Adams

AUS 08-NOV-16 ADAMS

AUS 09-NOV-16 adams

7/17/2018

67




------------ --------- ----------------

AUS 07-NOV-16 Adams

AUS 08-NOV-16 ADAMS

AUS 09-NOV-16 adams

112

binary_ci




------------ --------- ------------

AUS 07-NOV-16 Adams

AUS 08-NOV-16 ADAMS

AUS 09-NOV-16 adams

7/17/2018

68

113

binary_ai




------------ --------- -----------

AUS 07-NOV-16 Adams

AUS 08-NOV-16 ADAMS

AUS 09-NOV-16 adams

AUS 10-NOV-16 adáms

113

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

7/17/2018

69

114

column | table | user

SQL> alter table people default collation binary_ai;

new columns only

7/17/2018

70

116

key point

SQL> alter table people default collation binary_ai;

*

ERROR at line 1:

ORA-43929: Collation cannot be specified if parameter

MAX_STRING_SIZE=STANDARD

7/17/2018

71

118

partitioned outer join

119

7/17/2018

72

120

SQL> select *

2 from timeslots;

HR

--

8

9

10

11

12

13

14

15

16

120

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

7/17/2018

73

bookings by hour

121

conventional outer join

122

SQL> SELECT hrs.hr, t1.room, t1.who

2 from timeslots hrs

3 left outer join bookings t1

4 on hrs.hr = t1.hr

5 order by 1

HR ROOM WHO

------- ---------- ----------

8 Room2 PETE

9 Room1 JOHN

10

11 Room1 MIKE

12

13

14 Room2 JILL

15 Room2 JANE

16 Room1 SAM

122

7/17/2018

74

bookings by hour per room

123

124

HR ROOM WHO

------- ---------- ----------

8

9 Room1 JOHN

10

11 Room1 MIKE

12

13

14

15

16 Room1 SAM

7/17/2018

75

124

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

125

SQL> select *

2 from timeslots;

HR

--

8

9

10

11

12

13

14

15

16

x "Room 1"

x "Room 2"

...

x "Room n"

7/17/2018

76

partitioned outer join

126


2 FROM bookings t1

3 PARTITION BY (t1.room)

4 RIGHT OUTER JOIN hrs ON (hrs.hr = t1.hr)

5 order by 1,2

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 127

7/17/2018

77


2 FROM bookings t1



5 order by 1,2

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 127


2 FROM bookings t1



5 order by 1,2

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 127

7/17/2018

78

128

pagination

129

7/17/2018

79

130

"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

131

7/17/2018

80


2 from emp

3 where rownum <= 5



---------- ---------- -------------------

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

131

inline view

132

7/17/2018

81

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

133

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;

134

7/17/2018

82


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

135

136

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

7/17/2018

83

public static void Paging(Connection conn ) throws Exception

{

PreparedStatement sql_stmt =

conn.prepareStatement(

"select empno, ename, hiredate

from emp

order by hiredate desc");

137

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();

}

137

7/17/2018

84

demo

139

oc01.sql

7/17/2018

85

let the database know

140

SQL> select *

2 from (


4 from emp


6 )


141

7/17/2018

86

SQL> select *

2 from (


4 from emp


6 )


141

------------------------------------------------


------------------------------------------------


|* 1 | COUNT STOPKEY | | |

| 2 | VIEW | | 14 |

|* 3 | SORT ORDER BY STOPKEY| | 14 |

| 4 | TABLE ACCESS FULL | EMP | 14 |

------------------------------------------------


2 from emp



142

7/17/2018

87


2 from emp



142

-------------------------------------------------


-------------------------------------------------


|* 1 | VIEW | | 14 |

|* 2 | WINDOW SORT PUSHED RANK| | 14 |

| 3 | TABLE ACCESS FULL | EMP | 14 |

-------------------------------------------------

you get other benefits

143

oc02.sql

7/17/2018

88

144

"but what about the next page ?"

there

145

isno

nextpage

7/17/2018

89

new query

146


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

147

7/17/2018

90


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

147

forget the OFFSET clause

148

7/17/2018

91


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

149

fetch first 5 rows


2 from emp



--------- ---------- --------




03-DEC-81 7902 FORD


17-NOV-81 7839 KING






22-FEB-81 7521 WARD



149

fetch first 5 rows

offset 5 fetch next ...

7/17/2018

92


2 from emp



--------- ---------- --------




03-DEC-81 7902 FORD


17-NOV-81 7839 KING






22-FEB-81 7521 WARD



149

fetch first 5 rows


17-FEB-91 7521 BROWN


2 from emp



--------- ---------- --------




03-DEC-81 7902 FORD


17-NOV-81 7839 KING






22-FEB-81 7521 WARD



149

fetch first 5 rows



7/17/2018

93


2 from emp



--------- ---------- --------




03-DEC-81 7902 FORD


17-NOV-81 7839 KING






22-FEB-81 7521 WARD



150



2 from emp



--------- ---------- --------




03-DEC-81 7902 FORD


17-NOV-81 7839 KING






22-FEB-81 7521 WARD



150


7/17/2018

94


2 from emp



--------- ---------- --------




03-DEC-81 7902 FORD


17-NOV-81 7839 KING






22-FEB-81 7521 WARD



150



2 from emp

3 where hiredate < :last_shown


151

"an expensive query per page ?!?!

7/17/2018

95

consider result caching

152

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

153

oc03.sql

7/17/2018

96



3 from

4 ( select *

5 from t


7 ) f


9 )

10 select *

11 from first_200

12 where r <= 10

153

oc03.sql



3 from

4 ( select *

5 from t


7 ) f


9 )

10 select *

11 from first_200

12 where r <= 10

153

oc03.sql

r between 11 and 20

7/17/2018

97



3 from

4 ( select *

5 from t


7 ) f


9 )

10 select *

11 from first_200

12 where r <= 10

153

/*+ result_cache */ rownum r, f.*

oc03.sql

r between 11 and 20

154

7/17/2018

98

totals / subtotals

155

"Employee salary list, plus department total,

plus grand total"

156

7/17/2018

99

157

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

158

7/17/2018

100


2 order by 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

158

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


2 order by 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

158

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

7/17/2018

101


2 order by 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

158

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

from 3 to 2

159

rollup

7/17/2018

102


2 order by 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

160


2 order by 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

160

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

7/17/2018

103

still messy...

161

EMPNO SAL DEPTNO

---------- ---------- ----------

7782 2450 10

7839 5000 10

7934 1300 10

7566 2975 20

7902 3000 20

7876 1100 20

7369 800 20

162

DEPTNO SUM(SAL)

---------- ----------

10 8750

20 10875

30 9400

29025

7782 2450 10

7839 5000 10

7934 1300 10

7566 2975 20

10 8750

7/17/2018

104

EMPNO SAL DEPTNO

---------- ---------- ----------

7782 2450 10

7839 5000 10

7934 1300 10

7566 2975 20

7902 3000 20

7876 1100 20

7369 800 20

162

DEPTNO SUM(SAL)

---------- ----------

10 8750

20 10875

30 9400

29025

7782 2450 10

7839 5000 10

7934 1300 10

7566 2975 20

10 8750

EMPNO SAL DEPTNO

---------- ---------- ----------

7782 2450 10

7839 5000 10

7934 1300 10

7566 2975 20

7902 3000 20

7876 1100 20

7369 800 20

162

DEPTNO SUM(SAL)

---------- ----------

10 8750

20 10875

30 9400

29025

7782 2450 10

7839 5000 10

7934 1300 10

7566 2975 20

10 8750

7/17/2018

105

EMPNO SAL DEPTNO

---------- ---------- ----------

7782 2450 10

7839 5000 10

7934 1300 10

7566 2975 20

7902 3000 20

7876 1100 20

7369 800 20

162

DEPTNO SUM(SAL)

---------- ----------

10 8750

20 10875

30 9400

29025

7782 2450 10

7839 5000 10

7934 1300 10

7566 2975 20

10 8750

EMPNO SAL DEPTNO

---------- ---------- ----------

7782 2450 10

7839 5000 10

7934 1300 10

7566 2975 20

7902 3000 20

7876 1100 20

7369 800 20

162

DEPTNO SUM(SAL)

---------- ----------

10 8750

20 10875

30 9400

29025

7782 2450 10

7839 5000 10

7934 1300 10

7566 2975 20

10 8750

7/17/2018

106

EMPNO SAL DEPTNO

---------- ---------- ----------

7782 2450 10

7839 5000 10

7934 1300 10

7566 2975 20

7902 3000 20

7876 1100 20

7369 800 20

162

DEPTNO SUM(SAL)

---------- ----------

10 8750

20 10875

30 9400

29025

7782 2450 10

7839 5000 10

7934 1300 10

7566 2975 20

10 8750

from 2 to 1

163

7/17/2018

107

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

29025164

the whole lot !

165

7/17/2018

108

SQL> select deptno,job,sum(sal) from scott.emp

2 group by CUBE(deptno,job)

3 order by deptno,job;

166

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

166

7/17/2018

109

totally customisable

167

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

168

7/17/2018

110

169

170

7/17/2018

111

171

"talking" to your database

... makes it faster

172

example

7/17/2018

112

173

STORES

CUSTOMERS

SALES

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

174

7/17/2018

113

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

174

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 |

---------------------------------------------------

175

7/17/2018

114

176

can we do better ?

176

can we do better ?

add indexes ?

rewrite query ?

result cache ?

materialized view ?

7/17/2018

115

176

can we do better ?

add indexes ?

rewrite query ?

result cache ?

materialized view ?

177

share your knowledge with the db

oc04.sql

7/17/2018

116

wrap up

178

SQL and PL/SQL

179

7/17/2018

117

very cool

180

very powerful

181

7/17/2018

118

less code

182

never too early to start

183

7/17/2018

119

7/17/2018

120

Enjoy the conference !!!

youtube tinyurl.com/connor-tube

blog connor-mcdonald.com

twitter @connor_mc_d

sql and pl/sql - aioug.org · sql> declare 2 c_var_length constant pls_integer := 30; 3 l_str...

Documents