Optimal Usage of Oracle

1

• Optimal Usage of Oracles Partitioning Option

Frank Bommarito Paper 35697 SageLogix, Inc.
2
Overview

• Partitioning The Beginning

• Partitioning Concepts

• Partition Maintenance

• Types of Partitioning

• Practical Partitioning Usages

• Statistics

• Partitioning Options

• Conclusions

3
Partitioning The Beginning

• The concept one large table divided into many
  small tables
• Database sizes growing at phenomenal pace
• interesting challenges appeared for DBAs
• rebuilding indexes
• Duplication of tables
• Query tuning
• Partitioning allows for growth, with maintaining
  the DBA's management of the database
• The result more uptime

4
Partitioning The Beginning

• Oracle-based partitioning available in 8.0
  above
• Smaller tables (partitions) can be accessed,
  maintained separately
• Overview mechanism
• masks the division
• operations performed with no modifications to
  application
• Oracles optimizer
• partition aware
• partition pruning

5
Partitioning Concepts

• Two distinct object types for a partitioned table
  GLOBAL LOCAL
• GLOBAL objects refer to table as a whole
• LOCAL objects are the individual partitions
• Standard partitioned tables have same features
  (indexes, constraints, triggers), but
  implementation is different.
• Tables rows stored in multiple objects, not just
  one.

6
Partitioning Concepts

• Example
• Create table range_partition (
• Part_key number,
• Value1 varchar2(30),
• Value2 number)
• Partition by range (part_key)
• (
• partition p1 values less than (80000),
• partition p2 values less than (160000),
• partition pmax values less than (maxvalue)
• )
• Single table created, with three physical
  segments.
• Indexes need to account for each segment.

7
Partitioning Concepts

• Statement creates NON-PREFIXED LOCAL index.
• NON-PREFIXED partition key not leading column
• LOCAL 3 separate indexes created
• Partition key derived from Partition by range
  (part_key)
• Other examples
• Create index idx_example1 on range_partition
  (value2) LOCAL
• Column part_key partition key.
• Create index idx_example2 on range_partition
  (value1) GLOBAL
• Creates one single GLOBAL index - includes rows
  from all partitions.
• Create index idx_example3 on range_partition
  (part_key) LOCAL
• Creates a PREFIXED LOCAL index.

8
Performance Considerations

• Example
• Table range_partition loaded with 256,000 rows.
• All partitions a near equal distribution of rows.
  
• All three columns unique values.
• Three indexes above created on the table.
• 20,000 queries generated executed for each
  column indexed.

9
Performance Considerations

• Results
• Same table, without partitioning

Slowest (? index scans)
COLUMN INDEX TYPE TOTAL TIME
Value_1 GLOBAL 7 minutes 20 seconds
Value_2 NON-PREFIXED LOCAL 12 minutes 30 seconds
Part_key PREFIXED LOCAL 6 minutes 10 seconds
Similar
Fastest (partition pruning)
COLUMN INDEX TYPE TOTAL TIME
Value_1 STANDARD 7 minutes 20 seconds
Value_2 STANDARD 7 minutes 20 seconds
Part_key STANDARD 7 minutes 20 seconds
10
Partition Maintenance

• Why utilize a non-prefixed local index?
• Partitions splits table into smaller sizes -
  allows maintenance on excessively large tables.
• A partition can have maintenance performed
  without affecting other partitions.
• However, this does impact GLOBAL table items.
• Some of the maintenance operations include
• Rebuild a specific partitions data segments
• Exchange a non-partitioned table with a partition
• Merge two partitions together
• Divide two partitions apart
• Add new partitions to the table
• Drop old partitions from the table

11
Partition Maintenance

• Global Indexes
• A single ROW affected by a partition maintenance
  operation will cause ENTIRE global index to be
  invalid.
• 9i of Oracle contains update global index
  clause - can be applied to partition maintenance
  operations.
• Rebuilds only components of GLOBAL index
  impacted.
• Rebuilding can cause performance degradations -
  but are minimal compared to impact of
  INVALIDATING important indexes. 

12
Partition Maintenance

• Examples
• Alter table range_partition move partition p1
  tablespace new_tablespace
• Will rebuild partition locate newly rebuilt
  partition in new_tablespace. If partition p1
  one or more rows, global indexes become
  UNUSABLE. Application will receive errors if
  application needs to access index.
• Alter index idx_example2 rebuild
• Will rebuild unusable index. Needs to re-index
  entire tables contents, will not be working only
  on deltas.
