1	"Thick Database" Techniques for Fusion (and other web) Developers Dr. Paul Dorsey Dulcian, Inc.
2	Background Fusion technology stack is large and complex. Hard to make the transition into the J2EE environment. Host of different tools, programming languages, architectures, and technologies Projects often have the illusion of progress. Building functioning, scalable production software often becomes an impossible task.
3	Why do OO people avoid the database? Culture? Lack of knowledge? Clinical pathology?
4	“Frameworkaphobia” Definition: An irrational avoidance of frameworks (particularly non-open source) Diagnostic Indications: Desire to build everything him/herself “If I don’t build it, it must stink.” “If Oracle built it, it must really stink.” Irrational avoidance of Application Development Framework – Business Components (ADF BC) Symptoms: Higher than expected project cost Project failure Treatment No known cure Some success with short leashes and large bats Related conditions Megalomania Paranoid delusions
5	“Database Avoidance Syndrome” Definition: An aversion to placing any logic in the database Diagnostic Indications: “We should be database-independent.” “Databases are old fashioned. Everyone is coding this way.” Symptoms: Twice as much code as is necessary Performance is 10 times slower. Network traffic is 100 times as great. Four times the load on the database server Three times the development time
6	“SOAphilia” Definition: Irrational desire to refactor small systems to use web services and BPEL Diagnostic Indications: Ownership of 72 BPEL books Desire to use BPEL for data-centric processes
7	Oracle Architecture First-rate Service Oriented Architecture (SOA)-centric environment. Built from an OO developer’s perspective: Lacks much of the vision that would make Designer users comfortable “Not-so-subtle” encouragement to place business rules enforcement in the middle tier, coded as Java Can be used to articulate data-centric complex business processes, using portions of the architecture Business Process Execution Language (BPEL) Can lead to applications with poor performance because of the number of round trips needed between the middle tier and the database.
8	“Thick Database” Defined (1) Micro-Service-Oriented-Architecture (M-SOA) approach Service Component Architecture (SCA) Division between the database and user interface (UI) portions. Two key features involved in "thick database thinking": Nothing in the UI ever directly interacts with a database table. All interaction is accomplished through database views or APIs. Nearly all application behavior (including screen navigation) is handled in the database. Thick database does not simply mean stuffing everything into the database and hoping for the best.
9	“Thick Database” defined (2) Creating a thick database makes your application UI technology-independent. Creates reusable, UI technology-independent views and APIs. Reduces the complexity of UI development. Database provides needed objects. Reduces the burden on the UI developer
10	Database vs. UI Technology Stack-Independence Database Oracle will add features. DBMS will not internally refactor. Existing stack “works.” Huge DBA learning curve Huge cost of switching UI Technology Stack Java EE or .Net? AppEx FLEX All environments change Redesign assured Every year BRIM^® has been rebuilt.
11	Thick Database Benefits Minimizes development risk Helps build working applications that scale well. Benefits Better performance (10X) Less Network traffic (100X) Less code (2X) Fewer app servers (3X) Fewer DB resources (2X) Faster Development (2X)
12	Easier to Refactor UI technology stack changes are common. .Net Java EE battle rages on. Web architecture is more volatile than the database platform. Defense against the chaos of a rapidly evolving standard Test: What is the probability that your web UI standards will be the same in 18 months?
13	How Thick is too Thick? What would happen if 100% of all UI logic were placed in the database? Tabbing out of a field LOV populated from database Page navigation Pathologically complete way to implement the thick database approach. A system built this way would be sub-optimal. But it works
14	How Thin is too Thin? Can a skilled team successfully build applications that are 100% database “thin”? Requires a highly skilled team. Minimize round trips ANY middle tier technology (e.g. BPEL) can also be a performance killer. Possible but difficult
15	Thick Database Development Process Two portions of an application can be coded independently Teams can work in isolation until substantive portions are working. First version of the UI is built within a few days Use as testing environment for the database team Feedback can be received from users. Use Agile process Minimal design work done to produce a partially working system. Additional functionality created in an iterative design process.
16	UI and Database Development UI and database development take place at the same time. UI team takes the APIs and incorporates them into the application. Database team makes them work.
17	Interface Stubbing Stub out the code for the views and APIs. select <values> from dual APIs = functions that return a correct value (usually hard-coded). Interfaces will change as the application matures.
18	De-Normalized Views The idea: Convert relational data into something that will make user interface development easier Easiest way to separate data representation in the front-end from the real model. The solution: Use a view with a set of INSTEAD-OF triggers
19	De-Normalized view create or replace view v_customer as select c.cust_id, c.name_tx, a.addr_id, a.street_tx, a.state_cd, a.postal_cd from customer c left outer join address a on c.cust_id = a.cust_id
20	INSTEAD-OF Insert create or replace trigger v_customer_ii instead of insert on v_customer declare v_cust_id customer.cust_id%rowtype; begin if :new.name_tx is not null then insert into customer (cust_id,name_tx) values(object_seq.nextval, :new.name_tx) returning cust_id into v_cust_id; if :new.street_tx is not null then insert into address (addr_id,street_tx, state_cd, postal_cd, cust_id) values (object_seq.nextval,:new.street_tx, :new.state_cd,:new.postal_cd, v_cust_id); end if; end;
21	INSTEAD-OF Delete create or replace trigger v_customer_id instead of delete on v_customer begin delete from address where cust_id=:old.cust_id; delete from customer where cust_id=:old.cust_id; end;
22	INSTEAD-OF Update create or replace trigger v_customer_iu instead of update on v_customer begin update customer set name_tx = :new.name_tx where cust_id = :old.cust_id; if :old.addr_id is not null and :new.street_tx is null then delete from address where addr_id=:old.addr_id; elsif :old.addr_id is null and :new.street_tx is not null then insert into address(…) values (…); else update address set… where addr_id=:old.addr_id; end if; end;
23	Function-Based Views: Collections
24	Using Function-Based Views Sometimes it is just not possible to represent all required functionality in a single SQL statement. Denormalized view cannot be built. Oracle provides a different mechanism: Collections Allow you to hide the data separation, as well as all of the transformation logic.
25	What is a collection? Definition: A collection is an ordered group of elements, all of the same type, addressed by a unique subscript. Implementation: Since all collections represent data, they are defined as data types.
26	Collections: Pros & Cons Good news Usually faster Cleaner code Great for UI views Bad news Not always faster Somewhat annoying syntax
27	Why use collections? Logical reason: Collections allow you to articulate and manipulate sets of data. Technical reason: Processing data in sets is “usually” faster than doing so one element at a time. Physical reason: Manipulating sets in memory is “usually” 100 times faster than manipulating sets on the storage device.
28	Possible Issues Technical problem: Amount of memory is limited (especially in 32-bit architecture) Economic problem: Storage is cheap – memory is NOT. Learning curve: People who are used to old habits of processing one row at a time (since COBOL days) will have problems working with sets.
29	Nested Tables
30	Nested Tables (1) Nested tables – arbitrary group of elements of the same type with sequential numbers as a subscript Undefined number of elements (added/removed on the fly) Available in SQL and PL/SQL Very useful in PL/SQL! (but not in tables)
31	Nested Tables (2) Definition: declare type NestedTable is table of ElementType; ... create or replace type NestedTable is table of ElementType;
32	Nested Tables (3) Nested tables are NOT dense: You can remove objects from inside of the array Size of the nested table MAY OR MAY NOT equal the subscript of the last element Built-in NEXT and PREVIOUS can go over the gap
33	Nested Tables - Example 1 declare type month_nt is table of VARCHAR2(20); v_month_nt month_nt:=month_nt(); i number; begin v_month_nt.extend(3); v_month_nt(1):='January'; v_month_nt(2):='February'; v_month_nt(3):='March'; v_month_nt.delete(2); DBMS_OUTPUT.put_line('Count:'\|\|v_month_nt.count); DBMS_OUTPUT.put_line('Last:'\|\|v_month_nt.last); i:=v_month_nt.first; loop DBMS_OUTPUT.put_line(v_month_nt(i)); i:=v_month_nt.next(i); if i is null then exit; end if; end loop; end;
34	Built-ins for Nested Tables Built-in functions: First Last Count Exists (index) Next (index) Previous (index) Extend([count]) Delete([index][,index])
35	More About Nested Tables Nested tables can be used in SQL queries via the special operator: TABLE Allows hiding of complex procedural logic “under the hood” Nested table type must be declared as a user-defined type (CREATE OR REPLACE TYPE…)
36	Nested Tables – Example 2a Specify exactly what is needed as output and declare the corresponding collection: Create type lov_oty is object (id_nr NUMBER, display_tx VARCHAR2(256)); Create type lov_nt as table of lov_oty;
37	Nested Tables - Example 2b Write a PL/SQL function to hide all required logic function f_getLov_nt (i_table_tx,i_id_tx,i_display_tx,i_order_tx) return lov_nt is v_out_nt lov_nt := lov_nt(); begin execute immediate 'select lov_oty(' \|\|i_id_tx\|\|','\|\|i_display_tx\|\| ')'\|\| ' from '\|\|i_table_tx\|\| ' order by '\|\|i_order_tx bulk collect into v_out_nt; return v_out_nt; end;
38	Nested Tables - Example 2c Test SQL statement with the following code: select id_nr, display_tx from table( cast(f_getLov_nt ('emp', 'empno', 'ename\|\|''-''\|\|job', 'ename') as lov_nt) )
39	Nested Tables - Example 2d Create a VIEW on the top of the SQL statement. Completely hides the underlying logic from the UI INSTEAD-OF triggers make logic bi-directional Minor problem: There is still no way of passing parameters into the view other than some kind of global. Create or replace view v_generic_lov as select id_nr, display_tx from table( cast(f_getLov_nt (GV_pkg.f_getCurTable, GV_pkg.f_getPK(GV_pkg.f_getCurTable), GV_pkg.f_getDSP(GV_pkg.f_getCurTable), GV_pkg.f_getSORT(GV_pkg.f_getCurTable)) as lov_nt) )
40	Optimizing Database Processing
41	Associative Arrays (1) An associative array is a collection of elements that use arbitrary numbers and strings for subscript values PL/SQL only Still useful
42	Associative Arrays (2) Definition: declare type NestedTable is table of ElementType index by Varchar2([N]); ... type NestedTable is table of ElementType index by binary_integer;
43	Associative Arrays - Example 1 declare type dept_rty is record (deptNo number, extra_tx VARCHAR2(2000)); type dept_aa is table of dept_rty index by binary_integer; v_dept_aa dept_aa; begin for r_d in (select deptno from dept) loop v_dept_aa(r_d.deptno).deptNo:=r_d.deptno; end loop; for r_emp in (select ename, deptno from emp) loop v_dept_aa(r_emp.deptNo).extra_tx:= v_dept_aa(r_emp.deptNo).extra_tx\|\| ' '\|\|r_emp.eName; end loop; end;
44	Built-ins for Associative Arrays Built-in functions: First Last Count Exists (index) Next (index) Previous (index) Delete([index][,index])
45	More About Associative Arrays Index by VARCHAR2 instead of by BINARY_INTEGER Cannot be used in a FOR-loop Allow creation of simple composite keys with direct access to the row in memory
46	Associative Arrays - Example 2a Prepare memory structure declare type list_aa is table of VARCHAR2(2000) index by VARCHAR2(256); v_list_aa list_aa; cursor c_emp is select ename, deptno,to_char(hiredate,'q') q_nr from emp; v_key_tx VARCHAR2(256); begin for r_d in (select deptno from dept order by 1) loop v_list_aa(r_d.deptno\|\|'\|1'):= 'Q1 Dept#' \|\|r_d.deptno\|\|':'; v_list_aa(r_d.deptno\|\|'\|2'):= 'Q2 Dept#' \|\|r_d.deptno\|\|':'; ... end loop;
47	Associative Arrays - Example 2b Process data and present results ... for r_emp in c_emp loop v_list_aa(r_emp.deptno\|\|'\|'\|\|r_emp.q_nr):= list_aa(r_emp.deptno\|\|'\|'\|\|r_emp.q_nr)\|\| ' '\|\|r_emp.ename; end loop; v_key_tx:=v_list_aa.first; loop DBMS_OUTPUT.put_line (v_list_aa(v_key_tx)); v_key_tx:=v_list_aa.next(v_key_tx); exit when v_key_tx is null; end loop; end;
48	Bulk Operations
49	Bulk operations Operations on SETs BULK loading into the memory BULK processing Manipulation of SETs (MULTISET operators)
50	BULK COLLECT (1) BULK COLLECT clause The idea: Fetch a group of rows all at once to the collection Control a number of fetched rows (LIMIT) Risks: Does not raise NO_DATA_FOUND Could run out of memory
51	BULK COLLECT (2) Syntax: select … bulk collect into Collection from Table; update … returning … bulk collect into Collection; fetch Cursor bulk collect into Collection;
52	BULK COLLECT example declare type emp_nt is table of emp%rowtype; v_emp_nt emp_nt; cursor c_emp is select * from emp; begin open c_emp; loop fetch c_emp bulk collect into v_emp_nt limit 100; p_proccess_row (v_emp_nt); exit when c_emp%NOTFOUND; end loop; close c_emp; end;
53	FORALL (1) FORALL command The idea: Apply the same action for all elements in the collection. Have only one context switch between SQL and PL/SQL Risks: Special care is required if only some actions from the set succeeded
54	FORALL (2) Syntax: forall Index in lower..upper update … set … where id = Collection(i) ... forall Index in lower..upper execute immediate ‘…’ using Collection(i);
55	FORALL (3) Restrictions Only a single command can be executed. Must reference at least one collection inside the loop All subscripts between lower and upper limits must exist. Cannot work with associative array INDEX BY VARCHAR2 Cannot use the same collection in SET and WHERE Cannot refer to the individual column on the object/record (only the whole object)
56	FORALL Example declare type number_nt is table of NUMBER; v_deptNo_nt number_nt:=number_nt(10,20); begin forall i in v_deptNo_nt.first() ..v_deptNo_nt.last() update emp set sal=sal+10 where deptNo=v_deptNo_nt(i); end;
57	MULTISET Operations (1) MULTISET operations The idea: Combine the results of two nested tables into a single nested table. Commands: MULTISET EXCEPT MULTISET INTERSECT MULTISET UNION
58	MULTISET Example declare v_emp1_nt number_nt; v_emp2_nt number_nt; v_emp3_nt number_nt; begin select empno bulk collect into v_emp1_nt from emp where sal>1000; select empno bulk collect into v_emp2_nt from emp where job!='MANAGER'; select v_emp1_nt multiset union distinct v_emp2_nt into v_emp3_nt from dual; dbms_output.put_line('A OR B: '\|\|v_emp3_nt.count); select v_emp1_nt multiset except distinct v_emp2_nt into v_emp4_nt from dual; dbms_output.put_line('A MINUS B: '\|\|v_emp3_nt.count); select v_emp1_nt multiset intersect distinctv_emp2_nt into v_emp5_nt from dual; dbms_output.put_line('A AND B: '\|\|v_emp3_nt.count); end;
59	Conclusions The #1 critical success factor for any web development is effective utilization of the DB PL/SQL is not irrelevant ( and it continues to improve) Code that needs to access the DB is faster if it is in the DB Database independence is irrelevant UI technology independence is more important Just because everyone is moving logic to the middle tier, does not make it a smart idea
60	Share your Knowledge: Call for Articles/Presentations IOUG – The SELECT Journal select@ioug.org 3^rd annual Best Practices Booklet Best practices and tips for managing an Oracle Database, geared toward developers and DBAs 500-1,000 words long with a specific focus Deadline is May 15, 2008. For 2009 – focus will be Fusion tips ODTUG – Technical Journal pubs@odtug.com
61	Dulcian’s BRIM^® Environment Full business rules-based development environment For Demo Write “BRIM” on business card
62	Contact Information Dr. Paul Dorsey – paul_dorsey@dulcian.com Dulcian website - www.dulcian.com