1 / 71

The very, very latest in oracle database development

Lucas Jellema (AMIS, The Netherlands). The very, very latest in oracle database development. Oracle Open World 2012, San Francisco Public Expertezed Session – Thursday 29 th November 2012. The very very very latest…. <Secret Code>. Tom Kyte to the rescue….

arnav
Download Presentation

The very, very latest in oracle database development

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lucas Jellema (AMIS, The Netherlands) The very, very latest in oracle database development Oracle Open World 2012, San Francisco Public Expertezed Session – Thursday 29th November 2012

  2. The very veryvery latest… <Secret Code>

  3. Tom Kyte to the rescue…

  4. The database in modern architectures Oracle Open World 2012, San Francisco

  5. NO SQL

  6. The Top-3 Earning Employees • What can you say about the result of this query with respect to the question: “Who are our top three earning employees?” Correct Answer Sometimes correct Correct if there are never duplicate salaries Not Correct

  7. In-Line Views

  8. TOP-N Queries in 12c • Last part of a query to be evaluated – to fetch only selected rows from the result set: • To select the next set of rows: select * from emp order by saldesc FETCH FIRST 3 ROWS ONLY; select * from emp order by saldesc OFFSET 3 FETCH NEXT 4 ROWS ONLY;

  9. TOP-n% querying • To query for a percentage of the result set (rather than an absolute number of rows) • And the next batch select * from emp order by saldesc FETCH FIRST 30 PERCENT ROWS ONLY; select * from emp order by saldesc OFFSET (0.3*(select count(*) from emp)) ROWS FETCH NEXT (0.3*(select count(*) from emp)) ROWS ONLY;

  10. BOTTOM-N QuerY in 12c • Return only the last three rows in the ordered result set (in the proper order) • or: select * from emp order by saldesc OFFSET ((select count(*) from emp)-3) ROWS FETCH NEXT 3 ROWS ONLY select * from ( select * from emp order by salasc FETCH FIRST 3 ROWS ONLY ) order by saldesc;

  11. In-line PL/SQL Functions and procedures • Procedures are also allowed in-line • In-Line Functions and Procedures can invoke each other WITH procedure increment( operand in out number , incsize in number) is begin operand:= operand + incsize; end; FUNCTION inc(value number) RETURN number ISl_value number(10):= value; BEGIN increment(l_value, 100); RETURN l_value; end; SELECT inc(sal) from emp

  12. Special ‘business rule’: Default Value • The default values is the value that should be inserted for a column when the client has ignored the column • not provided a value nor indicated NULL • The default value is applied prior to the execution of the Before Row trigger • So :new.<column_value> has the value that will be inserted • The Before Row trigger has no built in way to telling whether the value was provided by the client or supplied as default by the database • Default value is typically used for auditing purposes • Note: default values for columns exposed in UI should be set in the client

  13. Column Default • Columns can have default values • Static or literals • SQL expressions evaluating to a static • Pseudo-columns like USER and CURRENT_DATE • DO NOT USE SYSDATE! DO NOT USE USER! • References to Application Context parameters • sys_context(‘USERENV’, ‘IP_ADDRESS’).. • Some funny value to let the before row trigger know that the real (complex) default must be calculated create table citizens( name varchar2(100) default 'John Doe' , birthdate date default current_date- 1, city varchar2(50) default sys_context('KANE_CTX', 'DEFAULT_CITY' ), zipcode varchar2(8) default 'XYXYXYXYXQQ')

  14. New options with default value alter table emp modify (sal number(10,2) DEFAULT ON NULL 1000 ) alter table emp modify (empno number(5) NOT NULL DEFAULT ON NULL EMPNO_SEQ.NEXTVAL ) create table emp ( empno NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 10), ...)

  15. Attribute Value Attribute Value Application Context • Memory area that enables application developers to define, set, and access key/value pairs • Rapid access in SQL and PL/SQL • Two Application Contexts are always around: • CLIENTCONTEXT and USERENV Application Context Attribute Value Pairs select sys_context('USERENV', 'SESSION_USER') from dual l_user:= sys_context('USERENV', 'SESSION_USER')

  16. Application Context appearances • Per session (default) • Stored in UGA, just like package state • Globally Accessible (shared across all sessions) • Stored in SGA • Associated with a Client Identifier • Attributes in a Globally Accessible Application Context can explicitly be tied to the Client Identifier • And are only accessible to sessions with that Client Identifier

  17. Typical web architecture using connection pool JDBC Connection Pool Session 1 Session 2 Session 3 Session 4 Package A globals Package B Package C

  18. Package state is tied to database session JDBC Connection Pool Session 1 Session 2 Session 3 Session 4 globals Package A globals Package B Package C

  19. Package state is tied to database session – not web session JDBC Connection Pool Session 1 Session 2 Session 3 Session 4 globals Package A globals Package B Package C

  20. Application Context to retain state for light weight end users JDBC Connection Pool Session 1 Session 2 Session 3 Session 4 globals ? Package A globals Package B Package C

  21. Application Context to retain state for light weight end users JDBC Connection Pool Session 1 Session 2 Session 3 Session 4 USERENV USERENV Package A globals Global Context Package C globals globals

  22. Application Context to retain state for light weight end users JDBC Connection Pool Session 1 Session 2 Session 3 Session 4 USERENV USERENV USERENV Package A globals Global Context Package C globals globals

  23. package globals: the state of the package in a session • This state is lost when the package is recompiled • That is undesirable in a highly available environment Package

  24. package globals can be replaced by Application Context • The Application Context is untouched by recompilation of the package • All ‘globals’ in the application context retain their values Package Application Context

  25. EBR to kill planned downtime (because of application upgrade) Application X VERSION 1 Application X VERSION 2 Release 2 Release 3 Base Release

  26. Time travelling

  27. Flashback • Introduced in 9i • Based on UNDO • Initially only for recovery • As of 11g – Total Recall option with Flashback Data Archive • Controlled history keeping • Look back into history • Query trends (version history) • Difference reporting • Audit trails (Replace journaling tables) • Require trick for transaction history: WHO? • Also: when is the start of history?

  28. OOW 2012 session comes to the rescue • CON8511 - Temporal Database Capabilities with the Latest Generation of Database Technology

  29. Total Recall - Flashback Data Archive Improvements • Complete schema evolution support: all table definition, partitioning, and space management DDLs are supported on FDA-enabled tables. • The metadata information for tracking transactions including the user context is now tracked. • This could mean that journaling tables are now officially deprecated • And the current contents of journaling tables can even be migrated to Flashback Data Archive • Introduction of SQL 2011 Valid Time Temporal Modeling

  30. Total Recall • Import and export of history • Support for import and export using Data Pump for FDA-enabled tables. Data Pump can now be used to export and import an FDA-enabled base table along with its schema-evolution metadata and historical row versions. • Construct and manipulate the Flashback Data Archive • import user-generated history • Restore points: Support for the use of named restore points in AS OF and versions queries has been added. • Total Recall will (in all likelihood) be part of every edition of the database – including SE

  31. Valid time temporal modeling • Validity (or effectivity) of facts recorded in a database is frequently specified through dates or timestamps • For example begin date and [derived] end date of a price, membership, allocation, certificate, agreement • This valid time can differ from the transaction time at which a record is entered into the database • Multiple entries with different, non-overlapping valid-time periods can exist for a single entity • In 12c the notion of Valid Time is introduced into the Oracle Database • The valid-time dimension consists of two date-time columns specified in the table definition (create or alter) • These Valid Time columns specify the period during which a record is valid • A table can have multiple valid_time markers

  32. Creating a table with valid time dimension • Table with explicit valid time columns: • Table with valid time dimension and implicit columns: columns valid_time_start and valid_time_end (TIMESTAMP) are added implicitly CREATE TABLE EMP ( employee_number NUMBER , salary NUMBER , department_id NUMBER , name VARCHAR2(30) , hiredate TIMESTAMP , firedate TIMESTAMP , PERIOD FOR user_time (hiredate, firedate) ); CREATE TABLE EMP ( employee_number NUMBER , salary NUMBER , department_id NUMBER , name VARCHAR2(30) , PERIOD FOR contract_time );

  33. Valid time aware flashback queries • Select all employees who were employed at a certain moment in time • Perform all queries for records that are valid at a certain point in time • Return all records currently (session time) valid • Return all records (default) SELECT * FROM EMP AS OF PERIOD FOR user_time TO_TIMESTAMP('01-JUN-2012 12.00.01 PM') EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time ( 'ASOF' , TO_TIMESTAMP('29-JUL-12 12.00.01 PM') ); EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('CURRENT'); EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('ALL');

  34. Database in modern architecture Mobile WS Business Tier Cache/Grid (L1, L2, L3) Enterprise Service Bus Services Standard Applications Database Database LegacyApplications

  35. Multi Tier Architecture Mobile WS Business Tier Cache/Grid (L1, L2, L3) Enterprise Service Bus HTTP REST HTTP SOAP FTP/WEBDAV JDBCJPA (H/EL) Services DB QRCN HTTP JMX, JMX Database Monitor, Trace, Audit Stored Procedures EncapsulationDecoupling Caching Business Logic Authentication & Fine Grained Authorization SQL

  36. Application architecture:Drive application from meta data • Agility • Design Time at Run Time • Define part of the application behavior and appearance through meta-data (outside the base source code) • The default settings are defined by developers and deployed along with the application • Read and interpreted at run time • Manipulated and re-read and re-interpreted at run time • Note: very similar to the waythe database operates: • Data Dictionary is the meta-data driving the behavior of the database Application meta

  37. Separate base data and customized data • If a value is changed during site-level implementation • Or run time customization • It should be kept apart from the base ‘meta-data’ • To prevent overwriting customized data when the new release arrives • To allow for (temporarily) reverting to base data • A simple solution: the Complex View with two underlying tables approach • Note: Select… For Update Ofis not allowed ORIGINAL_NAME IO trg Customized Values New release BaseValues

  38. Replace the original single table with a two-table base/custom split • rename <original> to <base> • create table <customizations>as select * from base where rownum = 0 • create or replace view <original>as select * from <customizations>union allselect * from <base> b left outer join <customizations> c on (b.id = c.id)where c.rowid is null

  39. Replace the original single table with a two-table base/custom split (2) • create or replace triggerhandle_insert_trginstead of inserton originalfor each row begin  insert into <customizations> (col, col2,…) values(:new.col, :new.col2,…);end; • create or replace triggerhandle_update_trginstead of update on originalfor each row begin  update <customizations>  set col = :new.col, …  where id = :new.id  ;  if sql%rowcount = 0   then    insert into <customizations> (id, col, col2,…)      (select id, :new.col, :new.col2 from base where  id = :new.id);  end if; end;

  40. Very similar to the architecture of Pluggable databases New release of Oracle Database ROOT PDB

  41. Application architecture: NO SQL • NO SQL • Complex SQL is hidden away inside the database • Cache to not have to query all the time from the database • … and to not take the overhead of a commit for not so important data • Process first – in memory, on middle tier (BigData and CEP) - and only persist what is useful Web Browser JEE Application Server NO SQL RDBMS SQL

  42. Query Result Change Notification • Continuous Query Notification: • Send an event when the result set for a query changes • Background process calls PL/SQL Handler or Java Listener or OCI client when thecommit has occurred • Event contains rowidof changed rows • Used for: • Refreshing specificdata caches (middletier, global context) • (custom) Replication Java Listener PL/SQL

  43. Continuous processing of data streams using CQL • Aggregation, Spot deviation, Match on complex patterns

  44. Who is afraid of Red, Yellow and blue • Table Events • Column Seq number(5) • Column Payload varchar2(200)

  45. Solution using Lead • With LEAD it is easy to compare a row with its successor(s) • As long as the pattern is fixed, LEAD will suffice with look_ahead_events as ( SELECT e.* , lead(payload) over (order by seq) next_color , lead(payload,2) over (order by seq) second_next_color FROM events e ) select seq from look_ahead_events where payload ='red' and next_color ='yellow' and second_next_color='blue'

  46. Find the pattern red, yellow and blue • Using the new 12c Match Recognize operator for finding patterns in relational data SELECT * FROM events MATCH_RECOGNIZE ( ORDER BY seq MEASURES RED.seq AS redseq , MATCH_NUMBER() AS match_num ALL ROWS PER MATCH PATTERN (RED YELLOW BLUE) DEFINE RED AS RED.payload ='red', YELLOW AS YELLOW.payload ='yellow', BLUE AS BLUE.payload ='blue' ) MR ORDER BY MR.redseq , MR.seq;

  47. Match_recognize for finding patterns in relational data • The expression MATCH_RECOGNIZE provides native SQL support to find patterns in sequences of rows • Match_recognize returns Measures for selected (pattern matched) rows • Similar to MODEL clause • Match Conditions are expressed in columns from the Table Source, aggregate functions and pattern functions FIRST, PREV, NEXT, LAST • Patterns are regular expressions using match conditions to express a special sequence of rows satisfying the conditions Table Source & Where Match_Recognize Process and Filter Select & Order By

  48. Did we ever consecutively hire three employees in the same job? • Find a string of three subsequent hires where each hire has the same job • Order by hiredate, pattern is two records that each have the same job as their predecessor SELECT * FROM EMP MATCH_RECOGNIZE ( ORDER BY hiredate MEASURES SAME_JOB.hiredate AS hireday , MATCH_NUMBER() AS match_num ALL ROWS PER MATCH PATTERN (SAME_JOB{3}) DEFINE SAME_JOB AS SAME_JOB.job = FIRST(SAME_JOB.job) ) MR

  49. The shopping algorithm

  50. The Shopping algorithm • shopForItem Item ( String itemName) {driveToShop; Item item = buyItemAtShop ( itemName);driveHomeFromShop; return item;}

More Related