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

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

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

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

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

5. Beyond DBMS_OUTPUTWat is het resultaat van dit code fragment? Pre 10gR2: 10gR2:

6. Beyond DBMS_OUTPUT10gR2 Improvement for DBMS_OUTPUT • Output limit from 255 to 32767 • SET SERVEROUTPUT ON SIZE UNLIMITED

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

8. 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 • /

9. 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 • /

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

11. SQL*Plus Autotrace with DBMS_XPLAN

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

13. Conditional PL/SQL Compilation • alter procedure P compile • plsql_ccflags = 'debug_code:true' reuse settings;

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

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

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

17. B – One Large Commit What takes longer? A – 300 small Commits

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

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

20. Asynchronous CommitExample

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

22. 10g Features and Functions • Data Pump • “Insertable” External Table • Merge improvements • Partition Outer Join • Regular Expressions (regexp) • Flashback in overdrive • Database Recycle Bin

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

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

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

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

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

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

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

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

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

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

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

34. Flashback • Flashback Query • 9iR1 – primitive • Had to open flashback cursors before doing any DML • It worked, but was not “easy”

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

36. Flashback • In 10g • Flashback Query • Flashback Table • Flashback Row History • Flashback Drop • Flashback Database

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

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

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

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

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

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

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

44. Apology For making some slight slide changes. No slight was intended, just better slides.

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

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

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

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

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

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