the features that (maybe) you didn't know about

The Features That maybe

You Didn’t Know About

Oren Nakdimon

www.db-oriented.com

[email protected]

+972-54-4393763

@DBoriented

This presentation is available in http://db-oriented.com/presentations © O

ren N

akdim

on

WHO AM I? A CHRONOLOGY BY “ORACLE YEARS”

Where: IAF

When: Oracle 6/7 [1991-1997]

What: Developer

Where: Golden Screens

When: Oracle 8 [1997-1998]

What: Server Group Manager

Where: TELEknowledge

When: Oracle 8i/9i [1998-2003]

What: DBA Group Manager

Where: Olista

When: Oracle 10g/11g [2004-2011]

What: VP R&D + Israel Site Manager Where:

When: Oracle 11g/12c [2011-]

What: Freelance Consultant

Where:

When: 2012-

What: Database

Architect / Developer / DBA

http://db-oriented.com/presentations




ren N

akdim

on

@DBORIENTED





ren N

akdim

on

HTTP://DB-ORIENTED.COM





ren N

akdim

on

Join ilOUG meetup group

Download presentations

Give us your feedback

About this meeting

What do you want to hear and see next?

Initiate and participate in discussions

Propose your presentation for upcoming meetings

http://www.meetup.com/ilOUG-Oracle-Technologies-Meetup/

[email protected]












ren N

akdim

on

SQL





ren N

akdim

on

Aggregate

Functions





ren N

akdim

on

select department_id,

MIN(salary)

from employees

group by department_id;

DEPARTMENT_ID EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY

30 117 Sigal Tobias 2800.00

30 119 Karen Colmenares 2500.00

30 118 Guy Himuro 2600.00

90 102 Lex De Haan 17000.00

90 101 Neena Kochhar 17000.00

30 115 Alexander Khoo 3100.00

60 104 Bruce Ernst 6000.00

30 116 Shelli Baida 2900.00

60 106 Valli Pataballa 4800.00

30 114 Den Raphaely 11000.00

90 100 Steven King 24000.00

60 103 Alexander Hunold 9000.00

60 105 David Austin 4800.00

60 107 Diana Lorentz 4200.00





ren N

akdim

on


MIN(salary)

from employees




30 118 Guy Himuro 2600.00




30 114 Den Raphaely 11000.00




60 104 Bruce Ernst 6000.00



90 102 Lex De Haan 17000.00

90 100 Steven King 24000.00

DEPARTMENT_ID MIN(SALARY)

30 2500.00

60 4200.00

90 17000.00





ren N

akdim

on



30 118 Guy Himuro 2600.00




30 114 Den Raphaely 11000.00




60 104 Bruce Ernst 6000.00



90 102 Lex De Haan 17000.00

90 100 Steven King 24000.00





ren N

akdim

on

Aggregate

Functions

FIRST





ren N

akdim

on

THE FIRST FUNCTION



30 118 Guy Himuro 2600.00




30 114 Den Raphaely 11000.00




60 104 Bruce Ernst 6000.00



90 102 Lex De Haan 17000.00

90 100 Steven King 24000.00

select

department_id,

min(first_name) keep(dense_rank FIRST order by salary)

from employees






ren N

akdim

on

THE FIRST FUNCTION



30 118 Guy Himuro 2600.00




30 114 Den Raphaely 11000.00




60 104 Bruce Ernst 6000.00



90 102 Lex De Haan 17000.00

90 100 Steven King 24000.00

select

department_id,

min(first_name) keep(dense_rank FIRST order by salary)

from employees


DEPARTMENT_ID MIN(FIRST_NAME)…

30 Karen

60 Diana

90 Lex





ren N

akdim

on

Aggregate

Functions

LAST





ren N

akdim

on

THE LAST FUNCTION



30 118 Guy Himuro 2600.00




30 114 Den Raphaely 11000.00




60 104 Bruce Ernst 6000.00



90 102 Lex De Haan 17000.00

90 100 Steven King 24000.00

select

department_id,

min(first_name) keep(dense_rank LAST order by salary)

from employees


DEPARTMENT_ID MIN(FIRST_NAME)…

30 Den

60 Alexander

90 Steven





ren N

akdim

on

WHAT IS THE MOST COMMON JOB IN EACH DEPARTMENT?


min(job_id) keep(dense_rank last order by cnt)

from (select department_id,

job_id,

count(*) cnt

from employees

group by department_id,

job_id)


