the very very latest in database development - lucas jellema - oracle openworld 2012

THE VERY, VERY LATEST IN ORACLE DATABASE DEVELOPMENT

Lucas Jellema (AMIS, The Netherlands)

Oracle Open World 2012, San Francisco

THE VERY VERY VERY LATEST…

<Secret Code>

THE DATABASE IN MODERN ARCHITECTURES

Oracle Open World 2012, San Francisco

NO SQL

THE TOP-3 EARNING EMPLOYEES

• What can you say about the result of this query with respect to the question: “Who are our top three earning employees?”

A. Correct AnswerB. Sometimes correctC. Correct if there are never duplicate

salariesD. Not Correct

IN-LINE VIEWS

SPECIAL ‘BUSINESS RULE’: DEFAULT VALUE• The default values is the value that should be

inserted for a column when the client has ignored the column– not provided a value nor indicated NULL

• The default value is applied prior to the execution of the Before Row trigger– So :new.<column_value> has the value that will

be inserted– The Before Row trigger has no built in way to

telling whether the value was provided by the client or supplied as default by the database

• Default value is typically used for auditing purposes– Note: default values for columns exposed in UI

should be set in the client

COLUMN DEFAULT

• Columns can have default values– Static or literals– SQL expressions evaluating to a static– Pseudo-columns like USER and CURRENT_DATE

• DO NOT USE SYSDATE! DO NOT USE USER!

– References to Application Context parameters• sys_context(‘USERENV’, ‘IP_ADDRESS’)..

– Some funny value to let the before row trigger know that the real (complex) default must be calculated

create table citizens( name varchar2(100) default 'John Doe' , birthdate date default current_date - 1, city varchar2(50) default sys_context('KANE_CTX', 'DEFAULT_CITY' ), zipcode varchar2(8) default 'XYXYXYXYXQQ')

• Memory area that enables application developers to define, set, and access key/value pairs

• Rapid access in SQL and PL/SQL

• Two Application Contexts are always around:– CLIENTCONTEXT and USERENV

APPLICATION CONTEXT

Attribute Value

Attribute Value

Application Context

Attribute ValuePairs

select sys_context('USERENV', 'SESSION_USER')from dual

l_user:= sys_context('USERENV', 'SESSION_USER')

APPLICATION CONTEXT APPEARANCES• Per session (default)

– Stored in UGA, just like package state• Globally Accessible (shared across all

sessions)– Stored in SGA

• Associated with a Client Identifier– Attributes in a Globally Accessible Application

Context can explicitly be tied to the Client Identifier

– And are only accessible to sessions with that Client Identifier

TYPICAL WEB ARCHITECTURE USING CONNECTION POOL

JDBC Connection Pool

Session 1 Session 2 Session 3 Session 4

Package Aglobals

Package B Package C

PACKAGE STATE IS TIED TO DATABASE SESSION

JDBC Connection Pool

Session 1 Session 2 Session 3 Session 4

Package Aglobals

Package B Package C

globals

PACKAGE STATE IS TIED TO DATABASE SESSION – NOT WEB SESSION

JDBC Connection Pool

Session 1 Session 2 Session 3 Session 4

Package Aglobals

Package B Package C

globals

APPLICATION CONTEXT TO RETAIN STATE FOR LIGHT WEIGHT END USERS

JDBC Connection Pool

Session 1 Session 2 Session 3 Session 4

Package Aglobals

Package B Package C

globals ?

APPLICATION CONTEXT TO RETAIN STATE FOR LIGHT WEIGHT END USERS

JDBC Connection Pool

Session 1 Session 2 Session 3 Session 4

Package Aglobals

Package CGlobal Context

globals

globals

USERENV USERENV

APPLICATION CONTEXT TO RETAIN STATE FOR LIGHT WEIGHT END USERS

JDBC Connection Pool

Session 1 Session 2 Session 3 Session 4

Package Aglobals

Package CGlobal Context

globals

globals

USERENV USERENVUSERENV

PACKAGE GLOBALS: THE STATE OF THE PACKAGE IN A SESSION• This state is lost when the package is

recompiled– That is undesirable in a highly available

environmentPackage

PACKAGE GLOBALS CAN BE REPLACED BY APPLICATION CONTEXT

• The Application Context is untouched by recompilation of the package– All ‘globals’ in the application context retain

