KC Server Development Nieuwe SQL en PL SQL

KC Server Development Nieuwe SQL en PL/SQL features in Oracle 10 g. R 2 en R 1 incl. reprise van ODTUG presentatie 15 november 2005 KC Server Development – 15 november 2005 Oracle 10 g’s Finest 1

Agenda 10 g. R 2 – Een paar hoogtepuntjes uit release 2 van Oracle 10 g (juli 2005) 10 g. R 1 – Een bonte verzameling features en functies uit 10 g. R 2 (juni 2004) De ODTUG 2005 presentatie: § Oracle 10 g’s Finest—The Top 3 SQL and PL/SQL Features New in 10 g Diner Workshop § AMIS LAB database (10 g. R 2) KC Server Development – 15 november 2005 Oracle 10 g’s Finest 2

10 g. R 2 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 10 x snellere transacties - Asynchronous Commit Small fry KC Server Development – 15 november 2005 Oracle 10 g’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 10 g’s Finest 4

Beyond DBMS_OUTPUT Wat is het resultaat van dit code fragment? Pre 10 g. R 2: KC Server Development – 15 november 2005 Oracle 10 g’s Finest 10 g. R 2: 5

Beyond DBMS_OUTPUT 10 g. R 2 Improvement for DBMS_OUTPUT Output limit from 255 to 32767 SET SERVEROUTPUT ON SIZE UNLIMITED KC Server Development – 15 november 2005 Oracle 10 g’s Finest 6

Will the update statement always succeed? create table emp ( empno number , ename varchar 2(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 10 g’s Finest 7

Statement level Constraint Checking Will this statement succeed? add constraint sal_check (sal < 6000) / alter table emp add constraint sal_check (sal < 6000) / update emp set sal = sal + 2000 / KC Server Development – 15 november 2005 Oracle 10 g’s Finest 8

Oracle 10 g. R 2 – 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 10 g’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 10 g’s Finest 10

SQL*Plus Autotrace with DBMS_XPLAN KC Server Development – 15 november 2005 Oracle 10 g’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 10 g’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 10 g’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. . . KC Server Development – 15 november 2005 Oracle 10 g’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. § § 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 § 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. 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 10 g’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 10 g’s Finest 16

What takes longer? A – 300 small Commits B – One Large Commit KC Server Development – 15 november 2005 Oracle 10 g’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 10 g’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 10 g’s Finest 19

Asynchronous Commit Example KC Server Development – 15 november 2005 Oracle 10 g’s Finest 20

10 g. R 2 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 10 g’s Finest 21

10 g 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 10 g’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 10 x for import and 2 x 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 10 g’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 10 g’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 10 g’s Finest 25

Example of Merge in 10 g 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 10 g’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 10 g’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 10 g’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 10 g’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 10 g’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 10 g’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 10 g’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 LAST_NAME ------------------------Steven King Steven Markle Stephen Stiles KC Server Development – 15 november 2005 Oracle 10 g’s Finest 33

Flashback Flashback Query § 9 i. R 1 – primitive § Had to open flashback cursors before doing any DML § It worked, but was not “easy” KC Server Development – 15 november 2005 Oracle 10 g’s Finest 37

Flashback Flashback Query § § § 9 i. R 2 – 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 10 g’s Finest 38

Flashback In 10 g § § § Flashback Query Flashback Table Flashback Row History Flashback Drop Flashback Database KC Server Development – 15 november 2005 Oracle 10 g’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 where ename = ‘SCOTT’ timestamp a and b ENAME SAL -----SCOTT 3000 SCOTT 3300 SCOTT 3630 … KC Server Development – 15 november 2005 Oracle 10 g’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 10 g’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 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 10 g’s Finest 56

Other 10 g 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 10 g’s Finest 57

Oracle 10 g’s Finest—The Top 3 SQL and PL/SQL Features New in 10 g Lucas Jellema AMIS, The Netherlands KC Server Development – 15 november 2005 Oracle 10 g’s Finest 58

Agenda Oracle 10 g 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 10 g’s Finest 59

10 g 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 10 g’s Finest 60

Apology For making some slight slide changes. No slight was intended, just better slides. KC Server Development – 15 november 2005 Oracle 10 g’s Finest 61

Oracle 10 g 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 Oracle 9 i 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 10 g’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 | |new join syntax| 10 g 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 10 g’s Finest 63

The MODEL Clause – like processing the result set in a spreadsheet select from 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 (10 g) Model order by Outer Query in case of In-Line View Result Set to Client KC Server Development – 15 november 2005 Oracle 10 g’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 10 g’s Finest 65

Model vs. Analytical Functions MODEL § 10 g § 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, 9 i. R 2 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 10 g’s Finest 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 10 g’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 10 g’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 10 g’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 10 g’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 10 g’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 (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 10 g’s Finest 72

Example Model Clause COUNTRY ----------Italy 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 10 g’s Finest 73

Visualization of MODEL clause KC Server Development – 15 november 2005 Oracle 10 g’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 10 g’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 10 g’s Finest 76

Pivoting the result set using the Model Clause DEPTNO -----10 30 20 NR 1 -----KING BLAKE SCOTT NR 2 -----CLARK ALLEN FORD NR 3 -----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 10 g’s Finest 77

Pivoting the result set using the Model Clause select deptno , nr 1 , nr 2 , nr 3 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) nr 1 , lpad(' ', 10) nr 2 , lpad(' ', 10) nr 3) rules upsert ( nr 1 [0] = ename [1] , nr 2 [0] = ename [2] , nr 3 [0] = ename [3] ) KC Server Development – 15 november 2005 Oracle 10 g’s Finest 78

10 g - Expression Filters Oracle 10 g 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 q Jobs have associated profile== filter q Application has profile == expression filter KC Server Development – 15 november 2005 Oracle 10 g’s Finest 79

Expression Filters (10 g) “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 10 g’s Finest 80

Overview of Expression Filters Attribute Set Car 4 Sale Attribute Set Assignment Linking column with filter clause To Attribute. Set and Object Type Table Consumers Cid Zip Phone Interest Object Type Car 4 Sale 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 , Car 4 Sale('Mustang', 2000, 18000 , 22000). get. Varchar() ) = 1 KC Server Development – 15 november 2005 Oracle 10 g’s Finest 81