DEPARTMENT_ID JOB_ID CNT

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

90 AD_VP 2

30 PU_CLERK 5

60 IT_PROG 5

90 AD_PRES 1

30 PU_MAN 1





ren N

akdim

on



min(job_id) keep(dense_rank last order by cnt)

from (select department_id,

job_id,

count(*) cnt

from employees

group by department_id,

job_id)


DEPARTMENT_ID MIN(JOB_ID)KEEP(DENSE_RANKLAST

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

30 PU_CLERK

60 IT_PROG

90 AD_VP





ren N

akdim

on

Aggregate

Functions

STATS_MODE





ren N

akdim

on



stats_mode(job_id)

from employees


DEPARTMENT_ID MIN(JOB_ID)KEEP(DENSE_RANKLAST

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

30 PU_CLERK

60 IT_PROG

90 AD_VP





ren N

akdim

on

Collections





ren N

akdim

on

DISTINCT COUNT OF A VALUE ACROSS COLUMNS IN A TABLE

I have this table: select * from country_test;

c1 c2 c3 c4

————— ————— ————— ——————

india us china uk

india india china uk

india china china uk

us us us uk

I need the distinct count of countries across the c1,c2,c3,c4 columns of the table, so the output has to be c1 c2 c3 c4 cnt

————— ————— ————— ——— ————

india us china uk 4

india india china uk 3

india china china uk 3

us us us uk 2

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8749607800346631637





ren N

akdim

on


ops$tkyte%ORA11GR2> with data(r)

2 as

3 (select 1 r from dual

4 union all

5 select r+1 from data where r < 4

6 )

7 select c1, c2, c3, c4, count(distinct c) cnt

8 from (

9 select rowid rid,

10 c1, c2, c3, c4,

11 decode(r,1,c1,2,c2,3,c3,4,c4) c

12 from data, country_test

13 )

14 group by rid, c1, c2, c3, c4

15 /

C1 C2 C3 C4 CNT

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

india us china uk 4

us us us uk 2



http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8749607800346631637





ren N

akdim

on


with data as (select rownum r,c1,c2,c3,c4

from ctest)

select listagg(rpad(val,21),'') within

group (order by column_list) orig

,count(distinct val) countries

from

(

select * from data

unpivot (val for column_list in

(c1,c2,c3,c4))

)

group by r

order by r;

select * from country_test,

lateral(

select count(distinct c) cnt from (

select c1 c from dual union all

select c2 from dual union all

select c3 from dual union all

select c4 from dual

)

);

select country_test.*, cnt_tab.cnt

from country_test,

(

select count(1) cnt, rid

from (

select rowid rid, c1 c from country_test

union select rowid, c2 from country_test



)

group by rid

) cnt_tab

where country_test.rowid = cnt_tab.rid

select * from country_test,

lateral(

select count(distinct val) cnt from (

select c1,c2,c3,c4 from dual

) unpivot(val for col in (c1,c2,c3,c4))

);





ren N

akdim

on


create type string_ntt as

table of varchar2(4000)





ren N

akdim

on

Collections

Default

Constructor





ren N

akdim

on




select

string_ntt('John','Paul','George','Ringo') beatles

from dual;

BEATLES

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

STRING_NTT('John', 'Paul', 'George', 'Ringo')





ren N

akdim

on




select

c.*,

cardinality(set(string_ntt(c1,c2,c3,c4)))

from country_test c;

C1 C2 C3 C4 STRING_NTT(C1,C2,C3,C4)

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

india us china uk STRING_NTT('india', 'us', 'china', 'uk')

india india china uk STRING_NTT('india', 'india', 'china', 'uk')

india china china uk STRING_NTT('india', 'china', 'china', 'uk')

us us us uk STRING_NTT('us', 'us', 'us', 'uk')





ren N

akdim

on

Collections

SET





ren N

akdim

on


select

c.*,



C1 C2 C3 C4 SET(STRING_NTT(C1,C2,C3,C4))

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


india india china uk STRING_NTT('india', 'china', 'uk')

india china china uk STRING_NTT('india', 'china', 'uk')

us us us uk STRING_NTT('us', 'uk')


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









ren N

akdim

on

Collections

CARDINALITY





ren N

akdim

on


select

c.*,



C1 C2 C3 C4 CARDINALITY(SET(STRING_NTT(C1,C2,C3,C4)))

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

india us china uk 4



us us us uk 2

C1 C2 C3 C4 SET(STRING_NTT(C1,C2,C3,C4))

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


india india china uk STRING_NTT('india', 'china', 'uk')

india china china uk STRING_NTT('india', 'china', 'uk')

us us us uk STRING_NTT('us', 'uk')


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









ren N

akdim

on

Collections

Unnesting





ren N

akdim

on

COLLECTION UNNESTING

select *

from

table(string_ntt('John','Paul','George','Ringo'));

COLUMN_VALUE

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

John

Paul

George

Ringo





ren N

akdim

on

KAKURO

Digits 1-9

Sum = associated clue

No duplications

6 = 1+2+3

12 = 1+2+3+6

12 = 1+2+4+5

http://db-oriented.com/2016/06/10/kakuro-helper-using-sql-query-with-the-powermultiset-function/





ren N

akdim

on

> select *

from table(powermultiset(integer_ntt(1,2,3,4,5,6,7,8,9)));

COLUMN_VALUE

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

create type integer_ntt as table of integer





ren N

akdim

on

Collections

POWERMULTISET





ren N

akdim

on

> select *

from table(powermultiset(integer_ntt(1,2,3,4,5,6,7,8,9)));

COLUMN_VALUE

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

INTEGER_NTT(1)

INTEGER_NTT(2)

INTEGER_NTT(1, 2)

INTEGER_NTT(3)

INTEGER_NTT(1, 3)

INTEGER_NTT(2, 3)

INTEGER_NTT(1, 2, 3)

INTEGER_NTT(4)

INTEGER_NTT(1, 4)

INTEGER_NTT(2, 4)


INTEGER_NTT(3, 4)



INTEGER_NTT(1, 2, 3, 4)

INTEGER_NTT(5)

...

INTEGER_NTT(1, 3, 4, 5, 6, 7, 8, 9)

INTEGER_NTT(2, 3, 4, 5, 6, 7, 8, 9)

INTEGER_NTT(1, 2, 3, 4, 5, 6, 7, 8, 9)

511 rows selected.

create type integer_ntt as table of integer





ren N

akdim

on

break on x on num_of_elements skip 1

select sum(b.column_value) x,

a.num_of_elements,

listagg(b.column_value,'+') within group(order by b.column_value) expr

from (select rownum id ,

cardinality(column_value) num_of_elements,

column_value combination

from table(powermultiset(integer_ntt(1,2,3,4,5,6,7,8,9)))) a,

table(a.combination) b

where a.num_of_elements > 1

group by a.id,a.num_of_elements

order by x,num_of_elements,expr;

ID NUM_OF_ELEMENTS COMBINATION

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

1 1 INTEGER_NTT(1)

2 1 INTEGER_NTT(2)

3 2 INTEGER_NTT(1, 2)

4 1 INTEGER_NTT(3)



7 3 INTEGER_NTT(1, 2, 3)

...

508 7 INTEGER_NTT(3, 4, 5, 6, 7, 8, 9)

509 8 INTEGER_NTT(1, 3, 4, 5, 6, 7, 8, 9)

510 8 INTEGER_NTT(2, 3, 4, 5, 6, 7, 8, 9)

511 9 INTEGER_NTT(1, 2, 3, 4, 5, 6, 7, 8, 9)

511 rows selected.






ren N

akdim

on



a.num_of_elements,

select a.*,b.column_value









ID NUM_OF_ELEMENTS COMBINATION COLUMN_VALUE

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

1 1 INTEGER_NTT(1) 1


3 2 INTEGER_NTT(1, 2) 1







7 3 INTEGER_NTT(1, 2, 3) 1

7 3 INTEGER_NTT(1, 2, 3) 2

7 3 INTEGER_NTT(1, 2, 3) 3

...

2304 rows selected.


1+2+3=6

1+2=3





ren N

akdim

on



a.num_of_elements,











X NUM_OF_ELEMENTS EXPR

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

3 2 1+2

4 2 1+3

5 2 1+4

2+3

6 2 1+5

2+4

3 1+2+3

7 2 1+6

2+5

3+4

3 1+2+4


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

41 7 2+4+5+6+7+8+9

8 1+2+3+5+6+7+8+9

42 7 3+4+5+6+7+8+9

8 1+2+4+5+6+7+8+9

43 8 1+3+4+5+6+7+8+9

44 8 2+3+4+5+6+7+8+9

45 9 1+2+3+4+5+6+7+8+9

502 rows selected.





ren N

akdim

on



a.num_of_elements,











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

3 2 1+2

4 2 1+3

5 2 1+4

2+3

6 2 1+5

2+4

3 1+2+3

7 2 1+6

2+5

3+4

3 1+2+4


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

41 7 2+4+5+6+7+8+9

8 1+2+3+5+6+7+8+9

42 7 3+4+5+6+7+8+9

8 1+2+4+5+6+7+8+9

43 8 1+3+4+5+6+7+8+9

44 8 2+3+4+5+6+7+8+9

45 9 1+2+3+4+5+6+7+8+9

502 rows selected.






ren N

akdim

on

PIVOT





ren N

akdim

on

break on x skip 1

select * from (


a.num_of_elements,









)

pivot (listagg(expr, chr(10)) within group (order by expr)

for num_of_elements in(2,3,4,5,6,7,8,9));


X 2 3 4 5 6 7 8 9

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

3 1+2

4 1+3

5 1+4

2+3





ren N

akdim

on

pivot (listagg(expr, chr(10)) within group (order by expr)

for num_of_elements in(2,3,4,5,6,7,8,9));


X 2 3 4 5 6 7 8 9

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

3 1+2

4 1+3

5 1+4

2+3

6 1+5 1+2+3

2+4

21 4+8+9 1+3+8+9 1+2+3+6+9 1+2+3+4+5+6

5+7+9 1+4+7+9 1+2+3+7+8

6+7+8 1+5+6+9 1+2+4+5+9

1+5+7+8 1+2+4+6+8

2+3+7+9 1+2+5+6+7

2+4+6+9 1+3+4+5+8

2+4+7+8 1+3+4+6+7

2+5+6+8 2+3+4+5+7

3+4+5+9

3+4+6+8

3+5+6+7

44 2+3+4+5+6+7+8+9

45 1+2+3+4+…





ren N

akdim

on

Recursive

Subquery

Factoring





ren N

akdim

on

RECURSIVE SUBQUERY FACTORING with fib(x,f) as (

select 1 as x, 1 as f from dual

union all

select f, x+f from fib where x+f <= &n

)

select f

from fib;

anchor member





ren N

akdim

on



union all


)

select f

from fib;

anchor member

recursive member





ren N

akdim

on



union all


)