their valuesPackage

Application Context

EBR TO KILL PLANNED DOWNTIME (BECAUSE OF APPLICATION UPGRADE)

Base ReleaseRelease 2 Release 3

Application XVERSION 1

Application XVERSION 2

TIME TRAVELLING

FLASHBACK

• Introduced in 9i• Based on UNDO• Initially only for recovery• As of 11g – Total Recall option with

Flashback Data Archive– Controlled history keeping

• Look back into history– Query trends (version history)– Difference reporting– Audit trails (Replace journaling tables)

• Require trick for transaction history: WHO?

• Also: when is the start of history?

DATABASE IN MODERN ARCHITECTURE

Database

Cache/Grid(L1, L2, L3)Enterprise

Service Bus

WSMobile

Services

Business Tier

Database

Standard Application

sLegacy

Applications

MULTI TIER ARCHITECTURE

Cache/Grid(L1, L2, L3)Enterprise

Service Bus

WSMobile

Services

Business Tier

Database

DB QRCNHTTP

JMX, JMX

Stored Procedures

HTTP RESTHTTP SOAP

FTP/WEBDAV

JDBCJPA (H/EL)

EncapsulationDecoupling

CachingBusiness Logic

Monitor, Trace, Audit

Authentication & Fine Grained Authorization

APPLICATION ARCHITECTURE:DRIVE APPLICATION FROM META DATA

• Agility• Design Time at Run Time• Define part of the application behavior and

appearance through meta-data (outside the base source code)– The default settings are defined by developers

and deployed along with the application– Read and interpreted at run time– Manipulated and re-read

and re-interpreted at run time• Note: very similar to the way

the database operates:– Data Dictionary is the

meta-data driving the behavior of the database

Application

meta

SEPARATE BASE DATA AND CUSTOMIZED DATA• If a value is changed during site-level

implementation– Or run time customization

• It should be kept apart from the base ‘meta-data’– To prevent overwriting customized data when

the new release arrives– To allow for (temporarily) reverting to base data

• A simple solution: the Complex View with two underlying tables approach– Note: Select…

For Update Ofis not allowed

BaseValues

Customized Values

New release

ORIGINAL_NAME IO trg

REPLACE THE ORIGINAL SINGLE TABLE WITH A TWO-TABLE BASE/CUSTOM SPLIT

• rename <original> to <base>• create table <customizations>

as select * from base where rownum = 0

• create or replace view <original>as select * from <customizations>union allselect * from <base> b left outer join <customizations> c on (b.id = c.id)where c.rowid is null

REPLACE THE ORIGINAL SINGLE TABLE WITH A TWO-TABLE BASE/CUSTOM SPLIT (2)

• create or replace trigger handle_insert_trginstead of insert on originalfor each row begin  insert into <customizations> (col, col2,…) values(:new.col, :new.col2,…);end;

• create or replace trigger handle_update_trginstead of update on originalfor each row begin  update <customizations>  set col = :new.col, …  where id = :new.id  ;  if sql%rowcount = 0   then    insert into <customizations> (id, col, col2,…)      (select id, :new.col, :new.col2 from base where  id = :new.id);  end if; end;

APPLICATION ARCHITECTURE: NO SQL• NO SQL

– Complex SQL is hidden away inside the database

– Cache to not have to query all the time from the database

– … and to not take the overhead of a commit for not so important data

– Process first – in memory, on middle tier (BigData and CEP) - and only persist what is useful

Web Browser

RDBMS

JEE Application ServerNO SQL

SQL

QUERY RESULT CHANGE NOTIFICATION• Continuous Query Notification:

– Send an event when the result set for a query changes

– Background process calls PL/SQL Handler or Java Listener or OCI client when thecommit has occurred

– Event contains rowidof changed rows

• Used for:– Refreshing specific

data caches (middletier, global context)

– (custom) Replication

PL/SQL

Java Listener

CONTINUOUS PROCESSING OF DATA STREAMS USING CQL• Aggregation, Spot deviation, Match on

complex patterns

WHO IS AFRAID OF RED, YELLOW AND BLUE

• Table Events– Column Seq number(5)– Column Payload varchar2(200)

SOLUTION USING LEAD

• With LEAD it is easy to compare a row with its successor(s)– As long as the pattern is fixed, LEAD will suffice