Expression Filters (10 g) Basic steps to create and use an expression column § Define an attribute set § Create user table with expression column (or add new varchar 2 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 10 g’s Finest 82

Overview of Expression Filters Using Expression Filter in Table Join Table Inventory Attribute Set Car 4 Sale Model Year Price Mileage Table Consumers Cid Zip Phone Interest Object Type Car 4 Sale 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 , Car 4 Sale(model, year, price, mileage). get. Varchar() ) = 1 KC Server Development – 15 november 2005 Oracle 10 g’s Finest 83

Steps to set up Expression Filters Create an object type as source for the Expression Attribute Set CREATE OR REPLACE TYPE Car 4 Sale AS OBJECT ( Model VARCHAR 2(20) , Year NUMBER , Price NUMBER , Mileage NUMBER ); Defining an attribute set based on the type BEGIN DBMS_EXPFIL. CREATE_ATTRIBUTE_SET ( attr_set => 'Car 4 Sale‘ , from_type => 'YES‘ ); END; KC Server Development – 15 november 2005 Oracle 10 g’s Finest 84

Expression Filters (10 g) 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 => 'car 4 sale', from_type => 'yes')) KC Server Development – 15 november 2005 Oracle 10 g’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 10 g’s Finest 91

Business Rule example Deptno Emp empno ename job sal deptno dname Business Rules label table msg EMP 1 DPT 1 EMP 2 DPT 2 BOS 1 CREATE OR REPLACE TYPE loc Business. Rule. Conditions AS OBJECT ( Condition 1 VARCHAR 2(20) , Condition 2 VARCHAR 2(20) , Condition 3 VARCHAR 2(20) , Condition 4 VARCHAR 2(20) , Condition 5 VARCHAR 2(20) ); KC Server Development – 15 november 2005 Oracle 10 g’s Finest BEGIN expression DBMS_EXPFIL. CREATE_ATTRIBUTE_SET ( attr_set => EMP 001 'Business. Rule. Conditions' DEPT 011 , from_type => 'YES' EMP 002 ); BEGIN 012 DEPT END; DBMS_EXPFIL. ASSIGN_ATTRIBUTE_SET BONUS 021 ( attr_set => 'Business. Rule. Conditions' , 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 EMP 1 DPT 1 EMP 2 DPT 2 BOS 1 EMP DEPT BONUS 001 011 002 012 021 sal deptno dname loc INSERT INTO Business_rules VALUES ('EMP 1', '001', 'EMP', 'Condition 1 =''SALESMAN'' or Condition 2 is null'); INSERT INTO Business_rules VALUES ('EMP 2', '002', 'EMP', '(sysdate - to_date(condition 3)) <8500'); INSERT INTO Business_rules VALUES ('DPT 1', '001', 'DEPT', 'Condition 2 <>''SALES'' or Condition 3 expression <> ''PARIS'' '); Condition 1 =''SALESMAN'' or Condition 2 is null Condition 2 <>''SALES'' or Condition 3 <> ''PARIS'' (sysdate - to_date(condition 3)) <8500 012 021 KC Server Development – 15 november 2005 Oracle 10 g’s Finest 93

Data Mining from PL/SQL KC Server Development – 15 november 2005 Oracle 10 g’s Finest 96

PL/SQL API for Data Mining: DBMS_FREQUENT_ITEMSET Oracle 9 i and 10 g 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 10 g’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 10 g’s Finest 98

Business Case: Patterns in Movie Preferences Three tables with movie data: KC Server Development – 15 november 2005 Oracle 10 g’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 10 g’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 10 g’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 ) ) support desc length KC Server Development – 15 november 2005 Oracle 10 g’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 10 g’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 ) ) support desc length desc order KC Server Development – 15 november 2005 Oracle 10 g’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 ) ) support desc length desc order KC Server Development – 15 november 2005 Oracle 10 g’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 10 g’s Finest 107

Simple transaction basket analysis: better presentation of the findings SELECT from , , where and and select items. column_value "Recommended Movie" 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 10 g’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 10 g’s Finest 109

Conclusions 10 g. R 1 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 10 g’s Finest 111

Onderwerpen voor ODTUG 2006 – 17 -21 Juni Washington DC Oracle 9 i en 10 g § § § § Oracle 10 g Web. Forms § § § SQL en PL/SQL Oracle Text Oracle Spatial XML DB HTML DB Lite Express Edition JDAPI Web. Util PJC Java/J 2 EE integratie Java. Script Oracle Warehouse Builder Oracle Discoverer … Oracle JDeveloper 10. 1. 3 § § § Oracle BPEL Oracle Inter. Connect XML Publisher Oracle en Open Source § § § ADF en Spring Hibernate en ADF My. SQL vs. Express Edition Business Rules Software Engineering § ADF Faces JHeadstart Oracle Development Tools & Jira, CVS, JUnit, Ant/Maven etc. … KC Server Development – 15 november 2005 Oracle 10 g’s Finest 112