select f

from fib;

Enter value for n: 121

F

----------

1

2

3

5

8

13

21

34

55

89

10 rows selected.

anchor member

recursive member





ren N

akdim

on

Pattern

Matching





ren N

akdim

on

THE MOST BASIC SQL

Row-level visibility

Maximum one output row per input row

WHERE clause





ren N

akdim

on

AGGREGATE FUNCTIONS

Group-level visibility

Strict definition of “group”

Each input row belongs to exactly one group

Maximum one output row per group

GROUP BY clause

HAVING clause





ren N

akdim

on

Window-level visibility

Strict definition of “window”

Each input row has its own window

Window-level aggregates are added to input rows

OVER

PARTITION BY

ORDER BY

ANALYTIC (WINDOW) FUNCTIONS





ren N

akdim

on

ANALYTIC (WINDOW) FUNCTIONS

Window-level visibility

Strict definition of “window”

Each input row has its own window

Window-level aggregates are added to input rows

OVER

PARTITION BY

ORDER BY





ren N

akdim

on

PATTERN MATCHING

Enhanced analysis of row sequences

Match-based output

One row per match (similar to the “group by” concept)

or

All the match’s input rows (similar to the “window” concept)

Each input row may belong to 0, 1 or more matches





ren N

akdim

on

PATTERN MATCHING

