KC Server Development
This presentation is the property of its rightful owner.
Sponsored Links
1 / 86

KC Server Development Nieuwe SQL en PL/SQL features in Oracle 10gR2 en R1 incl. reprise van ODTUG presentatie 15 november 2005 PowerPoint PPT Presentation


  • 67 Views
  • Uploaded on
  • Presentation posted in: General

KC Server Development Nieuwe SQL en PL/SQL features in Oracle 10gR2 en R1 incl. reprise van ODTUG presentatie 15 november 2005. Agenda. 10gR2 – Een paar hoogtepuntjes uit release 2 van Oracle 10g (juli 2005) 10gR1 – Een bonte verzameling features en functies uit 10gR2 (juni 2004)

Download Presentation

KC Server Development Nieuwe SQL en PL/SQL features in Oracle 10gR2 en R1 incl. reprise van ODTUG presentatie 15 november 2005

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Kc server development nieuwe sql en pl sql features in oracle 10gr2 en r1 incl reprise van odtug presentatie 15 november 2005

KC Server DevelopmentNieuwe SQL en PL/SQL features in Oracle 10gR2 en R1incl. reprise van ODTUG presentatie15 november 2005


Agenda

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)


10gr2 aandachttrekkers

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


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

Beyond DBMS_OUTPUTWat is het resultaat van dit code fragment?


Beyond dbms output wat is het resultaat van dit code fragment1

Beyond DBMS_OUTPUTWat is het resultaat van dit code fragment?

Pre 10gR2:

10gR2:


Beyond dbms output 10gr2 improvement for dbms output

Beyond DBMS_OUTPUT10gR2 Improvement for DBMS_OUTPUT

  • Output limit from 255 to 32767

  • SET SERVEROUTPUT ON SIZE UNLIMITED


Will the update statement always succeed

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;


Statement level constraint checking will this statement succeed

Statement level Constraint CheckingWill 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

  • /


Oracle 10gr2 dml with error log

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

  • /


Dml with error log errors logged for each failed record in the statement

DML with Error Log - Errors logged for each failed record in the statement

  • Rowid of the recordthat failed the Updateor Delete

    • Insert?

  • Columns mirroring thetable columns

    • Hold the values thatwould have been ifthe DML operation hadnot failed

  • Oracle Error numer andMessage

  • User defined tag


Sql plus autotrace with dbms xplan

SQL*Plus Autotrace with DBMS_XPLAN


Conditional pl sql compilation

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;


Conditional pl sql compilation1

Conditional PL/SQL Compilation

  • alter procedure P compile

  • plsql_ccflags = 'debug_code:true' reuse settings;


Conditional pl sql compilation use error directive to throw compilation errors

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...


Conditional pl sql compilation2

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.


Conditional pl sql compilation3

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


What takes longer

B – One Large Commit

What takes longer?

A – 300 small Commits


What happens during commit

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!


Asynchronous commit

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 real-time measurements, stocks tickers, visit-statistics etc.


Asynchronous commit example

Asynchronous CommitExample


10gr2 small fry

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


10g features and functions

10g Features and Functions

  • Data Pump

  • “Insertable” External Table

  • Merge improvements

  • Partition Outer Join

  • Regular Expressions (regexp)

  • Flashback in overdrive

    • Database Recycle Bin


Data pump

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


External table unload

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


Merge optimizations

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


Example of merge in 10g

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


Join options

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


Example joining voornamen en achternamen

Example Joining – Voornamen en Achternamen

CROSS JOIN

INNER JOIN

RIGHT OUTER JOIN

PARTITION BY (geslacht)

RIGHT OUTER JOIN

24 records

4 records

5 records

6 records


Partitioned outer join

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.


For each department get the number of employees hired per year

For each department, get the number of employees hired per year

select d.dname department

, year

, count(empno) "number of hires"

from 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)

group

by year

, d.dname


Partition outer join for each department get the number of employees hired per year

Partition Outer Join – For each department, get the number of employees hired per year

select d.dname department

, year

, count(empno) "number of hires"

from 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


Partition outer join for each department get the number of employees hired per year1

Partition Outer Join – For each department, get the number of employees hired per year

select d.dname department

, year

, count(empno) "number of hires"

from 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


Regular expressions

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 LAST_NAME

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

Steven King

Steven Markle

Stephen Stiles


Flashback

Flashback

  • Flashback Query

    • 9iR1 – primitive

    • Had to open flashback cursors before doing any DML

    • It worked, but was not “easy”


Flashback1

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


Flashback2

Flashback

  • In 10g

    • Flashback Query

    • Flashback Table

    • Flashback Row History

    • Flashback Drop

    • Flashback Database