• Alter table range_partition move partition p1
  tablespace new_tablespace update global indexes
• Performs same operation, but adds task of
  fixing global index (release 9i and above).

13
Partition Maintenance

• Constraints
• Likely largest prohibitive unit for partition
  maintenance.
• Most partition maintenance operations do not work
  when constraints are enabled.
• Typically, constraint needs to be dropped and
  re-applied after partition maintenance
  operations.
• Oracle added new syntax that is handy when
  disabling constraints.
• Example Alter constraint pk_contstraint disable
  keep index 
• Keep index clause will not drop the index.
  Maintenance operations proceed and index pieces
  can be rebuilt. Once complete, constraint can be
  re-enabled in relatively short time.

14
Partition Maintenance

• Example
• CREATE TABLE part_test
• (ID NUMBER NOT NULL, NUMB NUMBER)
• PARTITION BY RANGE (ID)
• (PARTITION P1 VALUES LESS THAN (10), PARTITION P2
  VALUES LESS THAN (20))
• CREATE unique INDEX part_test_pkx ON part_test
  (ID) LOCAL
• ALTER TABLE part_test ADD CONSTRAINT part_test_pk
  PRIMARY KEY (ID) USING INDEX
• create table fk_table (id number, descr
  varchar2(30))
•  

15
Partition Maintenance

• Example, cont.
• ALTER TABLE fk_table ADD CONSTRAINT fk_table_fk
  FOREIGN KEY (ID) REFERENCES PART_TEST(ID)
•  
• create table part_exch (ID NUMBER NOT NULL, NUMB
  NUMBER)
• insert into part_test values (1,1)
•  
• alter table part_test exchange partition p1 with
  table part_exch
•  
• ERROR at line 1
• ORA-02266 unique/primary keys in table
  referenced by enabled foreign keys

16
Partition Maintenance

• Stored PL/SQL
• Stored PL/SQL often exists.
• When objects are modified, PL/SQL program units
  need re-compilation to ensure that modifications
  are valid. Partition maintenance operations are
  excluded. A new partition does not impact stored
  PL/SQL. However, addition of new partition will
  invalidate any dependent PL/SQL program. Release
  9i automatically recompiles the invalidated
  programs.
• Whenever data dictionary adds or removes row
  resulting from partition maintenance operation,
  PL/SQL become invalid.

17
Partition Maintenance

• Stored PL/SQL, cont.
• The following command does not cause invalidation
  as the data dictionary is simply updated
• Alter table range_partition exchange partition p1
  with table no_partition
• The following command does cause invalidation as
  the data dictionary is removing a row
• Alter table range_partition drop partition p1

18
Types of Partitioning

• RANGE

• HASH

• LIST

• SUB-PARTITION (HASH or LIST)

19
Types of Partitioning

• RANGE PARTITIONS
• Most common.
• Table index partitions based on list of
  columns, allowing database to store occurrence in
  given partition. Typically used within data
  warehousing systems. Most common range boundary
  dates.
• Each partition is defined with upper boundary.
  Storage location found by comparing partitioning
  key with upper boundary. Upper boundary is
  non-inclusive the key of each occurrence must be
  less than this limit for record to be stored in
  this partition.

20
Types of Partitioning

• HASH PARTITIONS
• Ideal when there is no real method to divide
  table based on range.
• Utilize hashing algorithm to programmatically
  take column value and store within given
  partition.
• Each partition is defined with an internal
  hashing algorithm.
• Recommended when it is difficult to define
  criteria for distribution of data.

21
Types of Partitioning

• LIST PARTITIONS
• Hard-coded LIST of values that will exist within
  any partition.
• Common usage would be states. A state partition
  table would commonly have 50 partitions, one for
  each state.
•  SUB-PARTITIONS
• Utilized most often when partition strategy does
  not provide small enough partition units to
  achieve maintenance goals. Sub-partitions further
  divide table based another column.

22
Types of Partitioning

• Example RANGE - (a max partition will capture
  any values beyond the stated ranges including
  NULLS)
• Create table range_partition
• ( date_col date)
• partition by RANGE (date_col)
• (
• partition p_jan_2001 values less than
  (to_date(01022001,ddmmyyyy)),
• partition p_feb_2001 values less than
  (to_date(01032001,ddmmyyyy)),
• partition pmax values less than (maxvalue)
• )

23
Types of Partitioning

• Example HASH (most optimal when 8, 16, or 32
  partitions are used).
• Create table hash_partition
• (account_id varchar2(30))
• partition by HASH (account_id) partitions 16

24
Types of Partitioning