Zeckendorf's theorem

Every positive integer can be represented uniquely as the sum of distinct

non-consecutive Fibonacci numbers, and this representation can be found by

using a greedy algorithm, choosing the largest possible Fibonacci number at

each stage.

6 = 5 + 1 122 = 89 + 21 + 8 + 3 + 1 17 = 13 + 3 + 1 34 = 34

http://marogel.wordpress.com/2015/05/22/a-greedy-algorithm-using-recursive-subquery-factoring/





ren N

akdim

on

PATTERN MATCHING


with fib(x,f) as (


union all


)

select f

from fib

match_recognize(

order by f desc

all rows per match

pattern ((A|{-B-})+)

define A as sum(A.f) <= &n

)

F

1

2

3

5

8

13

21

34

55

89

n = 121





ren N

akdim

on

PATTERN MATCHING


with fib(x,f) as (


union all


)

select f

from fib

match_recognize(

order by f desc

all rows per match



)

F

89

55

34

21

13

8

5

3

2

1

n = 121





ren N

akdim

on

PATTERN MATCHING


with fib(x,f) as (


union all


)

select f

from fib

match_recognize(

order by f desc

all rows per match



)

F

89

55

34

21

13

8

5

3

2

1

A

n = 121





ren N

akdim

on

PATTERN MATCHING


