partitioning in 11grms.koenig-solutions.com/sync_data/trainer/qms/508... · 2018-07-31 · •...
TRANSCRIPT
![Page 1: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/1.jpg)
1
Information Management
• Partitioning• Compression• SecureFiles• Miscellaneous
• New Development features
• Unstructured data
Partitioning in 11gComposite Partitioning
![Page 2: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/2.jpg)
2
• Concept of composite partitioning• Data is partitioned along two dimensions (A,B)�• A distinct value pair for the two dimensions uniquely
determines the target partitioning
• Composite partitioning is complementary to multi-column range partitioning
• Extensions in Oracle Database 11g ..
Extended Composite Partitioning Strategies
Geography - ProductList - List
Geography - OrderIDList - Hash
ShipDate - OrderDateRange - Range
Geography -TimeList – Range
Use CaseNew 11g Strategy
Table SALESRANGE(order_date)-RANGE(ship_date)
Composite Partitioning - Concept
ship_date
... ...
... ...
... ...
...
...
...
...
Jan 2006 Feb 2006 Jan 2007
Jan 2006
Feb 2006
May 2006
Mar 2006
order_date
![Page 3: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/3.jpg)
3
Composite Partitioning - Concept
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007
... ...
... ...
...
...
...
...
Jan 2006
Feb 2006
May 2006
Mar 2006
• All records with order_date in March 2006
ship_date
order_date
Table SALESRANGE(order_date)-RANGE(ship_date)
Composite Partitioning - Concept
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007
... ...
... ...
...
...
...
...
Jan 2006
Feb 2006
May 2006
• All records with ship_date in May 2006
May2006
ship_date
order_date
Table SALESRANGE(order_date)-RANGE(ship_date)
![Page 4: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/4.jpg)
4
Composite Partitioning - Concept
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007
... ...
... ...
...
...
...
...
Jan 2006
Feb 2006
May 2006
Mar 2006
• All records with order_date in March 2006ANDship_date in May 2006
May2006
ship_date
order_date
Table SALESRANGE(order_date)-RANGE(ship_date)
Partitioning in 11gVirtual Column Based Partitioning
![Page 5: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/5.jpg)
5
Virtual Columns
Business Problem• Extended Schema attributes are fully derived and dependent on
existing common data• Redundant storage or extended view definitions are solving this
problem today
• requires additional maintenance and creates overhead
Solution• Oracle Database 11g introduces virtual columns
• purely virtual, meta-data only• Treated as real columns except no DML
• can have statistics• eligible as partitioning key
• Enhanced performance and manageability
Virtual Columns - Example
• Base table with all attributes ...
CREATE TABLE accounts
(acc_no number(10) not null,
acc_name varchar2(50) not null, ...
12500 Adams12507 Blake1266612875 Smith
King
![Page 6: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/6.jpg)
6
Virtual Columns - Example
12500 Adams12507 12Blake12666 1212875 12Smith
King
CREATE TABLE accounts
(acc_no number(10) not null,
acc_name varchar2(50) not null, ...
acc_branch number(2) generated always as (to_number(substr(to_char(acc_no),1,2)))
12
• Base table with all attributes ...• ... is extended with the virtual (derived) column
Virtual Columns - Example
12500 Adams12507 12Blake12666 1212875 12Smith
King
CREATE TABLE accounts (acc_no number(10) not null,acc_name varchar2(50) not null, ...acc_branch number(2) generated always as
(to_number(substr(to_char(acc_no),1,2)))partition by list (acc_branch) ...
12
• Base table with all attributes ...• ... is extended with the virtual (derived) column• ... and the virtual column is used as partitioning key
32320 Jones32407 32Clark32758 3232980 32Phillips
32
... Hurd
![Page 7: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/7.jpg)
7
Partitioning in 11gInterval Partitioning
Interval Partitioning
• Partitioning is key-enabling functionality for managing large volumes of data• one logical object for application transparency• multiple physical segments for administration
but• Physical segmentation requires additional data
management overhead• new partitions must be created on-time for new data
MarJan
Feb
CDRs
SQLSQL
Application
Automate the partition management
![Page 8: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/8.jpg)
8
Interval Partitioning
• Interval Partitioning• extension to range partitioning
• full automation for equi-sized range partitions
• Partitions are created as metadata information only• start partition is made persistent
• Segments are allocated as soon as new data arrives• no need to create new partitions
• local indexes are created and maintained as well
No need for any partition management
Interval PartitioningHow it works
CREATE TABLE sales (order_date DATE, ...)PARTITON BY RANGE (order_date)INTERVAL(NUMTOYMINTERVAL(1,’month’)(PARTITION p_first VALUES LESS THAN (’01-FEB-2006’);
Table SALES
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007 Oct 2009 Nov 2009
...
First segment is created
![Page 9: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/9.jpg)
9
Table SALES
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007 Oct 2009 Nov 2009
...
Other partitions only exist in metadata
CREATE TABLE sales (order_date DATE, ...)
PARTITON BY RANGE (order_date)
INTERVAL(NUMTOYMINTERVAL(1,’month’)
(PARTITION p_first VALUES LESS THAN (’01-FEB-2006’);
Interval PartitioningHow it works
Table SALES
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007 Oct 2009 Nov 2009
...
INSERT INTO sales (order_date DATE, ...)
VALUES (’04-MAR-2006’,...);
New segment is automatically allocated
CREATE TABLE sales (order_date DATE, ...)
PARTITON BY RANGE (order_date)
INTERVAL(NUMTOYMINTERVAL(1,’month’)
(PARTITION p_first VALUES LESS THAN (’01-FEB-2006’);
Interval PartitioningHow it works
![Page 10: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/10.jpg)
10
Table SALES
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007 Oct 2009 Nov 2009
...
INSERT INTO sales (order_date DATE, ...)
VALUES (’17-OCT-2009’,...);
... whenever data for a new partition arrives
CREATE TABLE sales (order_date DATE, ...)
PARTITON BY RANGE (order_date)
INTERVAL(NUMTOYMINTERVAL(1,’month’)
(PARTITION p_first VALUES LESS THAN (’01-FEB-2006’);
Interval PartitioningHow it works
Table SALES
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007 Oct 2009 Nov 2009
...
• Interval partitioned table can have classical range and automated interval section• Automated new partition management plus full partition
maintenance capabilities: “Best of both worlds”
Interval PartitioningHow it works
![Page 11: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/11.jpg)
11
Table SALES
... ...
2006 Jan 2007 Oct 2009 Nov 2009
...
• Interval partitioned table can have classical range and automated interval section• Automated new partition management plus full partition
maintenance capabilities: “Best of both worlds”
MERGE and move old partitions for ILM
Range partition section
Interval PartitioningHow it works
Table SALES
... ...
Jan 2007 Oct 2009 Nov 2009
...
• Interval partitioned table can have classical range and automated interval section• Automated new partition management plus full partition
maintenance capabilities: “Best of both worlds”
MERGE and move old partitions for ILM
Range partition section Interval partition section
Insert new data- Automatic segment creation
Interval PartitioningHow it works
2006
INSERT INTO sales (order_date DATE, ...)
VALUES (’13-NOV-2009’,...);
![Page 12: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/12.jpg)
12
Partitioning in 11gREF Partitioning
REF Partitioning
Business Problem• Related tables benefit from same partitioning strategy
• e.g. order – lineitem
• Redundant storage of the same information solves this problem• data overhead• maintenance overhead
Solution• Oracle Database 11g introduces REF Partitioning
• child table inherits the partitioning strategy of parent table through PK-FK relationship
• intuitive modelling
• Enhanced Performance and Manageability
![Page 13: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/13.jpg)
13
Before REF Partitioning
Table ORDERS
Jan 2006
... ...
Feb 2006
Table LINEITEMS
Jan 2006
... ...
Feb 2006
• Redundant storage of order_date• Redundant maintenance
• RANGE(order_date)�• Primary key order_id
• RANGE(order_date)�• Foreign key order_id
REF Partitioning
Table ORDERS
Jan 2006
... ...
Feb 2006
Table LINEITEMS
Jan 2006
... ...
Feb 2006
• RANGE(order_date)�• Primary key order_id
• RANGE(order_date)�• Foreign key order_id
PARTITION BY REFERENCE• Partitioning key inherited
through PK-FK relationship
![Page 14: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/14.jpg)
14
Well-tunedSQL & Schema
Partitioning Advisor
• Considers entire query workload to improve query performance
• Advises on partitioning methods • Range (equal-interval), range
key and interval
• Hash, hash key
• Integrated, non-conflicting advice with Indexes, MVs
SQL Workload
PackagedApps
Custom Apps
SQL Advisor
SQL PlanTuning
SQLStructureAnalysis
AccessAnalysis
SQLProfile
SQLAdvice
Indexes& MVs
PartitionAnalysis
Partition Advice
New!
• Proven functionality in 7th generation• experience comes with age and customer usage
• Fundamental concepts for success• the most comprehensive and complete offering
• 8th generation introduces ground-breaking new functionality• extended COMPOSITE strategies further flexibility and performance• INTERVAL for simplicity, ease of management• VIRTUAL COLUMNS extend applicability with low maintenance• REF enhanced performance for related tables
Oracle PartitioningSummary
![Page 15: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/15.jpg)
15
Advanced Compression OptionStore more with less space
Challenges
• Explosion in data volume managed by Enterprises• Government regulations (Sarbanes-Oxley, HIPPA, etc)• User generated content (Web 2.0)
• IT managers must support larger volumes of data with limited technology budgets • Need to optimize storage consumption
• Also maintain acceptable application performance
• Intelligent and efficient compression technology can help address these challenges
![Page 16: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/16.jpg)
16
Introducing Advanced Compression Option
• Oracle Database 11g introduces a comprehensive set of compression capabilities • Structured/Relational data compression• Unstructured data compression• Compression for backup data• Network transport compression
• Reduces resource requirements and costs • Storage System• Network Bandwidth• Memory Usage
Redo logs BackupsStandby
Table Compression
• Introduced in Oracle9i Release 2• Supports compression during bulk load operations (Direct Load, CTAS)
• Data modified using conventional DML not compressed
• Optimized compression algorithm for relational data
• Improved performance for queries accessing large amounts of data• Fewer IOs• Buffer Cache efficiency
• Data is compressed at the database block level• Each block contains own compression metadata – improves IO efficiency• Local symbol table dynamically adapts to data changes
• Compression can be specified at either the table or partition levels
• Completely transparent to applications
• Noticeable impact on write performance
![Page 17: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/17.jpg)
17
OLTP Table Compression
• Oracle Database 11g extends compression for OLTP data• Support for conventional DML Operations
(INSERT, UPDATE, DELETE)
• New algorithm significantly reduces write overhead• Batched compression ensures no impact for most OLTP
transactions
• No impact on reads• Reads may actually see improved performance due to fewer
IOs and enhanced memory efficiency
OLTP Table Compression
Overhead
Free Space
Uncompressed
Compressed
I nserts are uncom pressed
Block usage reaches PCTFREE – t riggers Com pression
I nserts are again uncom pressed
Block usage reaches PCTFREE – t riggers Com pression
• Adaptable, continuous compression• Compression automatically triggered when block usage
reaches PCTFREE• Compression eliminates holes created due to deletions
and maximizes contiguous free space in block
![Page 18: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/18.jpg)
18
Using OLTP Table Compression
• Requires database compatibility level at 11.1 or greater• New Syntax extends the ‘COMPRESS’ keyword
• COMPRESS [FOR {ALL | DIRECT_LOAD} OPERATIONS]• DIRECT_LOAD (DEFAULT)
• Refers to Bulk load operations from 10g and prior releases• ALL
• OLTP + Direct loads
• Enable compression for a new table
• Enable only direct load compression on existing table
• only new rows are compressed, existing rows are uncompressedALTER TABLE t2 COMPRESS
CREATE TABLE t1 COMPRESS FOR ALL OPERATIONS
Data Pump Compression
• Metadata compression available since Oracle Database 10g
• Oracle Database 11g extends compression to table data during exports• no need to decompress before import
• Single step compression of both data and metadata• compressed data directly hits disk resulting in reduced disk space
requirements
• 75% reduction in dump file size on export of sample OE and SH schemas
• Compression factor comparable to GNU gzip utility• Application transparent
• complete Data Pump functionality available on compressed files
![Page 19: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/19.jpg)
19
Backup data and Network transport Compression
• Fast RMAN Compression• compresses the backup set contents before writing them to disk or
tape
• no extra decompression steps are required during recovery when you use RMAN compression.
• high performance, industry standard compression algorithm
• 40% faster backup compression versus Oracle Database 10g• suitable for fast, incremental daily backups• reduces network usage
• Data Guard Network Compression• compression of redo traffic over the network
• improves redo transport performance• gap resolution is up to 2x faster
Summary
• Advanced Compression Option contains comprehensive data compression capabilities for all types of data• Structured, Unstructured, Backup, Network Transport
• Reduces storage consumption by 2 to 3 times• Improves read performance• Enhances memory, buffer cache utilization• Complete application transparency• Benefits diverse application workloads
![Page 20: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/20.jpg)
20
Oracle SecureFilesLOB storage like you have never seen before
Managing Enterprise Information
• Organizations need to efficiently and securely manage
StructuredSemi-
Structured Unstructured
XML PDF
• Simplicity and performance of file systems makes it attractive to store file data in file systems, while keeping relational data in DB
• Enterprise applications manipulate both files and relational data• e.g. Document Management, Media, Medical, CAD, Imaging
![Page 21: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/21.jpg)
21
Files belong with Relational Data
• Two data managers for one application is one too many• The application must patch over the gap
• This split compromises security, robustness, and management
Structured Semi-Structured
Unstructured
XML PDF
Oracle SecureFilesConsolidated Secure Management of Data
• SecureFiles is a new 11g feature designed to break the performance barrier keeping file data out of databases
• Next-generation LOBs - faster, and with more capabilities
• transparent deduplication, compression and encryption• leverage the security, reliability, and scalability of database
• superset of LOB interfaces allows easy migration from LOBs
• Enables consolidation of file data with associated relational data• single security model
• single view of data
• single management of data
• scalable to any level using SMP scale-up, or grid scale-out
![Page 22: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/22.jpg)
22
Designed from Scratch
• SecureFiles is a major rearchitecture of how the database handles unstructured (file) data• not an incremental improvement to LOBs
• Entirely new:• disk format
• network protocol• versioning and sharing mechanisms
• caching and locking
• redo and undo algorithms
• space and memory management
• cluster consistency algorithms
SecureFile Innovations
• Write Gather Cache• cache above the storage layer buffers data up to 4MB during writes
before flushing to disk• allows for large contiguous space allocation for LOB data and
reduced write latency.
• Intelligent Pre-fetching• improves read performance by pre-fetching LOB data from disk• overlaps disk IO with network latency to improve throughput
• New Space Management routine• automates new space allocation and “freed” space reclamation• optimized chunk size reduces fragmentation
• No more High Water Mark contention as with old LOBs• deletion and Reuse of entire LOBs not just individual chunks.
![Page 23: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/23.jpg)
23
Advanced Features - Compression
• Huge storage savings• industry standard compression algorithms• 2-3x compression for typical files (doc, pdf, xml)• minimal CPU overhead during compression
• Automatically detects if SecureFile data is compressible• skips compression for already compressed data and when space savings are
minimal or zero
• Two levels of compression provide different compression ratios• compression Levels: MEDIUM (default), HIGH• higher the degree of compression, higher the latency and CPU overhead
incurred
• SecureFiles Compression is independent of table or index compression
• Server-side compression• allows for random reads and writes to SecureFile data• can be specified at a partition level
• Part of the Advanced Compression Option
Advanced Features - Deduplication
• Enables storage of a single physical image for duplicate data• Significantly reduces space consumption
• Dramatically improves writes and copy operations
• No adverse impact on read operations• may actually improve read performance for cache data
• Duplicate detection happens within a table, partition or sub-partition
• Specially useful for content management, email applications and data archival applications
• Part of the Advanced Compression Option
Secure hash
![Page 24: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/24.jpg)
24
Advanced Features - Encryption
• Extends Transparent Data Encryption (TDE) syntax to SecureFile data• old LOB or BasicFiles data can not be encrypted
• Performed at Block level
• Support for industry-standard encryption algorithms• 3DES168
• AES128• AES192 (default)• AES256
• Encrypt on a per-column basis
• Part of the Advanced Security Option
SecureFile Interfaces
• SecureFiles can be accessed by both database clients and file system clients
• Database clients use extended LOB interfaces• JDBC, ODBC, OCI, .NET, PL/SQL• 11g has a highly optimized streaming protocol for SecureFiles
• File system clients use the file system protocols implemented inthe XML DB repository• FTP access• WebDav Access• http Access
![Page 25: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/25.jpg)
25
Integration with Other Products and Features
• Securefiles is fully integrated with• XML DB (Binary XML)
• Oracle InterMedia• Oracle Spatial• Content DB
• Out of box benefits for new installations• by setting
• Migration path being worked out for existing installations
• Efforts underway to integrate with Stellant
db_securefiles = FORCE or ALWAYS
Using SecureFiles
• Old LOBs are still supported and are referred to as ‘BASICFILE’• Default LOB storage type in Oracle Database 11g
• New init.ora parameter ‘db_securefile’ to manage LOB storage policy• PERMITTED – allow SecureFiles to be created• NEVER – disallow new SecureFile• FORCE – create all LOBs as SecureFiles• ALWAYS – attempt to create SecureFiles, but fall back to BasicFiles• IGNORE – ignore attempts to create SecureFiles
• Requires• locally managed tablespaces with ASSM is required to use
SecureFiles• compatibility set to 11.1 or higher
![Page 26: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/26.jpg)
26
Migration to SecureFiles
• Requires table rebuild• can be done at the partition level
• Online Redefinition is the preferred migration technique for SecureFiles
• no need to take the table or partition offline.• additional storage equal to the entire table and all LOB segments
must be available.
• global indexes need to be rebuilt.• recommend setting NOLOGGING storage attribute for destination
SecureFile columns during migration to avoid performance problems with redo generation
• if the destination table is partition, online redefinition can be done in parallel
Multimedia in Oracle Database
• Why put images in the database?• multimedia content subject to database enforced referential integrity
and transaction control
• metadata extraction, editing and indexing.• media-specific features such as metadata editing, thumb nailing and
format conversion
• simplifies secure delivery of content to streaming media servers
• New in Oracle Database 11g• image size limits increased, up to 128TB• new fastfiles datatype for increased performance, compression, and
deduplication
• improved DICOM support
![Page 27: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/27.jpg)
27
DICOM Medical Imaging Support
• Used by virtually all medical devices (CT, MRI, PET, …)
• Database supports the data management needs of clinical medicine and life sciences• multi-terabyte image archives with security,
auditing and standards conformance• image quality control – ensure well-formed
images• Built-in support for metadata extraction
and searching • Support for privacy regulations (HIPAA),
annotation and format conversion
PL/SQL Enhancements in 11g
![Page 28: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/28.jpg)
28
Enhanced SQL –PIVOT and UNPIVOT
100Q3Jeans
1000Q4Jeans
500Q3Jeans
1000Q1Jeans
1000Q2Shoes
2000Q1Shoes
AMOUNTQUARTERPROD
1000600Null1000Jeans
NullNull10002000Shoes
Q4Q3Q2Q1PROD
Enhanced performance of ETL in your warehouse
SELECT * FROM salesPIVOT
(sum(amount)FOR quarter in
‘Q1’,’Q2’,’Q3’,’Q4’);
PL/SQL Enhancements in 11gFine Grained Dependency Tracking
![Page 29: Partitioning in 11grms.koenig-solutions.com/Sync_data/Trainer/QMS/508... · 2018-07-31 · • Related tables benefit from same partitioning strategy • e.g. order – lineitem •](https://reader033.vdocuments.us/reader033/viewer/2022060323/5f0dd2637e708231d43c417b/html5/thumbnails/29.jpg)
29
The challenge
• View v ends up invalid in 10.2 because we know only that its dependency parent has changed – at the granularity of the whole object
create table t(a number)/create view v as select a from t/alter table t add(Unheard_Of number)/select status from User_Objectswhere Object_Name = ’V’
/
Fine Grained Dependency Tracking
• In 11.1 we track dependencies at the level of element within unit
• so we know that these changes have no consequence
create table t(a number)/create view v as select a from t/alter table t add(Unheard_Of number)/select status from User_Objectswhere Object_Name = ’V’
/