oracle 9i & 10g enhancements
TRANSCRIPT
1.
10g PL/SQL10g PL/SQL
• SQL Enhancements
• SQLPLUS Enhancements
• External Tables
• Timestamp Datatypes
• PL/SQL enhancements
2.
Final Schedule For Oracle10g Workshop
DATE DAY Topics Covered Conducted By
20-Sep-06 1 10g SQL & PL/SQL – Hands on Harsh & Vibhuti
20-Sep-06 1 AS Forms 10g – Hands on (Part 1) Pranali
21-Sep-06 2 AS Forms 10g – Hands on (Part 2) Pranali
21-Sep-06 210g Architecture, Grid & RAC10g Backup, Recovery & Network
Vibhuti
22-Sep-06 3 10g Performance Tuning Pranali
22-Sep-06 3 Final Test, Quiz and Games Vibhuti, Harsh, Pranali
3.
SQL EnhancementsSQL Enhancements
4.
• Multi-Table Insert
• MERGE Statement
• GROUPBY Clause Enhancements
• CASE enhancements
• JOINS – new syntax
• Introduction To Analytical Functions
• MODEL Clause
• Regular Expression
5.
Multi-Table InsertMulti-Table Insert
• Using this feature the INSERT … SELECT
statement can be used to insert rows into multiple tables as part of a singe DML statement
Types:• Unconditional Insert • Conditional Insert• Conditional First Insert• Pivoting Insert
6.
Example:
INSERT ALL Multi table InsertWHEN SAL > 10000 THENINTO sal_hist VALUES ( EMPID ,
HIREDATE,SAL)WHEN MGR > 200 THENINTO mgr_hist VALUES (EMPID , MGR ,SAL )
SELECT empno EMPID , hire_date HIREDATE ,Sal SAL , mgr MGR
FROM emp_old WHERE empno > 20 ;
7.
Current Scenario :
INSERT INTO revenue (person,month,revenue)SELECT person, 'Jan', rev_jan FROM revert UNION ALLSELECT person, 'Feb', rev_feb FROM revert UNION ALLSELECT person, ‘Apr‘ ,rev_mar FROM revert ;
In 9i/10g :Insert ALL into revenue values (person ,'JAN', rev_jan) into revenue values (person, 'FEB', rev_feb) into revenue values (person,‘ MAR', rev_mar)select person, rev_jan, rev_feb, rev_mar, rev_apr, rev_may,
rev_jun from revert;
8.
Example 1:Unconditional INSERT
INSERT ALL
INTO hbg2 VALUES (emp_no,name )
INTO hbg1 VALUES (emp_no,name)
SELECT emp_no, name
FROM hbg
WHERE emp_no > 2;
9.
Example 2: Conditional INSERT ALL
INSERT ALLWHEN emp_no > 4 THENINTO hbg2 VALUES (emp_no,name )WHEN emp_no <= 4 THENINTO hbg1 VALUES (emp_no,name)SELECT emp_no,nameFROM hbgWHERE emp_no > 2;
10.
Example 3: Conditional INSERT FIRSTINSERT FIRSTWHEN name ='Harsh' THENINTO hbg1 VALUES (emp_no,name) WHEN emp_no = 2 THENINTO hbg2 VALUES (emp_no,name) WHEN emp_no >2 and emp_no < 4 THENINTO hbg3 VALUES (emp_no,name) ELSEINTO hbg4 VALUES (emp_no,name)SELECT emp_no, name FROM hbg;
11.
Example 4: Conditional INSERT ALLINSERT ALLWHEN name ='Harsh' THENINTO hbg1 VALUES (emp_no,name) WHEN emp_no = 2 THENINTO hbg2 VALUES (emp_no,name) WHEN emp_no >0 and emp_no < 4 THENINTO hbg3 VALUES (emp_no,name) ELSEINTO hbg4 VALUES (emp_no,name)SELECT emp_no, name FROM hbg;
12.
Example 5: Pivoting INSERT
INSERT ALL
INTO sales VALUES (emp_id,name,region1)
INTO sales VALUES (emp_id,name,region2)
INTO sales VALUES (emp_id,name,region3)
SELECT emp_id, name, region1,region2, region3
FROM sales_main;
13.
• Specifying FIRST tells oracle to skip subsequent WHEN clauses after it finds one true for the row to be evaluated.
• We can also use an ELSE clause to tell oracle what to do if none of the WHEN clauses evaluates to true
14.
• Example 6: Using Table Aliases
INSERT ALL WHEN ottl < 100000 THEN INTO small_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 100000 and ottl < 200000 THEN INTO medium_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 200000 THEN INTO large_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 290000 THEN INTO special_orders SELECT o.order_id oid, o.customer_id cid, o.order_total ottl, o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem FROM orders o, customers c WHERE o.customer_id = c.customer_id;
15.
Uses
• Multi table inserts are useful in data warehouse scenario.
• Instead of using sqlldr to load data into multiple table , multi table inserts coupled with an external table can be used.
16.
MERGE StatementMERGE Statement
• This specialized statement combines insert and update into a single operation. It is intended for data warehousing applications that perform particular patterns of inserts and updates.
• Previous approach for achieving the same was:- Insert, if failed on primary key then update- Update, if SQL%NOTFOUND then insert.
• Avoid Multiple update statements.• Now, a single SQL statement does the job.
17.
Syntax :We can conditionally insert rows into table B from table A with help of single SQL statement.
MERGE INTO high_score_copy B Target table
USING high_score A - source (can be a table or view or subquerry)
ON ( A. name=B.name) condition to either update or insert
WHEN MATCHED THENUPDATE SET B.score=A. score
WHEN NOT MATCHED THEN INSERT VALUES (A.name,A.score);
18.
Example 1:
Merge into hbg1
using hbg
on (hbg.emp_no = hbg1.emp_no)
when matched
then update
set hbg1.name = hbg.name
when not matched
then insert (hbg1.emp_no, hbg1.name) values (hbg.emp_no, hbg.name)
19.
Example 2 :
Merge into hbg1
using hbg
on (hbg.emp_no = hbg1.emp_no)
when matched
then update
set hbg1.name = 'hbg'
when not matched
then insert (hbg1.emp_no, hbg1.name) values (hbg.emp_no, hbg.name)
20.
Example 3 :Merge into hbg1using hbgon (hbg.EMP_NO = hbg1.EMP_NO) when matched then update set hbg1.name = hbg.namewhen not matched then insert (emp_no, name) values (hbg.emp_no,
hbg.name)
21.
Example 4:
Merge into hbg1 h
using hbg g
on (h.EMP_NO = g.EMP_NO)
when matched
then update
set h.name = g.name
when not matched
then insert (emp_no, name) values (g.emp_no, g.name)
22.
Enhancements in 10g :
• We can now specify BOTH in one SQL instead of update or (and) insert separate SQL statements
• Can also delete rows from the target table during update operation instead of writing a separate SQL statements
23.
Advantages:
• Simpler to use than procedural code• Considerably faster than using procedural code• Can be used in data warehousing applications
24.
GROUP BY Clause EnhancementsGROUP BY Clause Enhancements
• ROLLUP
Used to calculate sub-totals
• CUBE
Used to get cross-tabulation results
25.
26.
27.
28.
ROLLUP• Used to calculate sub-totals.
Example :
SELECT EMP_ID, NAME, SUM(REGION)
FROM sales
GROUP BY ROLLUP(emp_id,name);
This will display for group by from right to left.
So the grouping will be done first of name and
then of empid
29.
30.
31.
• Grouping Sets : Simple extension to GROUP BY to facilitate multiple groupings
Example :
select DEPTNO,JOB,MGR,avg(sal)
from emp_old
group by grouping sets
((deptno,job,mgr), (deptno,mgr), (job,mgr));
32.
33.
GROUPING SETS• The example in the slide displays the
following:
• Total salary for every department (labeled 1)
• Total salary for every department, job ID, and manager (labeled 2)
• Grand total (labeled 3)
34.
Advantages
• All the group by enhancements make only one pass over the base table
• Code readability is improved
35.
CASE StatementCASE StatementFollowing are new CASE expression types
• Searched CASE expression• NULLIF• COALESCE
36.
37.
38.
39.
CASE• In a simple CASE expression, Oracle searches
for the first WHEN ... THEN pair for which expr is equal to comparison_expr and returns return_expr. If none of the WHEN ... THEN pairs meet this condition, and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oraclereturns null.
• You cannot specify the literal NULL for all the return_exprs and the else_expr
40.
NULLIF• Syntax
• NULLIF (expr1, expr2)
• In the syntax:
• expr1 is the source value compared to expr2
• expr2 is the source value compared with expr1. (If it is not equal to expr1, expr1
• is returned.)
41.
42.
COALESCE• Syntax• COALESCE (expr1, expr2, ... exprn)• In the syntax:• expr1 returns this expression if it is not null• expr2 returns this expression if the first expression is
null and this expression is not• null• exprn returns this expression if the preceding
expressions are null
43.
44.
JoinsJoins
• Oracle 9i provides support for SQL:1999 syntaxNew keywords like :
– CROSS JOIN– NATURAL JOIN– USING CLAUSE– ON CLAUSE
• Full outer join is possible from 9i
45.
46.
47.
48.
49.
• LEFT JOINSQL> SELECT e.ename,e.deptno,d.dname FROM emp_old e LEFT JOIN dept d ON ( e.deptno=d.deptno );
• RIGHT JOINSQL> SELECT e.ename,e.deptno,d.dname FROM emp_old e RIGHT JOIN dept d ON ( e.deptno=d.deptno );
• FULL JOINSQL> SELECT e.ename,e.deptno,d.dname
FROM emp_old e FULL JOIN dept dON ( e.deptno=d.deptno );
50.
Introduction To Analytical FunctionsIntroduction To Analytical Functions
• Analytic functions compute an aggregate value based on a group of rows.
• They differ from aggregate functions in that they return multiple rows for each group.
51.
Common Analytic FunctionsCommon Analytic Functions
• RANK• DENSE_RANK• ROW_NUMBER• FIRST_VALUE / LAST_VALUE• LEAD• LAG
52.
RANKRANK
• RANK calculates the rank of a value in a group of values.
• Rows with equal values for the ranking criteria receive the same rank.
• The PARTITION keyword is used to define where the rank resets. The specific column that is ranked is determined by the ORDER BY clause. If no partition is specified, ranking is performed over the entire result set. RANK assigns a rank of 1 to the smallest value unless descending order is used .
53.
Example 1:
Partition is optional and Order By is compulsory
SELECT emp_no, name salary,
RANK() OVER
( ORDER BY salary DESC)
AS r1
FROM test1 ;
54.
Example 2:
SELECT emp_no, name, salary,
RANK() OVER
(PARTITION BY emp_no
ORDER BY salary DESC)
AS r1
FROM test1
55.
Dense RankDense Rank• The difference between RANK and DENSE_RANK is
that DENSE_RANK leaves no gaps in ranking sequence when there are ties.
• That is, if you were ranking a competition using DENSE_RANK and had three people tie for second place, you would say that all three were in second place and that the next person came in third. The RANK function would also give three people in second place, but the next person would be in fifth place.
56.
Advantage
• Can be used to obtain top N or bottom N ranking
(1) SELECT EMP_NO, NAME, salary,
RANK() OVER( ORDER BY emp_no) AS r2
FROM test1
(2) SELECT EMP_NO, NAME, salary,
DENSE_RANK() OVER( ORDER BY emp_no) AS r2
FROM test1
57.
LEAD/LAG• LAG (value_expression [,offset] [,default]) OVER
([query_partition_clase] order_by_clause)
• LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clase] order_by_clause)
• value_expression - Can be a column or a built-in function, except for other analytic functions.
• offset - The number of rows preceding/following the current row, from which the data is to be retrieved. The default value is 1.
• default - The value returned if the offset is outside the scope of the window. The default value is NULL.
58.
Examples(1) SELECT empno, ename, sal, LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev, sal - LAG(sal, 1, 0 ) OVER (ORDER BY sal) AS
sal_diff FROM emp;(2) SELECT empno, ename, sal, LEAD(sal, 1, 0) OVER (ORDER BY sal) AS
sal_next, LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS
sal_diff FROM emp;
59.
FIRST_VALUE /LAST_VALUE
FIRST_VALUE / LAST_VALUE is an analytic function. It returns the first / last value in an ordered set of values.
If the first / last value in the set is null, then the function returns NULL unless you specify IGNORE NULLS.
If you specify IGNORE NULLS, then FIRST_VALUE / LAST_VALUE returns the first non-null value in the set, or NULL if all values are null.
60.
ExamplesLowest value
(1) SELECT empno, ename ,sal, LAST_VALUE (ename) OVER (ORDER BY sal desc ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING ) AS lvFROM (SELECT * FROM emp WHERE empno <6 );
(2) SELECT empno, ename ,sal, FIRST_VALUE (ename) OVER (ORDER BY sal asc ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING ) AS lvFROM (SELECT * FROM emp WHERE empno <6 );Partition by can be used
61.
Examples Highest value
(1) SELECT empno, ename ,sal, FIRST_VALUE(ename) OVER
(ORDER BY sal desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS hv
FROM (SELECT * FROM emp WHERE empno <6 );
(2) SELECT empno, ename ,sal, LAST_VALUE(ename) OVER
(ORDER BY sal asc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS hv
FROM (SELECT * FROM emp order by sal desc);
62.
ROWNUMBER• ROW_NUMBER is an analytic function. It assigns a
unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.
• Use of the function lets you implement top-N, bottom-N query.
63.
Examples
SELECT department_id, last_name, employee_id, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
FROM employees;
64.
MODEL ClauseMODEL Clause
• SQL Model clause, a powerful new extension to the SQL SELECT statement
• MODEL clause, is a scalable and manageable way of computing business models in the database
• With the SQL Model clause, we can view query results in the form of multidimensional arrays & then apply formulas to calculate new array values.
• The formulas can be sophisticated interdependent calculations with inter-row and inter-array references.
65.
• SYNTAX<prior clauses of SELECT statement>MODEL [main][reference models][PARTITION BY (<cols>)]DIMENSION BY (<cols>)MEASURES (<cols>)[IGNORE NAV] | [KEEP NAV][RULES[UPSERT | UPDATE][AUTOMATIC ORDER | SEQUENTIAL ORDER][ITERATE (n) [UNTIL <condition>] ]( <cell_assignment> = <expression> ... )
66.
EXAMPLE 1SELECT 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)
DIMENSION BY (prod, year)
MEASURES (sale sales)
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;
67.
68.
EXAMPLE 2 : FOR LOOPSELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15)
prod, year, salesFROM sales_viewWHERE country='Italy'MODEL RETURN UPDATED ROWSPARTITION BY (country)DIMENSION BY (prod, year)MEASURES (sale sales)RULES (sales[FOR prod IN (Prod1', ‘Prod2'),2005] = 1.3 * sales[CV(prod), 2001] )ORDER BY country, prod, year;
69.
• Partition columns define the logical blocks of the result set in a way similar to the partitions of the analytical functions
• Dimension columns define the multi-dimensional array and are used to identify cells within a partition
• Rules in the MODEL clause are applied to each partition independent of other partitions.
• Partitions serve as a boundary point for parallelizing the model computation
70.
Advantages Of MODEL Clause
• Performance:Model clause processing eliminates the need for many SQL join and union operations
• Scalability:The Model clause leverages Oracle parallelism, efficiently using all system resources made available to it.
• Manageability:Computations are centralized close to the data, so it reduces inconsistency & poor security
71.
Regular ExpressionsRegular Expressions• Regular expressions specify patterns to search for in
string data• A regular expression can specify complex patterns of
character sequences• Regular expression support in SQL/PLSQL is
implemented with functions like :– REGEXP_LIKE, – REGEXP_REPLACE– REGEXP_INSTR
72.
Example
SQL> SELECT first_name, last_name FROM employees WHERE REGEXP_LIKE (first_name,'^Ste(v|ph)en$');
Examine the string, looking for the firstsubstring bounded by commasSQL> SELECT REGEXP_SUBSTR('500 Oracle Parkway,
Redwood Shores, CA', ',[^,]+,') "REGEXPR_SUBSTR" FROM DUAL;
73.
EXAMPLES
(1) SELECT zip FROM zipcode
WHERE REGEXP_LIKE(zip, '[^[:digit:]]');
Result : ab123
123xy
(2) SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234', '[[:digit:]]{5}$') AS rx_instr FROM dual;
Result : 45
(3) SELECT REGEXP_SUBSTR('first field, second field , third field', ', [^,]*,') FROM dual;
Result : , second field ,
74.
EXAMPLES
(4) SELECT REPLACE('Joe Smith',' ', ' ') AS replace FROM dual;
Result : Joe Smith
(5) SELECT REGEXP_REPLACE('Joe Smith', '( ){2,}', ' ') AS RX_REPLACE FROM dual;
Result : Joe Smith (6) SELECT REGEXP_REPLACE('Ellen Hildi Smith',
'(.*) (.*) (.*)', '\3, \1 \2');
Result : Smith, Ellen Hildi
75.
SQL PLUS EnhancementSQL PLUS Enhancement
• We have enabled displaying database name at SQL-Prompt
• But from SQL prompt, if one connects to different database, the new database name is not displayed
• This security flaw is addressed in 10gNCSSLV> conn username/password@HISTLV
Connected. HISTLV>
76.
• Spool command is enhanced– Append or replace possible to existing spool file
• New DEFINE variables– _DATE default the _DATE variable is dynamic and
shows the current date
– _PRIVILEGE The _PRIVILEGE variable indicates the privilege level of the current connection
– _USER The _USER variable contains the user name connected
• set sqlprompt '&_user &_privilege &_date SQLPLUS> '
77.
External TablesExternal Tables
78.
What are External Tables?What are External Tables?
• These are like regular tables except that:- They are read-only tables- DML operations / Index creation not possible- Data is stored externally in flat-files
• Can be queried like a normal table• Supports parallelism• Stores only the mapping info between table
columns and flat-file contents
79.
• Create the table ascreate table emp_load
( EMPNO NUMBER(4) ,DEPTNO NUMBER(2) )ORGANIZATION EXTERNAL ( type oracle_loader default directory data_dir access parameters ( badfile ‘emp.bad’
logfile ‘emp.log’fields terminated by ',' )
location ('emp.dat')
80.
• Prior to Oracle 10g, external tables were read-only. However, in 10g, external tables can also be written to.
• Loading and unloading could be done in parallelism. (PARALLEL clause)
81.
Benefits
• Generate flat files on the target system serially or in parallel
• Helps in cases where External table driver cannot handle complex ETL( extraction transformation loading)
• In this data is internally converted from oracle internal representation to native external representation.
82.
Usage
• Used in TSAT to generate graphs based on trading-system data
• Can be used in DataWareHousing E(xtraction)T(ransformation)L(oading) process
• Can do away with TRADES Capture process in OPMS and INDEX
83.
Date-Time supportDate-Time support
84.
Introduction
Date-time data types – TIMESTAMP– TIMESTAMP WITH TIME ZONE– TIMESTAMP WITH LOCAL TIME ZONE
Interval data types– INTERVAL DAY TO SECOND– INTERVAL YEAR TO MONTH
85.
TIMESTAMP Data TypesTIMESTAMP Data Types• The TIMESTAMP datatype is an extension of
the DATE datatype.
• It also stores fractional part of seconds.
• Specify the TIMESTAMP datatype as follows:TIMESTAMP
[(fractional_seconds_precision)]where fractional_seconds_precision is
number of digits in fractional part of SECOND field.
86.
Interval Data Types
• Used to store the difference between datetime values.
• There are two classes of intervals :– Year-month intervals– Day-time intervals
• Interval date types are useful for flexible arithmetic operations .
87.
We can add various date data types to each other
Example: Interval day-to-second data type
SQL> select interval '120' day(3) + interval '5:10' hour to minute + interval '40' second
2 from dual;INTERVAL'120'DAY(3)+INTERVAL'5:10'HOURTOMINUTE+INTERVAL'40'SECOND
----------------------------------------------------
+000000120 05:10:40.000000000
88.
Usage
• Used in INDEX to find trade time lag between stratus and OLTP databases
• Possible usage in OPMS , INDEX and other OLTP applications, if sub-second data is required
• Usage also in ORDERS application
89.
PL / SQL PL / SQL EnhancementsEnhancements
90.
ASSOCIATIVE ARRAYSASSOCIATIVE ARRAYS• Associative arrays are sets of key-value pairs, where
each key is unique and is used to locate a corresponding value in the array.
• Starting with Oracle 9i , the key can be a string data type.
• Eg: • type population_type is TABLE of NUMBER
INDEX BY VARCHAR2(20);• state_population population_type;• state_population('TAMILNADU'):=20000;
91.
DECLARE TYPE country_tab IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(5); t_country country_tab;
BEGIN -- Populate lookup t_country ('UK') := 'United Kingdom'; t_country ('US') := 'United States of America';
t_country ('FR') := 'France'; t_country ('DE') := 'Germany'; -- Find country name for ISO code "DE"
DBMS_OUTPUT.PUT_LINE('ISO code "DE" = ' || t_country ('DE'));
END;
92.
• Uses And Advantages
• Like SQL table : retrieve values based on primary key– Appropriate for small lookup tables– It helps in fast lookup for an individual element
without knowing its position within the array and without having to loop through all the array elements
– Subscripts can be negative and non-sequential– PL/SQL automatically converts between host arrays
and associative arrays– Bulk-binding of host arrays i/o to associative arrays
using PL/SQL blocks
93.
PL/SQL Native CompilationPL/SQL Native Compilation
• Speeds up PL/SQL procedures by compiling them into native code residing in shared libraries.
• Procedures are translated into C code, compiled and linked into the oracle process
• Bypasses the runtime interpretation giving faster runtime performance
• Debugging tools for PL/SQL do not handle procedures compiled for native execution.
94.
Setup And ImplementationSetup And Implementation• Create a directory for storing the shared library and
set this path as PLSQL_NATIVE_LIBRARY_PATH• Alter Session set plsql_code_type=‘NATIVE ‘• Alter Session set plsql_compiler_flags = 'NATIVE';• Create or Replace or alter the Subprograms• Run any script which creates a set of Oracle Supplied
packages.• Create a DB using a preconfigured init file with
plsql_code_type = NATIVE.• SELECT object_name, param_name, param_value
FROM user_stored_settings WHERE param_name LIKE 'plsql%‘And object_name IN ('LOADER', 'DBRX_UTIL');
95.
Table FunctionsTable Functions
• Used to produce a collection of rows that can be queried like a database table or assigned to a PL/SQL collection variable.
• Input is a collection of rows in form of VARRAY, PL/SQL table or REF CURSOR.
• Are invoked from SELECT statement’s FROM clause.
• The collection of rows returned can be pipelined.
• The execution of a table function can be parallelized.
96.