with fib(x,f) as (


union all


)

select f

from fib

match_recognize(

order by f desc

all rows per match



)

F

89

21

8

3

A

n = 121





ren N

akdim

on

Oracle

Locator





ren N

akdim

on

create table stops (

stop_id varchar2(20) constraint stops_pk primary key,

stop_code varchar2(20),

stop_name varchar2(100),

stop_location mdsys.sdo_geometry

);

stops.txt

insert into user_sdo_geom_metadata

(table_name,column_name,diminfo,srid)

values ('STOPS',

'STOP_LOCATION',

mdsys.sdo_dim_array(

mdsys.sdo_dim_element('LONG', -180.0, 180.0, 0.05),

mdsys.sdo_dim_element('LAT', -90.0, 90.0, 0.05)),

8307);





ren N

akdim

on

CREATE TABLE STOPS_EXT (stop_id varchar2(20),

stop_code varchar2(20),

stop_name varchar2(100),

stop_desc varchar2(100),

stop_lat number,

stop_lon number,

location_type number(1),

parent_location varchar2(20),

zone_id number)

ORGANIZATION EXTERNAL (

TYPE ORACLE_LOADER

DEFAULT DIRECTORY EXT_TABLES_DIR

ACCESS PARAMETERS (

records delimited by 0x'0d0a'

characterset UTF8

skip 1

logfile EXT_TABLES_DIR:'stops_%p_%a.log'

badfile EXT_TABLES_DIR:'stops_%p_%a.txt'

fields terminated by ',' optionally enclosed by '"'

missing field values are null

reject rows with all null fields

)

LOCATION ('stops.txt')

)

REJECT LIMIT UNLIMITED;





ren N

akdim

on

insert /*+ append */ into stops (

stop_id,

stop_code,

stop_name,

stop_location)

select stop_id,

stop_code,

stop_name,

mdsys.sdo_geometry(2001, -- 2 dimensional point

8307, -- lat/long coordinate system

mdsys.sdo_point_type(stop_lon,

stop_lat,

null),

null, -- n/a for point type

null) -- n/a for point type

from stops_ext;

commit;





ren N

akdim

on

External

Tables





ren N

akdim

on

insert /*+ append */ into stops (

stop_id,

stop_code,

stop_name,

stop_location)

select stop_id,

stop_code,

stop_name,

mdsys.sdo_geometry(2001, -- 2 dimensional point

8307, -- lat/long coordinate system

mdsys.sdo_point_type(stop_lon,

stop_lat,

null),

null, -- n/a for point type

null) -- n/a for point type

from stops_ext;

commit;

create index stops_location_idx

on stops (stop_location)

indextype is mdsys.spatial_index

parameters ('layer_gtype=POINT');





ren N

akdim

on

(34.863356, 32.101307)





ren N

akdim

on

STOP_CODE STOP_NAME LONGITUDE LATITUDE

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

אימבר/השפלה 33080 34.860736 32.097785

השפלה/דרך אם המושבות 30990 34.858287 32.101256

32.104148 34.86375 תחנת רכבת קרית אריה 35378

חנה וסע/רכבת קריית אריה 31424 34.863596 32.105353

דרך זבולון המר/דרך אם המושבות 38334 34.867824 32.10336

משה דיין/אבשלום גיסין 38143 34.86262 32.096893

בזל/אבשלום גיסין 32172 34.864442 32.097726

בזל/אבשלום גיסין 32231 34.866672 32.098263

8 rows selected.

Elapsed: 00:00:00.13

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 275 | 16775 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | STOPS | 275 | 16775 | 3 (0)| 00:00:01 |

|* 2 | DOMAIN INDEX (SEL: 0.100000 %)| STOPS_LOCATION_IDX | | | 3 (0)| 00:00:01 |

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

SELECT s.stop_code,

s.stop_name,

s.stop_location.sdo_point.x longitude,