Flashback flashback row history

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 empversions between timestamp a and b

where ename = ‘SCOTT’

ENAME SAL

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

SCOTT 3000

SCOTT 3300

SCOTT 3630


Flashback flashback row history1

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


New connect by features

New Connect By Features

  • CONNECT_BY_ROOT returns for the indicated column the value of the parent (prior) record

  • 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’

SELECT ename "Employee"

, CONNECT_BY_ROOT ename "Manager"

FROM emp

CONNECT BY PRIOR empno = mgr

start with mgr is null

Employee Manager

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

JONES KING

SCOTT KING


Other 10g stuff

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


Oracle 10g s finest the top 3 sql and pl sql features new in 10g lucas jellema amis the netherlands

Oracle 10g’s Finest—The Top 3 SQL and PL/SQL Features New in 10g Lucas JellemaAMIS, The Netherlands


Agenda1

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


10g features i will not discuss but could also have picked

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


Apology

Apology

For making some slight slide changes.

No slight was intended, just better slides.


Oracle 10g sql model clause

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


Query structure

Query Structure

withinline view[,inline view,...] select column [,column,...] | SQL Function(column) |user defined function| scalar subquery | user defined aggregate | cursor expression | case expression | analytical functionfrom 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 bygroup by [ ROLLUP | CUBE | GROUPING SETS] having

model order [siblings] by [nulls first|nulls last]


The model clause like processing the result set in a spreadsheet

The MODEL Clause – like processing the result set in a spreadsheet

select <expression>

from <tables><views><in line views>joinwhere

select ename

, deptno

, sal

, model_measure dept_total

from empwhere 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 Queryin case of In-Line View

Result Set to Client


What does the model clause do

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


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

Analytical Functions

8.1.6 EE, 9iR2 SE

Calculate Columns usinginter-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

Model vs. Analytical Functions


Example model clause add an additional derived column total sal

Example Model ClauseAdd 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 navrules upsert ( total_sal[ANY]= sal[CV(ename)] + comm[CV(ename)])

dimensions

Cells correspond with measures

Cells referenced through dimensions

Only measures can be inserted or updated

measures


Example model clause result of adding column total sal

Example Model ClauseResult of adding column TOTAL_SAL

( total_sal[ANY]= sal[CV(ename)] + comm[CV(ename)])


Example model clause calculate and exclusively return grand total

Example Model ClauseCalculate (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]

)


What happened with this last model

What happened with this last model?

total_sal['GRAND'] = sum(sal)[ANY] + sum(comm)[ANY]

select ename

, total_sal

from emp

model

return updated rows


Example model clause introducing partitioning

Example Model ClauseIntroducing 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]

)


Example model clause

Example Model Clause

SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod

, year, salesFROM sales_viewWHERE country IN ('Italy','Japan')MODEL RETURN UPDATED ROWSPARTITION BY (country) –- for every country DIMENSION BY (prod, year) –- cells are referenced with theseMEASURES (sale sales) -- the value to be derivedRULES (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;


Example model clause1

Example Model Clause

COUNTRY PROD YEAR SALES

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

Italy 2_Products 2002 90387.54

Italy Bounce 2002 9179.99

Italy Y Box 2002 81207.55

Japan 2_Products 2002 101071.96

Japan Bounce 2002 11437.13

Japan Y Box 2002 89634.83


Visualization of model clause

Visualization of MODEL clause


Model clause

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


Example model clause creating new rows

Example Model ClauseCreating new rows

SELECT cell num

FROM DUAL

MODEL

DIMENSION BY (0 attr)

MEASURES (0 cell)

RULES ITERATE (10)

( cell[iteration_number] = iteration_number)


Pivoting the result set using the model clause

Pivoting the result set using the Model Clause

This my goal

DEPTNO NR1 NR2 NR3

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

10 KING CLARK MILLER

30 BLAKE ALLEN TURNER

20 SCOTT FORD JONES

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

/


Pivoting the result set using the model clause1

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]

)


10 g expression filters

10g - Expression Filters

  • Oracle 10g Expression Filter

    • Store filter expressions

    • Select records that comply with the stored filters

      • Or the filters that matchspecific records!

    • Filter expressions express

      • Customer interest

      • Regulation or businessrule

      • Match applicant withjobs and vice versa

        • Jobs have associatedprofile== filter

        • Application has profile== expression filter


Expression filters 10 g

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


Overview of expression filters

Object TypeCar4Sale

Model

Year

Price

Mileage

Overview of Expression Filters

Attribute Set Assignment -

Linking column with filter clause

To AttributeSet and Object Type

Attribute Set

Car4Sale

Table Consumers