• Example LIST
• Create table list_partition
• (state_id varchar2(2))
• partition by LIST (state_id)
• (
• partition P_MI values (MI),
• partition P_CO values (CO)
• )

25
Practical Partitioning Usages

• Partition Usage I Data Warehousing
• based on date ranges (daily or monthly)

• Partition Usage II OLTP
• based upon frequently accessed key

• Partition Usage III ODS
• based upon date range and key

• Partition Usage IV Temporary Storage
• Partitions rotate and reused over time

Example A partition is based by day of month.
31 partitions created date function used to
place rows in partition based by day of month.
Partitions read by application that TRUNCATES
partitions after reading data.
26
Statistics

• Cost-based optimizer of Oracle is
  partitioning-aware. Rule-based optimizer does
  not do partitions.
• Cost-based optimizer works off of statistics.
  Statistics on standard tables easier to generate
  comprehend than statistics on partition tables.
  
• Statistics are the number one problem with
  partitioning implementations.
• With partitions, there are LOCAL and GLOBAL
  statistics.
• GLOBAL statistics are utilized when GLOBAL
  operations are performed.
• LOCAL statistics are utilized when partition key
  is available and partition elimination is
  possible.

27
Statistics

• Examples
• Select from range_partition where value1 b1
• Value1 is indexed GLOBALLY (i.e. only global
  statistics are reviewed). Optimizer will
  determine if full tables scan or index lookup is
  most appropriate.
• Select from range_partition
• Where value1 b1
• And value2 b2
• And part_key b3
• Local statistics global statistics are
  evaluated. Local statistics come into play
  because PART_KEY is within the where clause.

28
Statistics

• Statistics are gathered LOCALLY or GLOBALLY.
• Once gathered, they are tied together (i.e.
  partition maintenance operations that impact
  GLOBAL operations also impact GLOBAL statistics).
  
• If a partition is added to a table, GLOBAL
  statistics may disappear.

29
Statistics

• NO table statistics
• If there are NO table statistics at all, the
  optimizer acts relatively rule-based.
• Relatively Rule
• Rule 1 If GLOBAL index exists and can be used,
  it will be.
• Rule 2 If there are no GLOBAL indexes, LOCAL
  indexes will be used (if they exist).
• Therefore, NO statistics is an option if all
  indexes created on the table are good choices and
  any GLOBAL indexes are superior to LOCAL indexes.
  
• Conclusion If the partition is to be queried
  from a single column and that column is the
  partition key with an index, then the absence of
  gathering statistics is optimal.

30
Statistics

• Gathering Statistics - LOCALLY
• If the following commands are used INITIALLY to
  gather statistics, and no other command used,
  then GLOBAL statistics are derived.