s.stop_location.sdo_point.y latitude

FROM stops s

WHERE sdo_within_distance(

s.stop_location,

sdo_geometry(2001,8307,

sdo_point_type(34.863356, 32.101307, NULL), NULL, NULL),

'distance=500 unit=meter') = 'TRUE';

All the stops within 500

meters





ren N

akdim

on

GEOMETRY AGGREGATION

SELECT sdo_util.to_wktgeometry(

sdo_aggr_union(sdoaggrtype(s.stop_location,0.05)))

FROM stops s


s.stop_location,








ren N

akdim

on

GEOMETRY AGGREGATION

SELECT sdo_util.to_wktgeometry(

sdo_aggr_union(sdoaggrtype(s.stop_location,0.05)))

FROM stops s


s.stop_location,




MULTIPOINT ((34.866672 32.098263), (34.864442 32.097726), (34.86262

32.096893), (34.867824 32.10336), (34.863596 32.105353), (34.86375

32.104148), (34.858287 32.101256), (34.860736 32.097785))





ren N

akdim

on

MULTIPOINT ((34.866672 32.098263), (34.864442 32.097726), (34.86262

32.096893), (34.867824 32.10336), (34.863596 32.105353), (34.86375

32.104148), (34.858287 32.101256), (34.860736 32.097785))





ren N

akdim

on

SELECT s.stop_code,

s.stop_name,

s.stop_location.sdo_point.x longitude,

s.stop_location.sdo_point.y latitude

FROM stops s

WHERE sdo_nn(

s.stop_location,



'sdo_num_res=3') = 'TRUE';

STOP_CODE STOP_NAME LONGITUDE LATITUDE

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

32.104148 34.86375 תחנת רכבת קרית אריה 35378

בזל/אבשלום גיסין 32172 34.864442 32.097726

חנה וסע/רכבת קריית אריה 31424 34.863596 32.105353

3 rows selected.

Elapsed: 00:00:00.21

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


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


| 1 | TABLE ACCESS BY INDEX ROWID | STOPS | 28 | 1708 | 3 (0)| 00:00:01 |

|* 2 | DOMAIN INDEX (SEL: 0.100000 %)| STOPS_LOCATION_IDX | | | 3 (0)| 00:00:01 |

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

3 nearest stops





ren N

akdim

on

SELECT round(sdo_nn_distance(1)) dist_meters,

s.stop_code,

s.stop_name

FROM stops s

WHERE sdo_nn(

s.stop_location,



'sdo_num_res=3',1) = 'TRUE'

order by dist_meters;

DIST_METERS STOP_CODE STOP_NAME

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

תחנת רכבת קרית אריה 35378 317

בזל/אבשלום גיסין 32172 410

חנה וסע/רכבת קריית אריה 31424 449

3 rows selected.

Elapsed: 00:00:00.06

3 nearest stops + their

distance





ren N

akdim

on

COUNTRIES AND POLYGONS

> desc countries

Name Null? Type

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

COUNTRY_ID NUMBER

COUNTRY_NAME VARCHAR2(100)

POLYGON MDSYS.SDO_GEOMETRY





ren N

akdim

on

WHICH COUNTRY CONTAINS OUR POINT?

COUNTRY_NAME

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

Israel

1 row selected.

Elapsed: 00:00:00.03

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


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


| 1 | TABLE ACCESS BY INDEX ROWID | COUNTRIES | 2 | 48 | 2 (0)| 00:00:01 |

|* 2 | DOMAIN INDEX (SEL: 0.100000 %)| COUNTRIES_POLYGON_IDX | | | 2 (0)| 00:00:01 |

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

select c.country_name

from countries c

where sdo_relate(

c.polygon,



'mask=contains') = 'TRUE';





ren N

akdim

on

Online

Operations





ren N

akdim

on

ONLINE DDL OPERATIONS

Offline Operations Online Operations





ren N

akdim

on



Get ORA-54 due to active transactions

Wait for active transactions to end





ren N

akdim

on



Get ORA-54 due to active transactions

Wait for active transactions to end

Block new DML statements Do not block new DML statements





ren N

akdim

on

THE KEYWORD ONLINE

11g

CREATE INDEX … [ONLINE]

ALTER INDEX … REBUILD [ONLINE]

12c

ALTER TABLE … DROP CONSTRAINT … [ONLINE]

ALTER TABLE … SET UNUSED … [ONLINE]

