Using Additional New Features

1. Using Additional New Features

2. Objectives • After completing this lesson, you should be able to describe enhancements to the following: • SQL commands • Oracle objects • Data Guard • Real Application Clusters (RAC)

3. Online Index Rebuild • In Oracle9i, online index rebuild was extended to the following: • Reverse key indexes • Function-based indexes • Key compressed indexes on regular tables • Key compressed indexes on index-organized tables (including secondary indexes)

4. Index-Organized Tables: High-Availability Enhancements • Oracle9i extends functionality to index-organized tables (IOTs): • Online create and rebuild of IOT secondary indexes • Online move of IOTs along with their overflow segment • Online coalesce of IOT primary indexes • Online update of logical ROWIDS for secondary indexes on IOTs

5. Global Index Maintenance During Partition DDLs • Prior to Oracle9i, when a partition DDL statement was issued against a partitioned table, all indexes corresponding to the data partition affected by the DDL were marked UNUSABLE. • Oracle9i provides the capability to automatically update a global index during a DDL via an optional clause. • Global indexes stay USABLE all the time. • Global indexes are easier to use now because they do not require rebuilding.

6. Fast Partition Split Optimization • Consequences of splitting a partition: • Creation of two new partitions • Redistribution of rows • Requires rebuilding corresponding local index partitions and global indexes • The above is not necessary if all rows stay in one of the two generated partitions: • Mostly the case when splitting catch-all partitions • In this case, split is just like an ADDPARTITION. • Automatically detected: • New partition storage attributes remain identical. • The creation of a local prefixed index enhances performance.

7. List Partitioning: Overview and Benefits The new partition method, introduced in Oracle9i, provides the following benefits: • User controls how rows map to partitions. • The LIST partition method allows for the distribution of data, based on discrete column values. • Unordered and unrelated sets of data can be grouped and organized together very naturally, using LIST partitioning. • No relationship between partitions • Ideal for columns that consist of discrete values • Powerful data management capabilities

8. Enhanced Parallel DML • Parallel direct-load INSERT into one single partition is available since Oracle8i. • Parallel direct-load INSERT into multiple partitions with intrapartition parallelism is available since Oracle9i Database Release 1. • Parallel INSERT, UPDATE, DELETE, and MERGE are available in Oracle9i Database Release 2 for: • Nonpartitioned tables • Single partition • Not supported for nonpartitioned tables with bitmap indexes • Limited to one process per partition in the presence of local bitmap indexes

9. Renaming Columns and Constraints ALTER TABLE [schema.]table_nameRENAME COLUMNold_column_nameTO new_column_name ALTER TABLE [schema.]table_nameRENAME CONSTRAINT old_constraint_nameTO new_constraint_name

10. Constraint Enhancements • Data dictionary views show corresponding indexes for unique and primary key constraints. • Explicit index control • Less foreign key locking overhead • Cached primary key lookup • Primary keys and foreign keys on views

11. Index Scans and Function-Based Indexes • B*-tree indexes do not contain NULL entries;therefore, index scans are sometimes impossible. • All built-in operators know whether their result is guaranteed to be NOTNULL when their inputs are NOTNULL. • This knowledge allows the CBO to use index-only scans on function-based indexes.

12. Multitable INSERT Statement • Allows you to insert rows into multiple tables as part of a single DML statement • Two types: conditional and unconditional • Can be used to transfer data from one or more source tables to a set of target tables • Can be used to refresh materialized views • Performance improvement: no materialization and repeated scan costs on the source tables

13. LONG to LOB Migration • ALTER TABLE has been enhanced: • Modify LONG column to CLOB • Modify LONGRAW column to BLOB • During conversion, the space required for both LONG and LOB data must be available. • Most SQL functions and operators that accept VARCHAR2 also accept CLOB. • SQL functions that accept RAW also accept BLOB. • LOB columns in partitioned index-organized tables and in function-based indexes

14. MERGE Command Enhancements • New conditional and extension clauses to the standard MERGE statement: • Allow conditional updates and inserts • Use the DELETE clause

15. Integrating Interrow Calculations in SQL • Enhances SQL by directly providing spreadsheet-like array computations • Offers analytical capabilities • Uses the MODEL clause to integrate interrow functionality into the Oracle database: • Classifies columns of a table into three groups • Treats measures as cells in ann-dimensional array

16. Remotetables Fast refresh Self-joins Views that can be flattened Materialized Join View (MJV) Enhancements • Oracle Database 10g supports MJV fast refresh for the following cases: • Multiple instances of a table in the FROM clause • Inline or named views in the FROM clause • Remote tables in the FROM clause • Some considerations apply

17. Tuning Manually Created MVs • Fix MV log problems • Redefine MVs to enable fast refresh and general query rewrite • Decompose MVs into fast refreshable sub-MVs DBMS_ADVISOR.TUNE_MVIEW

18. Partition Change Tracking (PCT) • Oracle Database 10g supports PCT refresh when using: • List partitioning • ROWID columns as partition markers • Join-dependent expressions • TRUNCATEPARTITIONinstead ofDELETE

19. Partitioned IOT Enhancements Global indexmaintenance Local bitmapIndexes List-partitioned IOTs LOBs