• Execute dbms_stats.gather_table_stats(owner,'RANG
  E_PARTITION','P2',CASCADEgtTRUE)
• OR
• execute dbms_stats.gather_table_stats(owner,'RANG
  E_PARTITION','P2',CASCADEgtTRUE,METHOD_OPTgt'FOR
  ALL INDEXED COLUMNS SIZE 200')
• The only difference between these statistics
  commands is the generation of histograms.

31
Statistics

• Gathering Statistics LOCALLY, How To
• The GLOBAL statistics are populated after running
  a LOCAL script.
• Before generating statistics on any of the
  partitions
• Select num_rows from dba_tables where
• table_nameRANGE_PARTITION
• NUM_ROWSNULL
•  
• Select partition_name,num_rows from
  dba_tab_partitions
• where table_name RANGE_PARTITION
• All rows have a NUM_ROWSNULL

32
Statistics

• Gathering Statistics LOCALLY, cont.
• After a SINGLE execution of a statistic
  generation statement
• execute dbms_stats.gather_table_stats
• ('SYSTEM','RANGE_PARTITION','P1',CASCADEgtTRUE)
• Select num_rows from dba_tables where table_name
  
• RANGE_PARTITION
• Global Result 250,000
•  
• Select partition_name,num_rows from
  dba_tab_partitions
• where table_name RANGE_PARTITION
• Results P1 79999, P2 NULL, PMAX NULL

33
Statistics

• Gathering Statistics LOCALLY, cont.
• The GLOBAL statistics are guessed populated.
  Once LOCAL statistics are generated, GLOBAL
  statistics are aggregate and not reality.
  Gathering statistics this way uses the relatively
  rule method of optimization if GLOBAL index
  exists, its used. LOCAL indexes are evaluated
  if the where clause allows for partition pruning.
• These commands do not account for GLOBAL table
  units (the GLOBAL indexes were never analyzed).
• Once GLOBAL indexes are analyzed, all needed
  units have statistics and optimizer takes over.
• execute dbms_stats.gather_index_stats
• ('SYSTEM','RANGE_PARTITION_DESC')

34
Statistics

• Gathering Statistics GLOBALLY
• The following command will gather GLOBAL and
  LOCAL statistics.
• execute dbms_stats.gather_table_stats(owner,
• RANGE_PARTITION', GRANULARITYgt'ALL',CASCADEgtTRU
  E)
•  
• This command is equivalent to all the commands
  earlier. This is the recommended approach for
  initial gathering of statistics on partitions, as
  it ensures that ALL statistics are gathered.

35
Statistics

• Partition Maintenance Effects
• Vary by release.
• Release 8.x, GLOBAL statistics temporarily
  disappear (the num_rows value becomes NULL).
• Release 9.x, the GLOBAL statistics do not change.
• For both, statistics are no longer valid and need
  updating.
• An advantage of a partitioned table is the
  ability to perform maintenance work on smaller
  segments - ONLY the modified partitions are
  updated.
• All GLOBAL statistics for the table will be
  corrected (including GLOBAL index statistics).

36
Statistics

• Partition Maintenance Effects, cont.
• Once any partition modifications occur, ensure to
  run the statistics immediately on the effected
  partitions. Failure to do so can lead to the
  optimizers inability to parse the SQL statement.
  HANGING CAN OCCUR.
• If this occurs, best corrective actions are to
  remove all statistics and generate them again.

37
Partitioning Options

• Enable Row Movement
• Release 8I and above A new option with Oracle
  partition.
• Allows updates to partition key to occur when
  update would relocate a row from one partition
  to another.
• As the partition id is stored within the ROWID,
  this WILL CHANGE the ROWID for the row. This
  could impact application programs that utilize
  ROWID.
• Exchange without validation
• When a partition is exchanged with another table
• Alter table part_table exchange partition p1
• with table fk_table without validation

38
Partitioning Options

• Exchange without Validation, cont.
• It is possible that rows from this partition
  cannot be transparently queried.
• Validation ensures rows in fk_table qualify for
  given partition. When this option is bypassed
  (for performance reasons), one must ensure that
  new partition rows do not violate constrained
  partition boundaries.
• Given that partition pruning occurs prior to
  selection, violation of boundaries could render
  false results from a query.

39
Partitioning Options

• Exchange without validation - Example
• CREATE TABLE part_test
• (ID NUMBER NOT NULL) PARTITION BY RANGE (ID)
• (PARTITION P1 VALUES LESS THAN (10), PARTITION
  P2 VALUES LESS THAN (20))
• create table fk_table (id number not null)
• insert into part_test values (5)
• insert into fk_table values (5)
• commit
• alter table part_test exchange partition p2 with
  table fk_table without validation
• -- Returns 2 rows - both with the value of 5
• select from SYSTEM.PART_TEST
•  
• -- Returns 1 row - with the value of 5
• select from SYSTEM.PART_TEST where id5

40
Partitioning Options

• Oracle Initialization Parameters
• Oracles partitioning option coverts one table
  into many physical segments.
• More physical segments require more resources
  from Oracle SGA.
• In particular, Oracle initialization parameter
  DML_LOCKS must be set to accommodate
  partitioning. (i.e. if a table has 1000
  partitions, then DML_LOCKS must be set to at
  least 1000 or the table cannot be created.)

41
Conclusion

• With the advent of partitioning, improved
  database administration with maintenance
  operations occurring at a partition level rather
  than at the table or index level, allow Database
  Administrators to provide improved SLAs.
• Partitioning then becomes a crucial aspect of any
  database containing large amounts of data. As
  maintenance windows decrease in length due to
  cost of downtime, understanding methods to
  shorten database downtime is critical for
  success.
• After researching the various methods, each DBA
  should test partitioning scheme best suited for
  their environment.
• Performance and maintenance are the primary
  concerns to account for when implementing
  partitioning option. The partitioning of large
  tables allows for faster data access, as well as
  decreased maintenance windows.

42
Conclusion, cont.

• Partitioning should not be taken lightly.
  However, it should be considered for any database
  with excessive data or when excessive growth is
  anticipated.
•  
• Please check out our website at
• www.sagelogix.com/partitioning
  .
• This location has a download zip file containing
  source code, which will automate the maintenance
  of date based range partitions.

43
Q A
Frank Bommarito SageLogix, Inc www.sagelogix.com