with look_ahead_events as( SELECT e.* , lead(payload) over (order by seq) next_color , lead(payload,2) over (order by seq) second_next_color FROM events e)select seqfrom look_ahead_eventswhere payload ='red' and next_color ='yellow' and second_next_color='blue'

THE SHOPPING ALGORITHM

THE SHOPPING ALGORITHM

• shopForItem Item ( String itemName) {

driveToShop;

Item item = buyItemAtShop ( itemName);

driveHomeFromShop;

return item;

}

GET THIS WEEK’S GROCERIES

getGroceries Item[] ( String[] shoppingList) {

Item[] items = new Item[ shoppingList.length];

for (int i=0; i < shoppingList.length; i++) {

items[i] = shopForItem (shoppingList[i]);

}

return items;

}

PENSION FUND – SEPTEMBER 2012

Employer

Participants

Job & Benefits

><

FETCHING THE DATA OF THE PENSION FUND FOR THE WEB APPLICATION

>< select * from employers where id = < 324>

select * from participants where employer_id = < 324>

select * from benefits where participant_id = <#>

1 record

100s records

10s records

REPORTING ON MANY EMPLOYERS

select * from employers

select * from participants where employer_id = <#>

select * from benefits where participant_id = <#>

10k records

100k records

100s records1 query

100s queries

10k queries

APPLICATION ARCHITECTURE – BULK RETRIEVE • Have the database bulk up the data retrieval• Return Ref Cursor, Types and Collections or

JSON/XML

select * from employerswhere id in <some set> select *

from participants where employer_id in <some set>

select b.* from benefits b join participants p on (p.id = b.participant_id)where p.employer_id in <some set>

Benefits Package

HTTP

JDBC

Other(Email, FTP/File,

XMPP/Chat)

SOA Suite

Oracle Service Bus

PL/SQL package

Table

AQ

View

WebLogic Server Database

Email ServerFile/FTP Server

Chat/IM XMPP Server

XMLDB

EPG

Native DB WebService

XML

Java/JEEOO

APPLICATION ARCHITECTURE – SERVICE ENABLING

Chat/IM XMPP Server

HTTP

JDBC

Other(Email, FTP/File,

XMPP/Chat)

SOA Suite

Oracle Service

Bus

DB

AQ

JMS

EJB

FileFTP

SDO

WS

http

PL/SQL package

Table

8i AQ

11g Native DB WebService

10g EPG

View

JEE Server Database

JAX-WS

ADF BC/SDO WS

EJB/JPA

Email ServerFile/FTP Server

UMS

9i XML DB

XMLTypes

XMLXML & XSD

JSON/ CSV

Ref Cursor

Types & CollJPublisher

WS

utl_file, BFILE,

URITYPE

JMS Queue

XML/JSONRelational/Oracle Type

JMS

Adapters

Pojo

Java App

THE TALKING DATABASE

Details on the Employee. Employee name is Smith, his job is Analyst. He

works in department 20…

EMP

THE TALKING DATABASE

PL/SQLexposed through dbms_epg

httpaudio/mpeg, binary data

BUSINESS RULES

• Data Oriented Rules or Data Constraints• Declarative support in database

– For referential integrity • Order must be for a Customer

– For attribute and tuple rules• Salary must be numeric, • Hiredate may not be in the future, • End date must come after begin date

• No declarative support for complex data rules – across multiple records and tables– A department in France may not have less then

20% female employees– Order items of type weapon may not be part of

an order that ships around Christmas

BUSINESS RULES – WHERE AND HOW TO IMPLEMENT• Criteria:

– Safe– Well performant– Reusable and maintainable– Productive to implement

• Options– Client side

• JavaScript

– Middle-tier• Java, Enterprise Service Bus

– Database• Constraints and triggers are statement level – i/o

transaction level

Database

RDBMS NOT ALWAYS EXCLUSIVELY ACCESSED THROUGH ONE LAYER

SOA, ESB, WebServices

Data Replication & Synchronization

Batch Bulk Processes Standard

Applications

LegacyApplications

11G VIRTUAL COLUMNS

• Add columns to a table based on an expression– Using ‘real’ columns, SQL Function and User

Defined Functions– No data is stored for Virtual

Columns, only meta-data– Virtual Columns can be

indexed

VIRTUAL

alter table empADD (income AS (sal + nvl(comm,0)))