DROP INDEX … [ONLINE]

ALTER INDEX … UNUSABLE [ONLINE]





ren N

akdim

on

ONLINE OPERATIONS

12c

ALTER INDEX … INVISIBLE

ALTER INDEX … VISIBLE





ren N

akdim

on

Invisible

Indexes





ren N

akdim

on

INVISIBLE INDEXES

Maintained by DML

Invisible to the optimizer

Unless optimizer_use_invisible_indexes is true





ren N

akdim

on

ONLINE OPERATIONS

Adding a new column to a non-empty table

An optional column with no default

As of 11g, adding a mandatory column with default is a meta-data only operation: Fast

No space

No redo

No undo

Online

As of 12c, the same is true also for optional columns





ren N

akdim

on

“ALMOST ONLINE” OPERATIONS

> drop index t_idx;

drop index t_idx

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

or timeout expired

Elapsed: 00:00:00.00





ren N

akdim

on

DDL_LOCK_TIMEOUT





ren N

akdim

on


> alter session set ddl_lock_timeout=2;

Session altered.

> drop index t_idx;

drop index t_idx

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

or timeout expired

Elapsed: 00:00:02.02





ren N

akdim

on


> alter session set ddl_lock_timeout=2;

Session altered.

> drop index t_idx;

Index dropped.

Elapsed: 00:00:00.95





ren N

akdim

on


ALTER TABLE ADD CONSTRAINT





ren N

akdim

on

Online

Operations

ENABLE

NOVALIDATE





ren N

akdim

on


ALTER TABLE ADD CONSTRAINT

ALTER TABLE ADD CONSTRAINT ENABLE NOVALIDATE

ALTER TABLE ENABLE VALIDATE CONSTRAINT





ren N

akdim

on

INDEX REBUILD?

index on T(CREATION_TIME)

select … from T where CREATION_TIME between …





ren N

akdim

on

Index

Coalesce





ren N

akdim

on

INDEX REBUILD?

index on T(CREATION_TIME)

select … from T where CREATION_TIME between …





ren N

akdim

on

Edition

Based

Redefinition





ren N

akdim

on

Procedure P

Procedure P

My Schema

Edition1

Edition2

procedure p is

begin

-- do something

end p;

create or replace

procedure p as

begin

-- do something else

end p;





ren N

akdim

on

Procedure

P

My Schema

Edition1

Edition2

Function

F

Procedure

P

View

V





ren N

akdim

on

Procedure

P

My Schema

Edition1

Edition2

Function

F

Procedure

P

View

V

Edition3 Function

F

Package

PKG





ren N

akdim

on

Procedure

P

My Schema

Edition1

procedure p is

...

select name

into ...

from people

...

Table

PEOPLE - id

- name





ren N

akdim

on

Procedure

P

My Schema

Edition1

Edition2

Edition3 Procedure

P

procedure p is

...

select name

into ...

from people

...

Table

PEOPLE - id

- name

- first_name

- last_name procedure p is

...

select

first_name,

last_name

into ...

from people

...





ren N

akdim

on

Procedure

P

My Schema

Edition1

Edition2

Edition3 Procedure

P

Editioning

View

PEOPLE

Editioning

View

PEOPLE

Table

PEOPLE$T - id

- name

- first_name

- last_name

create editioning view people

as select id, name

from people$t

create editioning view people

as select id, first_name, last_name

from people$t

select name

into ...

from people

select

first_name, last_name

into ...

from people





ren N

akdim

on

I LOVE EBR BECAUSE…

It enables to apply any change in an online fashion

The upgrade is performed in the privacy of a new unexposed edition

The upgrade can be done at any time

Supported everywhere (since Oracle 11.2), including standard edition





ren N

akdim

on

Partition

Views





ren N

akdim

on

PARTITION VIEWS

T1

T2

T3

T4 alter table add partition create table

create or replace view

drop table alter table drop partition





ren N

akdim

on

PARTITION VIEWS

> desc events_01

Name Null? Type

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

EVENT_ID NOT NULL NUMBER

EVENT_TIME NOT NULL DATE

EVENT_TYPE_ID NOT NULL NUMBER

DETAILS VARCHAR2(100)

create or replace view events as

select * from events_01

where event_time >= date'2016-01-01'

and event_time < date'2016-02-01'

union all




union all








ren N

akdim

on

PARTITION VIEWS

select event_type_id,count(*)