20. Skipping Unusable Indexes • SKIP_UNUSABLE_INDEXES is now also a dynamic initialization parameter. • Its default value is TRUE at both the system and session levels to avoid ORA-1502 errors. • You should monitor DBA_IND_PARTITIONS or the new entries in the alert.log file. Wed Oct 29 02:06:40 2003 Some indexes or index [sub]partitions of table JFV.SALES_TEST have been marked unusable

21. Hash-Partitioned Global Indexes: Overview Range-partitioned global index … … 900, 901, 1000, 1001, < 1000 < MAXVALUE Hash-partitioned global index … … 900, 1000, 901, 1001,

22. Bitmap Index Storage Enhancements SetCOMPATIBLE to 10.0.0.0 For each existing bitmap index Create new bitmap indexes Severe slowdown before raisingCOMPATIBLE? Slowdown after raisingCOMPATIBLE? Yes Rebuild Yes

23. MAXTRANS and Maximum Concurrency physical_attributes_clause::= PCTFREE integer PCTUSED integer INITRANS integer storage_clause

24. Large Object (LOB) Data Type Changes • Support for terabyte-sized LOBs • BLOB, CLOB, NCLOB • New DBMS_LOB.GET_STORAGE_LIMIT function • Returns actual LOB size limit

25. Implicit Conversion Between CLOB and NCLOB • Transparent implicit conversion is supported in: • SQL IN and OUT bind variables for query and DML • PL/SQL functions and procedure parameter passing • PL/SQL variable assignment

26. Regular Expression Support • A powerful method of describing both simple and complex patterns for searching and manipulating • Multilingual regular expression support for SQL and PL/SQL string types • Several new functions, all prefixed with REGEXP_ • REGEXP_LIKE • REGEXP_REPLACE • REGEXP_INSTR • REGEXP_SUBSTR

27. Matching Mechanism Regular Expression: 'a(b|c)d' String to Match: 'aabcd'

28. Syntax: Example REGEXP_LIKE(srcstr, pattern [,match_option]) • srcstr: Search value • pattern: Regular expression • match_option: Option to change default matching. Can include one or more of the following values: • ‘c’ Case-sensitive matching (default) • ‘i’ Case-insensitive matching • ‘n’ Allows match-any-character operator • ‘m’ Treats source string as multiple line

29. Using REGEXP_LIKE in SQL SELECT first_name, last_name, salary, TO_CHAR(hire_date, 'yyyy') FROM employees WHERE REGEXP_LIKE( TO_CHAR(hire_date, 'yyyy'), '^199[6-9]$'); FIRST_NAME LAST_NAME SALARY TO_C --------------- --------------- ---------- ---- David Austin 4800 1997 Valli Pataballa 4800 1998 Diana Lorentz 4200 1999 John Chen 8200 1997 Ismael Sciarra 7700 1997 Jose Manuel Urman 7800 1998 Luis Popp 6900 1999 ... 79 rows selected.

30. Case-Insensitive and Accent-Insensitive Query and Sort • Two new sorting options are available in Oracle Database 10g: • Case-insensitive sort • Accent-insensitive sort

31. Changes in Configuration Parameters • Use the NLS_SORT parameter to specify the linguistic name: • Examples: • Use the NLS_COMP parameter to specify the sort action for WHERE clauses and PL/SQL blocks: NLS_SORT = <NLS_sort_name>[_AI | _CI] NLS_SORT = FRENCH_M_AI NLS_SORT = XGERMAN_CI NLS_COMP = BINARY | ANSI

32. Support in SQL and Functions • SQL clauses supporting NLS_SORT and NLS_COMP settings: • WHERE • ORDERBY • STARTWITH • HAVING • IN/NOTIN • BETWEEN • CASE-WHEN • The NLSSORT() function is extended to support case-insensitive and accent-insensitive functionality.

33. Quote Operator q • Eliminates the need to use escape characters for single quotation marks in text literals • Supports CHAR and NCHAR literals • Valid values are: • Any single or multibyte character • Paired [ ], { }, ( ), and < > SELECT cust_address FROM customersWHERE cust_last_name = q'X 'John's Bait Shop' X'; SQL . . . v_string1 := q'['So,' she said, 'It's finished.']';v_string2 := q'!name LIKE '%DBMS_%'!'; PL/SQL

34. Using Data Guard Standby site Production site Redo information transport Oracle net Database Database copy

35. Benefits of Using Data Guard • Provides high availability and disaster protection through a distributed computing configuration • Offers different levels of data protection modes to balance data availability against performance requirements • Centralizes and simplifies management using Data Guard broker • Provides failover and switchover capabilities

36. Benefits of Using Data Guard • Safeguards against physical corruptions • Leverages against critical user errors or logical errors • Provides configurable log transport services and log apply services

37. Benefits of Using Data Guardnotes only

38. Node 1 Instance Introduction to Real Application Clusters (RAC) Activesessions Node 2 Primary Secondary Instance Shared database files

39. Sample Oracle10g RAC Database Activesessions MAIL MAIL DW OE OE InstanceLGS1 InstanceLGS2 InstanceLGS3 LGS database

40. Full Notes Page

41. Summary • In this lesson, you should have learned how describe enhancements to: • SQL and database objects • Data Guard • Real Application Clusters (RAC)