UNIQUENESS RULES USING VIRTUAL COLUMNS• Business Rule:

– Not more than one manager per department

alter table empadd constraint only_one_mgr_in_dept_ukunique (one_mgr_flag)

alter table empADD ( one_mgr_flag as ( case when job ='MANAGER' then deptno end ))

CHALLENGE: ORDERS BELONG TO A CUSTOMER IN ONE OF TWO TABLES• The Orders table contains Order records for

customers – either Dutch or Australian customers

• These customers are stored in two different tables

• Can we implement referential integrity to ensure that the order’s customer exists?

ORDER

CountryCustomer_Id….

OZ_CUSTOMER

IdName

DUTCH_CUSTOMER

IdName

?

USING VIRTUAL COLUMNS IN FOREIGN KEY RELATIONS• A foreign key can be created on a Virtual

Column– That means for example we can have a single

column with some id– And two virtual columns with CASE expressions

that produce NULL or the ID value– With Foreign Keys on the Virtual Columns

ORDER

CountryCustomer_IdDutch_id (VC)Australian_id (VC)

OZ_CUSTOMER

IdName

DUTCH_CUSTOMER

IdName

alter table ordersadd (australian_ctr_id as (case country when 'OZ' then customer_id end))

alter table ordersadd (dutch_ctr_id as (case country when 'NL' then customer_id end))

USING VIRTUAL COLUMNS IN FOREIGN KEY RELATIONS

ORDER

CountryCustomer_IdDutch_id (VC)Australian_id (VC)

OZ_CUSTOMER

IdName

DUTCH_CUSTOMER

IdName

alter table ordersadd constraint odr_dcr_fk foreign key (dutch_ctr_id) references dutch_customer (id)

alter table ordersadd constraint odr_ocr_fk foreign key (australian_ctr_id) references oz_customer (id)

FOREIGN KEY SHOULD ONLY REFER TO CERTAIN RECORDS USING VC• Foreign Key can reference a UK based on a

Virtual Column• That allows a ‘conditional foreign key’ or a

foreign key that can only reference specific records in the referenced table– Only refer to Women in the PEOPLE table for the

Mother Foreign Key– Only refer to Values in the Domain Values table

for the Domain Name == ‘COLORS’

alter table domain_valuesadd (country_value as (case domain_name when 'COUNTRIES' then domain_value end))

alter table domain_valuesadd (country_value as (case domain_name when 'COUNTRIES' then domain_value end))

alter table domain_valuesadd (color_value as (case domain_name when 'COLORS' then domain_value end))

RESTRICTED FOREIGN KEYS USING VIRTUAL COLUMNS

CARS

IDMakeTypeColorYear

DOMAIN_VALUES

IdDomain_NameDomain_ValueColor_ValueGender_ValueOrderStatus_ValueCountry_ValueShipmentMethod_Value

alter table carsadd constraint car_clr_fk foreign key (color) references domain_values (color_value)

LACK OF WATERTIGHTNESSIN TRIGGER BASED RULE VALIDATOIN

• Statement time validation means:

• To prevent leakage we should validate at commit time– Logically correct as transaction is the logical unit– Effects from other sessions between statement and

commit are taken into account• However: Oracle unfortunately does not provide us

with a pre-commit or on-commit trigger• Workarounds:

– Dummy Table with Materialized View On Commit Refresh and Trigger on Materialized View

– Do a soft-commit by calling a package to do the actual commit – that will first do transaction level checks• Supported by a deferred check constraint that is violated

by each operation that potentially violates a business rule

DML in different session

VALIDATION

DMLvalidation

More DMLvalidation

Commit

SAFE SOLUTION: USE CUSTOM LOCKS• Prior to validating a certain business rule for

a specific record – acquire a custom lock– That identifies both Rule and Record– Using dbms_lock

• When a record is being validated for a certain rule, other sessions have to wait

• The commit (or rollback) releases all locks• Validation in a different session will include

all committed data

DML in different session

DMLvalidation

More DMLvalidation

Commit

SUMMARY

• Inline Views• Defaulting• Application Context• Flashback and the time dimension• NoSQL means smart SQL

– Cache refresh driven by change notification– Streaming analysis before persisting

• Decoupling galore– Bulk retrieval– Service enabling

• Business Rules• EBR• 12c promises even more