from events

where event_time between date'2016-02-20' and date'2016-02-22'

group by event_Type_id;

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


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


| 1 | HASH GROUP BY | | 7 | 21 | 19 (6)| 00:00:01 |

| 2 | VIEW | EVENTS | 2884 | 8652 | 18 (0)| 00:00:01 |

| 3 | UNION-ALL | | | | | |

|* 4 | FILTER | | | | | |

| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| EVENTS_01 | 1 | 11 | 3 (0)| 00:00:01 |

|* 6 | INDEX RANGE SCAN | EVENT_01_TIME_IDX | 1 | | 2 (0)| 00:00:01 |

| 7 | TABLE ACCESS BY INDEX ROWID BATCHED | EVENTS_02 | 2882 | 31702 | 18 (0)| 00:00:01 |


|* 9 | FILTER | | | | | |

| 10 | TABLE ACCESS BY INDEX ROWID BATCHED| EVENTS_03 | 1 | 11 | 3 (0)| 00:00:01 |


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

Predicate Information (identified by operation id):

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

4 - filter(NULL IS NOT NULL)

6 - access("EVENT_TIME">=TO_DATE(' 2016-02-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

"EVENT_TIME“ <TO_DATE(' 2016-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


"EVENT_TIME"<=TO_DATE(' 2016-02-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))








ren N

akdim

on

PARTITION VIEWS

select count(details)

from events


and event_type_id = 1;

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


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


| 1 | SORT AGGREGATE | | 1 | 74 | | |

| 2 | VIEW | EVENTS | 12366 | 893K| 79 (2)| 00:00:01 |

| 3 | UNION-ALL | | | | | |

|* 4 | TABLE ACCESS FULL | EVENTS_01 | 6367 | 99K| 40 (0)| 00:00:01 |

|* 5 | TABLE ACCESS FULL | EVENTS_02 | 5998 | 95968 | 38 (0)| 00:00:01 |

|* 6 | FILTER | | | | | |

|* 7 | TABLE ACCESS BY INDEX ROWID BATCHED| EVENTS_03 | 1 | 16 | 3 (0)| 00:00:01 |


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


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

4 - filter("EVENT_TYPE_ID"=1 AND

"EVENT_TIME">=TO_DATE(' 2016-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

"EVENT_TIME"< TO_DATE(' 2016-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


"EVENT_TIME"<=TO_DATE(' 2016-02-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

"EVENT_TIME">=TO_DATE(' 2016-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


7 - filter("EVENT_TYPE_ID"=1)







ren N

akdim

on

PARTITION VIEWS

select count(details)

from events


and event_type_id = 6;

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


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


| 1 | SORT AGGREGATE | | 1 | 74 | | |

| 2 | VIEW | EVENTS | 2418 | 174K| 42 (0)| 00:00:01 |

| 3 | UNION-ALL | | | | | |

|* 4 | TABLE ACCESS FULL | EVENTS_01 | 2388 | 38208 | 40 (0)| 00:00:01 |

|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED | EVENTS_02 | 29 | 464 | 2 (0)| 00:00:01 |

|* 6 | INDEX RANGE SCAN | EVENT_02_TYPE_IDX | 41 | | 1 (0)| 00:00:01 |

|* 7 | FILTER | | | | | |

|* 8 | TABLE ACCESS BY INDEX ROWID BATCHED| EVENTS_03 | 1 | 16 | 2 (0)| 00:00:01 |

|* 9 | INDEX RANGE SCAN | EVENT_03_TYPE_IDX | 1 | | 1 (0)| 00:00:01 |

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


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


"EVENT_TIME">=TO_DATE(' 2016-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

"EVENT_TIME“ <TO_DATE(' 2016-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

5 - filter("EVENT_TIME"<=TO_DATE(' 2016-02-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND


6 - access("EVENT_TYPE_ID"=6)


8 - filter("EVENT_TIME"<=TO_DATE(' 2016-02-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND


9 - access("EVENT_TYPE_ID"=6)





ren N

akdim

on

PARTITION VIEWS

It’s our responsibility to insert into the right partition

EXCHANGE PARTITION is supported

Indexing

Local indexes are supported by definition

Global indexes are not (easily) supported

Partial indexes are supported by definition




THANK YOU

Oren Nakdimon

www.db-oriented.com

[email protected]

+972-54-4393763

@DBoriented

the features that (maybe) you didn't know about

Technology