Oracle’s Next Generation of Busines Intelligence Tools
Download
Report
Transcript Oracle’s Next Generation of Busines Intelligence Tools
KC Server Development
Nieuwe SQL en PL/SQL features
in Oracle 10gR2 en R1
incl. reprise van ODTUG
presentatie
15 november 2005
KC Server Development – 15 november 2005 Oracle 10g’s Finest
1
Agenda
10gR2 – Een paar hoogtepuntjes uit release 2
van Oracle 10g (juli 2005)
10gR1 – Een bonte verzameling features en
functies uit 10gR2 (juni 2004)
De ODTUG 2005 presentatie:
Oracle 10g’s Finest—The Top 3 SQL and PL/SQL
Features New in 10g
Diner
Workshop
AMIS LAB database (10gR2)
KC Server Development – 15 november 2005 Oracle 10g’s Finest
2
10gR2 Aandachttrekkers
Grenzen verkennen met DBMS_OUTPUT
DML met Error Log – Constraint Violations
allowed
SQL*Plus Autotrace op basis van DBMS_XPLAN
Conditional PL/SQL Compilation
10x snellere transacties - Asynchronous Commit
Small fry
KC Server Development – 15 november 2005 Oracle 10g’s Finest
3
Beyond DBMS_OUTPUT
Wat is het resultaat van dit code fragment?
A – Compilation Error
B – Runtime Error
C – 10 regels output
D – 4 regels output en dan ORA-20000: ORU-10028
E – 10 regels output en dan ORA-20000: ORU-10028
KC Server Development – 15 november 2005 Oracle 10g’s Finest
4
Beyond DBMS_OUTPUT
Wat is het resultaat van dit code fragment?
Pre 10gR2:
KC Server Development – 15 november 2005 Oracle 10g’s Finest
10gR2:
5
Beyond DBMS_OUTPUT
10gR2 Improvement for DBMS_OUTPUT
Output limit from 255 to 32767
SET SERVEROUTPUT ON SIZE UNLIMITED
KC Server Development – 15 november 2005 Oracle 10g’s Finest
6
Will the update statement always succeed?
create table emp
( empno number
, ename varchar2(10)
, constraint emp_pk primary key (empno)
);
insert into emp values(1,'sam');
insert into emp values(2,'joe');
update emp
set empno=empno+1;
KC Server Development – 15 november 2005 Oracle 10g’s Finest
7
Statement level Constraint Checking
Will this statement succeed?
add constraint sal_check check (sal < 6000)
/
alter table emp
add constraint sal_check check (sal < 6000)
/
update emp
set
sal = sal + 2000
/
KC Server Development – 15 november 2005 Oracle 10g’s Finest
8
Oracle 10gR2 – DML with Error Log
begin
dbms_errlog.create_error_log
('EMP'
,'ERROR_LOG_EMP‘
) ;
end;
/
update emp
set
sal = sal + 2000
LOG ERRORS INTO ERROR_LOG_EMP ('salary raise')
REJECT LIMIT 1
/
KC Server Development – 15 november 2005 Oracle 10g’s Finest
9
DML with Error Log - Errors logged for each
failed record in the statement
Rowid of the record
that failed the Update
or Delete
Insert?
Columns mirroring the
table columns
Hold the values that
would have been if
the DML operation had
not failed
Oracle Error numer and
Message
User defined tag
KC Server Development – 15 november 2005 Oracle 10g’s Finest
10
SQL*Plus Autotrace with DBMS_XPLAN
KC Server Development – 15 november 2005 Oracle 10g’s Finest
11
Conditional PL/SQL Compilation
create or replace procedure p
as
begin
$IF $$debug_code
$THEN
dbms_output.put_line( 'Our debug code' );
dbms_output.put_line( 'Would go here' );
$END
dbms_output.put_line( 'And our real code
here' );
end;
alter procedure P compile
plsql_ccflags = 'debug_code:true' reuse
settings;
KC Server Development – 15 november 2005 Oracle 10g’s Finest
12
Conditional PL/SQL Compilation
alter procedure P compile
plsql_ccflags = 'debug_code:true' reuse
settings;
KC Server Development – 15 november 2005 Oracle 10g’s Finest
13
Conditional PL/SQL Compilation - Use $error
directive to ‘throw’ compilation errors
...
$if $$plsql_optimize_level !=1
$then
$error 'This program must be compiled at
optimization_level 1' $end
$end
...
KC Server Development – 15 november 2005 Oracle 10g’s Finest
14
Conditional PL/SQL Compilation
You can leave your - really slow - debug code in your application
now—and turn it on and off at will.
You can program assertions as you might in C or Java.
Each subprogram can test the values of its inputs, for example, and verify that
they meet some criteria. These tests can be active during the whole development
cycle and inactive for production.
However, they remain as formal documentation of the conditions upon which the
unit depends, and can be simply reactivated for debugging a production-time bug.
You can write database version-independent code
Program one package with code sections for version X and version Y; during
compilation, depending on the ‘condition’, one of the two is compiled
Check out the new DBMS_DB_VERSION supplied package.
The same applies to multi-locale, multi-customer, multi-platform
One set of packages, conditional code sections per locale/customer/…
You can support best practices during unit testing.
For production, helper subprograms will be declared in a package body. For calls
from a unit-testing framework, they are conditionally declared in the specification.
KC Server Development – 15 november 2005 Oracle 10g’s Finest
15
Conditional PL/SQL Compilation
Use Compile Time variables to insert static strings in the
PL/SQL code
$$PLSQL_UNIT
$$PLSQL_LINE
Use DBMS_DB_VERSION package to retrieve
information about the compile time environment
RELEASE , VER_LE_9_2 , VER_LE_10_1 , VER_LE_10_2
Instead of Session Level plsql_ccflags settings, you can
also refer to Package variables (constants!)
Use package DBMS_PREPROCESSOR to retrieve the
source as it is active after compilation
View USER_PLSQL_OBJECT_SETTINGS to retrieve the
values of the flags at compilation time
KC Server Development – 15 november 2005 Oracle 10g’s Finest
16
What takes longer?
A – 300 small Commits
B – One Large Commit
KC Server Development – 15 november 2005 Oracle 10g’s Finest
17
What happens during Commit?
Log writer process flushes the contents of the
REDO LOG buffers to the disk
To each of the specified Redo Log files
Not until the O/S signals the successful
completion of this write process (for each of the
Redo Log files) is the commit considered
complete
Only then will no data have been lost upon immediate
database crash
The Client has to wait for this write process to
complete!
KC Server Development – 15 november 2005 Oracle 10g’s Finest
18
Asynchronous Commit
With the statement
COMMIT WRITE BATCH NOWAIT ;
You can instruct Oracle to NOT wait for the Redo
Log writing before returning control to the Client
Consequences:
Transaction ‘completes’ much faster
You stand the risk of losing transactions
•
Eventually, the database will crash and some committed
transactions will have been lost!
Typical Usage
Frequent, small, non-crucial transactions such as realtime measurements, stocks tickers, visit-statistics etc.
KC Server Development – 15 november 2005 Oracle 10g’s Finest
19
Asynchronous Commit
Example
KC Server Development – 15 november 2005 Oracle 10g’s Finest
20
10gR2 Small Fry
Transparent Data Encryption
The data is stored encrypted on-disk, so that even if someone
steals your database, the information is protected
.Net support
Stored Procedures in .Net languages C# and VB.NET
XML DB: XSLT 2.0, XQuery, …
Data Pump direct compress
CLOB and BLOB over database links
PL/SQL Data Mining
Restore Points
easier Flashback to ‘logically labeled SCN’
Unicode 4.0 – 1226 new characters
UTL_NA for Matrix calculations
Rules Manager
KC Server Development – 15 november 2005 Oracle 10g’s Finest
21
10g Features and Functions
Data Pump
“Insertable” External Table
Merge improvements
Partition Outer Join
Regular Expressions (regexp)
Flashback in overdrive
Database Recycle Bin
KC Server Development – 15 november 2005 Oracle 10g’s Finest
22
Data Pump
New Integrated Import/Export architecture
New utilities expdp, impdp
Uses External Table Data Pump Unload
High speed direct-path load/unload
Serial speedup up to 10x for import and 2x for export
Complete server-site utility infrastructure
Automatically scales using parallel execution
Restart capabilities after stop or failure
Job runtime and size estimations
Progress monitoring
Public documented APIs
KC Server Development – 15 november 2005 Oracle 10g’s Finest
23
External Table unload
Unload data with a simple CTAS
Using pretty much any query
Combine multiple files from many source systems into
one big “Create” to facilitate merging
Subsetting made easy
Supports parallel unload and reload
Does not support
BFILE (easy workaround using dbms_lob.filegetname in a query)
LONG / LONG RAW (easy workaround using TO_LOB)
‘final object types’ – workable solutions
KC Server Development – 15 november 2005 Oracle 10g’s Finest
24
MERGE Optimizations
MERGE can be used as INSERT-only respective
UPDATE-only operation
More efficient plans
No outer join necessary (UPDATE-only)
Can use ANTI-JOIN (INSERT-only)
Conditional INSERT and UPDATE branches
Full flexibility for individual data filtering
Optional DELETE clause for UPDATE branch
Implicit Data Maintenance
KC Server Development – 15 november 2005 Oracle 10g’s Finest
25
Example of Merge in 10g
Conditional Update, Delete and Insert
Note: either update or insert can be omitted
MERGE INTO Products P – Destination table
USING Product_Changes S – Source table
ON (P.PROD_ID = S.PROD_ID)
-- Search/Join condition
WHEN MATCHED
THEN UPDATE
-- update if join
SET P.PROD_LIST_PRICE = S.PROD_NEW_PRICE
WHERE P.PROD_STATUS <> "OBSOLETE" -- Conditional UPDATE
DELETE WHERE (P.PROD_STATUS = "OBSOLETE")
WHEN NOT MATCHED
THEN INSERT
-- insert if not join
(prod_id, prod_status,prod_new_price) values (...)
WHERE S.PROD_STATUS <> "OBSOLETE" -- Conditional INSERT
KC Server Development – 15 november 2005 Oracle 10g’s Finest
26
Join Options
Cross Join (cartesian result set)
Every record from A combined with every record in B
(Inner) Join
Only records from A that match on join key with
records from B and vice versa
Right Outer Join
All records from B with matching records from A where
available and “empty” A records otherwise
Partition By Right Outer Join
All records from B with – per partition in A - matching
records where available and “empty” A records
otherwise
KC Server Development – 15 november 2005 Oracle 10g’s Finest
27
Example Joining –
Voornamen en Achternamen
CROSS JOIN
24 records
INNER JOIN
4 records
RIGHT OUTER JOIN
5 records
PARTITION BY (geslacht)
RIGHT OUTER JOIN
6 records
KC Server Development – 15 november 2005 Oracle 10g’s Finest
28
Partitioned Outer Join
New outer join syntax enabling easy specification and high
performance for joins that "densify" sparse data.
To specify comparison calculations and to format reports
reliably, best to return a consistent set of dimension members
in query results
Yet data normally stored in "sparse" form: why waste space
storing non-occurrence?
Ugly and slow SQL needed to add back rows for nonexistent
cases into query output.
Most frequently used to replace missing values along time
dimension.
Proposed to ANSI for SQL standard.
KC Server Development – 15 november 2005 Oracle 10g’s Finest
29
For each department,
get the number of employees hired per year
select
,
,
from
group
by
,
d.dname department
year
count(empno) "number of hires"
emp e
right outer join
( select distinct extract(year from hiredate) year
from emp
) years
on extract(year from e.hiredate) = year
right outer join
dept d
on (e.deptno = d.deptno)
year
d.dname
KC Server Development – 15 november 2005 Oracle 10g’s Finest
30
Partition Outer Join – For each department,
get the number of employees hired per year
select
,
,
from
d.dname department
year
count(empno) "number of hires"
emp e
partition by (deptno)
right outer join
( select distinct extract(year from hiredate) year
from emp
) years
on extract(year from e.hiredate) = year
right outer join
dept d
on (e.deptno = d.deptno)
group
by
,
year
d.dname
KC Server Development – 15 november 2005 Oracle 10g’s Finest
31
Partition Outer Join – For each department,
get the number of employees hired per year
select
,
,
from
group
by
,
d.dname department
year
count(empno) "number of hires"
emp e
partition by (deptno)
right outer join
( select distinct extract(year from hiredate) year
from emp
) years
on extract(year from e.hiredate) = year
right outer join
dept d
on (e.deptno = d.deptno)
year
d.dname
KC Server Development – 15 november 2005 Oracle 10g’s Finest
32
Regular Expressions
POSIX compliant regular expression functionality with
additional multilingual support
similar to like, instr, substr, replace
Support for VARCHARs and CLOBs
Four functions – SQL and PL/SQL:
regexp_like, regexp_instr, regexp_substr and regexp_replace
SELECT first_name, last_name
FROM employees
WHERE REGEXP_LIKE (first_name, ’^Ste(v|ph)en$’);
FIRST_NAME
-------------------Steven
Steven
Stephen
LAST_NAME
------------------------King
Markle
Stiles
KC Server Development – 15 november 2005 Oracle 10g’s Finest
33
Flashback
Flashback Query
9iR1 – primitive
Had to open flashback cursors before doing any DML
It worked, but was not “easy”
KC Server Development – 15 november 2005 Oracle 10g’s Finest
37
Flashback
Flashback Query
9iR2 – sophisticated
No need to open cursors before doing modifications
Can flashback in SQL, no packages needed
Can flashback in modifications
It worked and was much easier
•
•
•
Could join the table with itself as of N minutes ago
Put update a set of rows, put them back as they were N
minutes ago
In SQL
KC Server Development – 15 november 2005 Oracle 10g’s Finest
38
Flashback
In 10g
Flashback Query
Flashback Table
Flashback Row History
Flashback Drop
Flashback Database
KC Server Development – 15 november 2005 Oracle 10g’s Finest
39
Flashback – Flashback Row History
Instead of “show me the data as of”, you can say
“show me all versions of the data between”
Select ename, sal
from emp versions between timestamp a and b
where ename = ‘SCOTT’
ENAME
SAL
---------- ---------SCOTT
3000
SCOTT
3300
SCOTT
3630
…
KC Server Development – 15 november 2005 Oracle 10g’s Finest
45
Flashback – Flashback Row History
See related information about each row
SCN range the row was “valid for”
Time range (approx) the row was valid for
The transaction id (XID) that modified the row
The operation (I/U/D) that was performed on the row
select ename, sal,
versions_operation,
versions_starttime,
versions_endtime,
versions_startscn,
versions_endscn,
versions_xid
from emp versions between timestamp &A and &B
where empno = 7788
order by versions_startscn nulls first
KC Server Development – 15 november 2005 Oracle 10g’s Finest
46
New Connect By Features
CONNECT_BY_ROOT returns for the indicated column the value of
the parent (prior)
SELECT ename "Employee"
record
,
CONNECT_BY_ROOT ename "Manager"
FROM
emp
CONNECT BY PRIOR empno = mgr
start with mgr is null
Employee
---------JONES
SCOTT
Manager
---------KING
KING
Function CONNECT_BY_ISLEAF returns 0 for non-leaf nodes and 1
for leaf-nodes
Functions CONNECT_BY_ISCYCLE and NO_CYCLE
help prevent ‘end-less loops’
KC Server Development – 15 november 2005 Oracle 10g’s Finest
56
Other 10g Stuff
Alternative Quoting Mechanism
Automatic execution of Bulk Collect when a For
Loop has been programmed
Get the full Error Call-stack for the origin of an
exception: dbms_utility.format_error_backtrace
Column Level VPD
Only enforce policy when user attempts to read
specific columns
Fine Grained Audit on DML events
KC Server Development – 15 november 2005 Oracle 10g’s Finest
57
Oracle 10g’s Finest—The Top 3 SQL and
PL/SQL Features New in 10g
Lucas Jellema
AMIS, The Netherlands
KC Server Development – 15 november 2005 Oracle 10g’s Finest
58
Agenda
Oracle 10g Release 1 for SQL and PL/SQL
What I will not discuss
SQL Model Clause
The spreadsheet in your query
Expression Filters
The query in your tables
DBMS_Frequent_Itemset
Data Mining through PL/SQL
KC Server Development – 15 november 2005 Oracle 10g’s Finest
59
10g Features I will not discuss
(but could also have picked)
Partition Outer Join
Hierarchical Improvements (hear Tom Kyte, yesterday)
Case Insensitive Query
Regular Expressions (see Alan Wintersteen, session 10)
Bulk DML Improvements – (FORALL)
DBMS_SCHEDULER
Alternative Quoting
Returning Aggregate
UTL_MAIL
Data Pump
Flashback Functionality (see David Anderson, right now)
Merge Enhancements
PL/SQL Compiler enhancements
KC Server Development – 15 november 2005 Oracle 10g’s Finest
60
Apology
For making some slight slide changes.
No slight was intended, just better slides.
KC Server Development – 15 november 2005 Oracle 10g’s Finest
61
Oracle 10g SQL Model Clause
The SQL Model clause allows users to embed
spreadsheet-like models in a SELECT statement
in a way that was previously the domain of dedicated
multidimensional OLAP servers such as Oracle Express and
Oracle9i OLAP or Spreadsheets such as Excel
Often, these models involve a series of macros that aggregate
data over a number of business dimensions, over varying time
periods, and following a set of complex business rules
The aim of the SQL Model clause is to give
normal SQL statements the ability to create a
multidimensional array from the results of a
normal SELECT statement
carry out any number of interdependent inter-row and
inter-array calculations on this array
KC Server Development – 15 november 2005 Oracle 10g’s Finest
62
Query Structure
with
inline view[,inline view,...]
select column [,column,...] | SQL Function(column) |
user defined function| scalar subquery |
user defined aggregate | cursor expression |
case expression | analytical function
from
table [AS OF or VERSIONS] [SAMPLE clause] |
view | inline view | external table |
table function | cast nested table
where <condition> | <join condition>|new join syntax|
10g Query Partition (Partition Outer Join)
connect by
group by [ ROLLUP | CUBE | GROUPING SETS]
having
model
order
[siblings] by [nulls first|nulls last]
KC Server Development – 15 november 2005 Oracle 10g’s Finest
63
The MODEL Clause – like processing the
result set in a spreadsheet
select <expression>
from
<tables><views><in line views>
join
where
{
select ename
,
deptno
,
sal
,
model_measure dept_total
from
emp
where sal > 1000
model ...
order
by deptno, ename
Model processing:
•Update ‘cells’
• Through calculations across cells
• Get value from preceding or following row
• Multi-row aggregation (count, max, min, sum, avg)
• Create new cells (columns or measures)
• Create new records
(10g) Model
order by
Outer Query
in case of
In-Line View
Result Set to Client
KC Server Development – 15 november 2005 Oracle 10g’s Finest
64
What does the MODEL clause do?
MODEL allows
Any Column Value in the result set to be updated
Columns to be added
•
The MODEL clause may return more columns than the
underlying tables contain
Rows to be added
Choice between return all rows or only the new and
updated rows
Values of cells to be derived using expressions that
•
•
•
Reference existing cell-values
May contain functions and aggregations such as SUM, MAX,
MIN, AVG
May use queries to retrieve values from outside the result set
KC Server Development – 15 november 2005 Oracle 10g’s Finest
65
Model vs. Analytical Functions
MODEL
10g
Calculate Columns using
inter-row calculations
(direct cell-reference, max,
min, sum, count, etc.)
Update cells
Insert rows
Suppress untouched rows
Use reference models
8.1.6 EE, 9iR2 SE
Calculate Columns using
inter-row calculations (lag,
lead, first, last, rank, sum,
max, min, count, avg,…)
No update of values
No creation of rows
No suppression of rows
More efficient
(performance)
Easier syntax
KC Server Development – 15 november 2005 Oracle 10g’s Finest
Analytical Functions
66
Example Model Clause
Add an additional derived column total_sal
select ename
,
deptno
,
sal
,
comm
,
total_sal
from
emp
model main salary_model
dimension by (ename)
measures
(sal, comm, 0 as total_sal, deptno )
ignore nav
rules upsert
( total_sal[ANY]= sal[CV(ename)] + comm[CV(ename)]
)
dimensions
measures
Cells correspond with measures
Cells referenced through dimensions
Only measures can be inserted or updated
KC Server Development – 15 november 2005 Oracle 10g’s Finest
67
Example Model Clause
Result of adding column TOTAL_SAL
( total_sal[ANY]= sal[CV(ename)] + comm[CV(ename)]
)
KC Server Development – 15 november 2005 Oracle 10g’s Finest
68
Example Model Clause
Calculate (and exclusively return) Grand Total
select ename
,
total_sal
from
emp
model
return updated rows
main salary_model
dimension by ( ename )
measures
(sal, comm, 0 as total_sal ) ignore nav
rules upsert
( total_sal['GRAND']= sum(sal)[ANY] + sum(comm)[ANY]
)
KC Server Development – 15 november 2005 Oracle 10g’s Finest
69
What happened with this last model?
total_sal['GRAND']
= sum(sal)[ANY] + sum(comm)[ANY]
KC Server Development – 15 november 2005 Oracle 10g’s Finest
select ename
,
total_sal
from
emp
model
return updated rows
70
Example Model Clause
Introducing Partitioning
select deptno
,
ename
,
total_sal
from
emp
model
return updated rows
main
salary_model
partition by (deptno)
dimension by ( ename )
measures
(sal, nvl(comm,0) comm , 0 as total_sal )
rules upsert
( total_sal['GRAND']= sum(sal)[ANY] + sum(comm)[ANY]
)
KC Server Development – 15 november 2005 Oracle 10g’s Finest
71
Example Model Clause
SELECT SUBSTR(country,1,20) country
,
SUBSTR(prod,1,15) prod
,
year
,
sales
FROM
sales_view
WHERE country IN ('Italy','Japan')
MODEL RETURN UPDATED ROWS
PARTITION BY (country) –- for every country
DIMENSION BY (prod, year) –- cells are referenced with these
MEASURES (sale sales) -- the value to be derived
RULES (
sales['Bounce', 2002] = sales['Bounce', 2001] +
sales['Bounce', 2000],
sales['Y Box', 2002] = sales['Y Box', 2001],
sales['2_Products', 2002] = sales['Bounce', 2002]
+ sales['Y Box', 2002]
)
ORDER BY country, prod, year;
KC Server Development – 15 november 2005 Oracle 10g’s Finest
72
Example Model Clause
COUNTRY
-------------------Italy
Italy
Italy
Japan
Japan
Japan
PROD
YEAR
SALES
--------------- ---------- ---------2_Products
2002
90387.54
Bounce
2002
9179.99
Y Box
2002
81207.55
2_Products
2002 101071.96
Bounce
2002
11437.13
Y Box
2002
89634.83
KC Server Development – 15 november 2005 Oracle 10g’s Finest
73
Visualization of MODEL clause
KC Server Development – 15 november 2005 Oracle 10g’s Finest
74
MODEL Clause
Inside the MODEL clause, you can use nifty
features like
CV(dimension) to refer to the current value of a certain
dimension
FOR loops over DIMENSION values
•
•
e.g. sales[ ‘prodx’, for year in (2000,2001,2002)] =
sales[CV(product), 1999 – (2002 - CV(year))]
or even more interesting: sales[‘prodx’, for year in (select
year from important_years)] = ….
Use symbolic references like ANY and ‘year > 2000’
Use ITERATE to evaluate rules a fixed number of
times
Use Reference Models to retrieve supporting values
from
KC Server Development – 15 november 2005 Oracle 10g’s Finest
75
Example Model Clause
Creating new rows
SELECT cell num
FROM
DUAL
MODEL
DIMENSION BY (0 attr)
MEASURES (0 cell)
RULES ITERATE (10)
( cell[iteration_number]
= iteration_number)
KC Server Development – 15 november 2005 Oracle 10g’s Finest
76
Pivoting the result set using the Model Clause
DEPTNO
---------10
30
20
NR1
---------KING
BLAKE
SCOTT
NR2
---------CLARK
ALLEN
FORD
NR3
---------MILLER
TURNER
JONES
This my goal
This is Step One
select deptno
,
ename
,
row_number() OVER ( PARTITION BY deptno
ORDER BY sal desc
NULLS LAST) rnk
from
emp
order
by
deptno
,
rnk
/
KC Server Development – 15 november 2005 Oracle 10g’s Finest
77
Pivoting the result set using the Model Clause
select deptno
,
nr1
,
nr2
,
nr3
from
emp
model
return updated rows
partition by (deptno) –- do this thing for each Department
dimension by (row_number() OVER (PARTITION BY deptno
ORDER BY sal desc NULLS LAST) rnk
) -– each position gets its own result column
measures (ename, lpad(' ',10) nr1
, lpad(' ',10) nr2 , lpad(' ',10) nr3)
rules upsert
(
nr1 [0] = ename [1]
, nr2 [0] = ename [2]
, nr3 [0] = ename [3]
)
KC Server Development – 15 november 2005 Oracle 10g’s Finest
78
10g - Expression Filters
Oracle 10g Expression Filter
Store filter expressions
Select records that comply with the stored filters
•
Or the filters that match
specific records!
Filter expressions express
•
•
•
Customer interest
Regulation or business
rule
Match applicant with
jobs and vice versa
Jobs have associated
profile== filter
Application has profile
== expression filter
KC Server Development – 15 november 2005 Oracle 10g’s Finest
79
Expression Filters (10g)
“Given data, find consumers for it”
Expression Filters like stored Where-clauses plus
framework for efficient dynamic evaluation
What is an expression filter ?
Feature to manage and evaluate conditional expressions that
describe users interests in data
Allows you to store conditional expressions in a column,
which you can then use in the WHERE clause of a database query
Expression Filter includes:
Expression datatype,
SQL EVALUATE operator,
Expression indexing (Enterprise Edition only)
Administrative utilities
KC Server Development – 15 november 2005 Oracle 10g’s Finest
80
Overview of Expression Filters
Attribute Set
Car4Sale
Attribute Set Assignment Linking column with filter clause
To AttributeSet and Object Type
Table Consumers
Cid
Zip Phone Interest
Object Type
Car4Sale
Model
Year
Price
Mileage
Consumer Interest
specified through “Filter” Clause, such as:
'Model=''Taurus'' and Price < 15000 and Mileage < 25000'
select *
from
consumers
where evaluate
( interest
, Car4Sale('Mustang', 2000, 18000 , 22000).getVarchar()
) = 1
KC Server Development – 15 november 2005 Oracle 10g’s Finest
81
Expression Filters (10g)
Basic steps to create and use an expression
column
Define an attribute set
Create user table with expression column (or add new varchar2
column to existing table)
Assign attribute set to the expression column in the table
Insert expressions in the table
Apply the SQL EVALUATE operator
Applying the SQL evaluate Operator
The EVALUATE operator returns 1 for an expression that
evaluates to true for the data item and 0 otherwise.
The EVALUATE operator is used in the WHERE clause of a
standard SQL query or DML (UPDATE or DELETE) statement.
KC Server Development – 15 november 2005 Oracle 10g’s Finest
82
Overview of Expression Filters
Using Expression Filter in Table Join
Table Inventory
Attribute Set
Car4Sale
Model Year Price Mileage
Table Consumers
Cid
Zip Phone Interest
Object Type
Car4Sale
Model
Year
Price
Mileage
Consumer Interest
specified through “Filter” Clause, such as:
'Model=''Taurus'' and Price < 15000 and Mileage < 25000'
select consumers.*, inventory.*
from
consumers, inventory
where evaluate
( interest
, Car4Sale(model, year, price, mileage).getVarchar()
) = 1
KC Server Development – 15 november 2005 Oracle 10g’s Finest
83
Steps to set up Expression Filters
Create an object type as source for the Expression Attribute Set
CREATE OR REPLACE
TYPE Car4Sale AS OBJECT
( Model VARCHAR2(20)
, Year NUMBER
, Price NUMBER
, Mileage NUMBER
);
Defining an attribute set based on the type
BEGIN
DBMS_EXPFIL.CREATE_ATTRIBUTE_SET
( attr_set => 'Car4Sale‘
, from_type => 'YES‘
);
END;
KC Server Development – 15 november 2005 Oracle 10g’s Finest
84
Expression Filters (10g)
The Expression Attribute Set
Defined as special Oracle Object Type that carries all valid
variables for the set
Implicitly includes all the built-in functions
Possible to add user-defined functions
May contain XPath predicates defined on XMLType attributes
Expressions must adhere to SQL Where Clause format
•
Subqueries are not allowed!
Not required to use all the defined elementary attributes (null is
acceptable)
Two ways to create an attribute set
•
•
use dbms_expfil.create_attribute_set in combination with
dbms_expfil.add_elementary_attribute (automatically creates object)
use an existing object type to create an attribute set
dbms_expfil.create_attribute_set(attr_set => 'car4sale',from_type =>
'yes'))
KC Server Development – 15 november 2005 Oracle 10g’s Finest
85
Typical Usage of Expression Filters
To screen incoming data
Find matches with expressed interests or conditions
•
•
We have found an item that may be exactly what you’re
looking for
A suspect has just entered the country
Find non-matches
•
•
This new piece of data does not meet one of (y)our standards
This record does not adhere to this business rule
To screen existing data for new interests,
conditions, standards or rules
Because of this new EU regulation, we have to
redesign these products…
KC Server Development – 15 november 2005 Oracle 10g’s Finest
91
Business Rule example
Deptno
Emp
empno ename job
sal deptno dname
Business Rules
label table msg
EMP1
DPT1
EMP2
DPT2
BOS1
CREATE OR REPLACE TYPE
loc BusinessRuleConditions AS
OBJECT
( Condition1 VARCHAR2(20)
, Condition2 VARCHAR2(20)
, Condition3 VARCHAR2(20)
, Condition4 VARCHAR2(20)
, Condition5 VARCHAR2(20)
);
KC Server Development – 15 november 2005 Oracle 10g’s Finest
BEGIN
expression
DBMS_EXPFIL.CREATE_ATTRIBUTE_SET
( attr_set =>
EMP
001
'BusinessRuleConditions'
DEPT 011
, from_type => 'YES'
EMP
002
);
BEGIN
DEPT 012
END;
DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET
BONUS
021
( attr_set =>
'BusinessRuleConditions'
, expr_tab => 'Business_Rules'
, expr_col => 'br_expression'
);
END;
92
Inserting the business rules as Expressions
Deptno
Emp
empno ename job
Business Rules
label table msg
EMP1
DPT1
EMP2
DPT2
BOS1
EMP
DEPT
EMP
DEPT
BONUS
001
011
002
012
021
sal deptno dname
loc
INSERT INTO Business_rules VALUES ('EMP1',
'001','EMP', 'Condition1 =''SALESMAN'' or Condition2
is null');
INSERT INTO Business_rules VALUES ('EMP2',
'002','EMP', '(sysdate - to_date(condition3))
<8500');
INSERT INTO Business_rules VALUES ('DPT1',
'001','DEPT', 'Condition2 <>''SALES'' or Condition3
expression
<> ''PARIS'' ');
Condition1 =''SALESMAN'' or Condition2 is null
Condition2 <>''SALES'' or Condition3 <> ''PARIS''
(sysdate - to_date(condition3)) <8500
012
021
KC Server Development – 15 november 2005 Oracle 10g’s Finest
93
Data Mining from PL/SQL
KC Server Development – 15 november 2005 Oracle 10g’s Finest
96
PL/SQL API for Data Mining:
DBMS_FREQUENT_ITEMSET
Oracle 9i and 10g EE contain Data Mining
What used to be the Oracle Darwin product
Data Mining is statistically analyzing data, trying
to discover patterns to be used for predictions
The Data Mining functionality is largely accessed
through a Java API
Some Data Mining can directly be done from
PL/SQL: dbms_frequent_itemset
To find out from a large collection of items or facts,
which items have an increased chance of occurring
together
KC Server Development – 15 november 2005 Oracle 10g’s Finest
97
Examples of questions suited for Data Mining
with DBMS_FREQUENT_ITEMSET
Supermarket-basket analysis: if you buy pizza and cola,
what other products are likely to be in your trolley
the occurrence of a certain disease in combination with
behavioral or diet-related characteristics
the preference for a certain type of car in combination with
properties such as income, profession, preferred make of
father/neighbour/friend, age
the preference for a political candidate or party; you can
think of some of the associated properties…(religion,
income, skincolor, corporate associations etc.)
the occurrence of a software bug given some of the
characteristics of the application, the organization, the
technology and the programmer…
KC Server Development – 15 november 2005 Oracle 10g’s Finest
98
Business Case: Patterns in Movie Preferences
Three tables with movie data:
KC Server Development – 15 november 2005 Oracle 10g’s Finest
99
Business Case: Patterns in Movie Preferences
Questions to investigate
Is there such a Frequent Itemset pattern that we can
predict for someone of certain gender and age what
movies he/she is likely to appreciate?
If we know that someone enjoys Finding Nemo and
Shrek, is there a distinct possibility that he will enjoy
Sharktale?
Who should we target in our marketing for a new
movie, if that movie is akin to X?
When my 5-year old nephew comes to visit, how on
earth can I entertain him?
KC Server Development – 15 november 2005 Oracle 10g’s Finest
100
Performing Analysis
We need to call dbms_frequent_itemset
Instructing how to find data – using a Cursor
Expression
The cursor must return (id,value) where id is a ‘basket
identifier’
All values must be of the same data type
DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONAL
( cursor ( select id, value from …)
, 0.05 – threshold (minimal % of supporting
records)
, 4 –- itemset min length
, 6 –- itemset max length
, null -- INCLUDING_ITEMS
REF CURSOR
, null -- EXCLUDING_ITEMS
REF CURSOR
) RETURNS ANYDATASET
KC Server Development – 15 november 2005 Oracle 10g’s Finest
101
Simple transaction basket analysis
SELECT
,
,
,
FROM
order
by
,
CAST (itemset as FI_number_NT) itemset
support
length
total_tranx
table( DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONAL
( cursor
( SELECT id, mve_id_1 FROM movie_preferences mpe
union
SELECT id, mve_id_2 FROM movie_preferences mpe
union
SELECT id, mve_id_3 FROM movie_preferences mpe
union
SELECT id, 1000* age_category
FROM movie_preferences mpe
union
SELECT id, case mpe.gender when 'M' then -1
else -2 end FROM movie_preferences mpe
)
, 0.05 -- threshold
, 4 –- itemset min length
, 6 –- itemset max length
, NULL
, NULL
)
)
support desc
length
KC Server Development – 15 november 2005 Oracle 10g’s Finest
102
Interpretation of simple analysis
First record: 3 occurrences
-2 : Female
5000: 21+ years
3: Shark Tale
5: Love Actually
Second record: 3 occurrences
Female
12-16 years
Shark Tale and Finding Nemo
…
KC Server Development – 15 november 2005 Oracle 10g’s Finest
103
Simple transaction basket analysis:
better presentation of the findings
SELECT
,
,
,
FROM
order
by
,
present_items( CAST (itemset as FI_number_NT)) itemset
support
length
total_tranx
table( DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONAL
( cursor
( SELECT id, mve_id_1 FROM movie_preferences mpe
union
SELECT id, mve_id_2 FROM movie_preferences mpe
union
SELECT id, mve_id_3 FROM movie_preferences mpe
union
SELECT id, 1000* age_category
FROM movie_preferences mpe
union
SELECT id, case mpe.gender when 'M' then -1
else -2 end FROM movie_preferences mpe
)
, 0.05
, 4
, 6
, NULL
, NULL
)
)
support desc
length desc order
KC Server Development – 15 november 2005 Oracle 10g’s Finest
104
Simple transaction basket analysis:
better presentation of the findings
SELECT
,
,
,
FROM
order
by
,
present_items( CAST (itemset as FI_number_NT)) itemset
support
length
total_tranx
table( DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONAL
( cursor
( SELECT id, mve_id_1 FROM movie_preferences mpe
union
SELECT id, mve_id_2 FROM movie_preferences mpe
union
SELECT id, mve_id_3 FROM movie_preferences mpe
union
SELECT id, 1000* age_category
FROM movie_preferences mpe
union
SELECT id, case mpe.gender when 'M' then -1
else -2 end FROM movie_preferences mpe
)
, 0.05
, 4
, 6
, NULL
, NULL
)
)
support desc
length desc order
KC Server Development – 15 november 2005 Oracle 10g’s Finest
105
Business Case: Our Nephew
Our nephew is visiting;
he is 5 years old
he enjoyed Finding Nemo
he is male
What movie should we rent for him?
KC Server Development – 15 november 2005 Oracle 10g’s Finest
107
Simple transaction basket analysis:
better presentation of the findings
SELECT
from
,
,
where
and
and
and
and
select items.column_value "Recommended Movie"
<inline view> it
table(it.itemset) items
movies mve
support > 1
set_contains_item( itemset, '2-6 years' ) = 1
set_contains_item( itemset, 'M' ) = 1
mve.title = items.column_value
mve.title != 'Finding Nemo'
KC Server Development – 15 november 2005 Oracle 10g’s Finest
108
In-Line View for selecting Movie-Basket
Combinations that include Finding Nemo
( SELECT CAST(itemset as FI_varchar_NT) itemset
,
support,length,total_tranx
FROM
table
( DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONAL
( cursor
( SELECT mpe.id, title FROM movie_preferences mpe
, movies mve where mve.id = mpe.mve_id_1
union
SELECT mpe.id, title FROM movie_preferences mpe
, movies mve where mve.id = mpe.mve_id_2
union
SELECT mpe.id, title FROM movie_preferences mpe
, movies mve where mve.id = mpe.mve_id_3
union
SELECT mpe.id, acy.description
FROM movie_preferences mpe, age_categories acy
where acy.id = mpe.age_category
union
SELECT id, gender FROM movie_preferences
)
, 0.05 , 4 , 6
, CURSOR( SELECT *
FROM table(FI_VARCHAR_NT('Finding Nemo')))
, NULL
)
)
) it
KC Server Development – 15 november 2005 Oracle 10g’s Finest
109
Conclusions
10gR1 has some neat new SQL & PL/SQL features
Enrichments of existing ones
Some brand new ones
•
Especially in the area of On-Line, Large Data Set interpretation
Model Clause – spreadsheet in SQL
Inter-row, direct cell references, adding records
Expression Filters – stored Filter Conditions
“Have (new) data find searchers”
Matching, Alerting, Rule Enforcement
DBMS_FREQUENT_ITEMSET – data mining
Exploit association patterns (“baskets”) buried in your
data for “fortune telling”
KC Server Development – 15 november 2005 Oracle 10g’s Finest
111
Onderwerpen voor ODTUG 2006 –
17-21 Juni Washington DC
Oracle 9i en 10g
SQL en PL/SQL
Oracle Text
Oracle Spatial
XML DB
HTML DB
Lite
Express Edition
Oracle 10g WebForms
JDAPI
WebUtil
PJC
Java/J2EE integratie
JavaScript
Oracle Warehouse Builder
Oracle Discoverer
…
ADF
ADF Faces
JHeadstart
Oracle BPEL
Oracle InterConnect
XML Publisher
Oracle en Open Source
ADF en Spring
Hibernate en ADF
MySQL vs. Express Edition
Business Rules
Software Engineering
Oracle Development Tools &
Jira, CVS, JUnit, Ant/Maven
etc.
…
KC Server Development – 15 november 2005 Oracle 10g’s Finest
Oracle JDeveloper 10.1.3
112