Interest

Cid

Zip

Phone

Consumer Interestspecified 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


Expression filters 10 g1

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.


Overview of expression filters using expression filter in table join

Object TypeCar4Sale

Model

Year

Price

Mileage

Overview of Expression FiltersUsing Expression Filter in Table Join

Table Inventory

Mileage

Model

Year

Price

Attribute Set

Car4Sale

Table Consumers

Interest

Cid

Zip

Phone

Consumer Interestspecified 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


Steps to set up expression filters

Steps to set up Expression Filters

  • Create an object type as source for the Expression Attribute Set

  • Defining an attribute set based on the type

  • CREATE OR REPLACE TYPE Car4Sale AS OBJECT

  • ( Model VARCHAR2(20)

  • , Year NUMBER

  • , Price NUMBER

  • , Mileage NUMBER);

  • BEGIN

  • DBMS_EXPFIL.CREATE_ATTRIBUTE_SET ( attr_set => 'Car4Sale‘

  • , from_type => 'YES‘

  • );

  • END;


Expression filters 10 g2

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


Typical usage of expression filters

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…


Business rule example

Business Rule example

Deptno

Emp

  • CREATE OR REPLACE TYPE BusinessRuleConditions AS OBJECT

  • ( Condition1 VARCHAR2(20)

  • , Condition2 VARCHAR2(20)

  • , Condition3 VARCHAR2(20)

  • , Condition4 VARCHAR2(20)

  • , Condition5 VARCHAR2(20)

  • );

deptno

dname

loc

empno

ename

job

sal

Business Rules

  • BEGIN

  • DBMS_EXPFIL.CREATE_ATTRIBUTE_SET

  • ( attr_set => 'BusinessRuleConditions'

  • , from_type => 'YES'

  • );

  • END;

label

table

msg

expression

EMPDEPTEMPDEPTBONUS

EMP1DPT1EMP2DPT2BOS1

001011002012021

  • BEGIN

  • DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET

  • ( attr_set => 'BusinessRuleConditions'

  • , expr_tab => 'Business_Rules'

  • , expr_col => 'br_expression'

  • );

  • END;


Inserting the business rules as expressions

Inserting the business rules as Expressions

Deptno

Emp

deptno

dname

loc

empno

ename

job

sal

  • 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 <> ''PARIS'' ');

Business Rules

label

table

msg

expression

EMPDEPTEMPDEPTBONUS

EMP1DPT1EMP2DPT2BOS1

001011002012021

Condition1 =''SALESMAN'' or Condition2 is nullCondition2 <>''SALES'' or Condition3 <> ''PARIS''(sysdate - to_date(condition3)) <8500012021


Data mining from pl sql

Data Mining from PL/SQL


Pl sql api for data mining dbms frequent itemset

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


Examples of questions suited for data mining with dbms frequent itemset

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…


Business case patterns in movie preferences

Business Case: Patterns in Movie Preferences

  • Three tables with movie data:


Business case patterns in movie preferences1

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?


Performing analysis

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


Simple transaction basket analysis

Simple transaction basket analysis

  • SELECT CAST (itemset as FI_number_NT) itemset

  • , support

  • , length

  • , total_tranx

  • FROM 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

  • )

  • )

  • order

  • by support desc

  • , length


Interpretation of simple analysis

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


Simple transaction basket analysis better presentation of the findings

Simple transaction basket analysis:better presentation of the findings

  • SELECT present_items( CAST (itemset as FI_number_NT)) itemset

  • , support

  • , length

  • , total_tranx

  • FROM 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

  • )

  • )

  • order

  • by support desc

  • , length desc order


Simple transaction basket analysis better presentation of the findings1

Simple transaction basket analysis: better presentation of the findings

  • SELECT present_items( CAST (itemset as FI_number_NT)) itemset

  • , support

  • , length

  • , total_tranx

  • FROM 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

  • )

  • )

  • order

  • by support desc

  • , length desc order


Business case our nephew

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?


Simple transaction basket analysis better presentation of the findings2

Simple transaction basket analysis:better presentation of the findings

  • SELECT select items.column_value "Recommended Movie"

  • from <inline view> it

  • , table(it.itemset) items

  • , movies mve

  • where support > 1

  • and set_contains_item( itemset, '2-6 years' ) = 1

  • and set_contains_item( itemset, 'M' ) = 1

  • and mve.title = items.column_value

  • and mve.title != 'Finding Nemo'


In line view for selecting movie basket combinations that include finding nemo

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


Conclusions

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”


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

Oracle JDeveloper 10.1.3

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.

Onderwerpen voor ODTUG 2006 – 17-21 Juni Washington DC


  • Login