Table Partition Key Columns Analysis (dtabpartkey.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides Oracle database administration functionality via the dtabpartkey.sql script.

The Script

rem  dtabpartkey.sql
rem
rem
set linesize 132
rem
ttitle 'Table Partition Key Columns'
rem
clear break
break on owner on name skip 1
rem
col OWNER  format a12 heading 'OWNER'
col NAME   format a35 heading 'NAME'
col OBJECT_TYPE  format a10 heading 'TYPE'
col COLUMN_NAME  format a25 heading 'COLUMN'
col COLUMN_POSITION  format 999 heading 'POS'
rem
select
       OWNER,
       NAME,
       OBJECT_TYPE,
       COLUMN_POSITION,
       COLUMN_NAME
 from sys.dba_part_key_columns
where owner like nvl(upper('&owner'),'%')
  and name like nvl(upper('&table'),'%')
order by
       OWNER,
       NAME,
       OBJECT_TYPE,
       COLUMN_POSITION
/
set linesize 80

What This Script Does

This script provides comprehensive information about partition key columns for partitioned tables and indexes. It shows the column names and their positions in the partition key, helping DBAs understand partitioning strategies, validate partition designs, and troubleshoot partition-related issues.

Key Features

• Partition Key Analysis: Shows all columns used in partition keys
• Column Positioning: Displays the order of columns in composite partition keys
• Object Type Support: Covers both tables and indexes
• Flexible Filtering: Filter by owner and object name patterns
• Clear Organization: Groups results by owner and object name

Usage

Run the script and provide filter criteria when prompted:

@dtabpartkey.sql

Input Parameters:

• Owner: Schema owner name or pattern (use % for all owners)
• Table: Table/index name or pattern (use % for all objects)

Required Privileges

SELECT on SYS.DBA_PART_KEY_COLUMNS

Sample Output

                                        Table Partition Key Columns

OWNER        NAME                                TYPE       POS COLUMN
------------ ----------------------------------- ---------- --- -------------------------
SALES        CUSTOMER_ORDERS                     TABLE        1 ORDER_DATE
                                                              2 REGION_ID

             ORDERS_REGION_IDX                   INDEX        1 REGION_ID
                                                              2 ORDER_DATE

             QUARTERLY_SALES                     TABLE        1 SALE_DATE

HR           EMPLOYEE_HISTORY                    TABLE        1 EMPLOYEE_ID
                                                              2 EFFECTIVE_DATE

             EMP_DEPT_IDX                        INDEX        1 DEPARTMENT_ID

FINANCE      TRANSACTION_LOG                     TABLE        1 TRANSACTION_DATE

             MONTHLY_SUMMARIES                   TABLE        1 SUMMARY_MONTH
                                                              2 ACCOUNT_TYPE

             TXN_HASH_IDX                        INDEX        1 TRANSACTION_ID

Key Output Columns

• OWNER: Schema that owns the partitioned object
• NAME: Name of the partitioned table or index
• TYPE: Object type (TABLE or INDEX)
• POS: Position of the column in the partition key (1, 2, 3, etc.)
• COLUMN: Name of the column used in the partition key

Understanding Partition Key Design

Single Column Partition Keys

• POS = 1 only: Simple partition key using one column
• Common for: Date-based partitioning, range partitioning
• Examples: ORDER_DATE, TRANSACTION_DATE, EMPLOYEE_ID

Composite Partition Keys

• Multiple POS values: Partition key using multiple columns
• Order matters: Column position affects partition pruning
• Common for: Hash partitioning, list partitioning, subpartitioning

Partition Key Types by Position

Primary Partition Key (POS = 1)

• Most important: Primary partitioning column
• Performance impact: Major influence on partition pruning
• Common choices: Date columns, ID ranges, geographic regions

Secondary Partition Key (POS = 2+)

• Subpartitioning: Often used for subpartitions
• Composite keys: Additional partitioning dimensions
• Hash distribution: Often hash-based for even distribution

Common Partitioning Patterns

Date-Based Partitioning

-- Example: Monthly partitions
OWNER: SALES, NAME: CUSTOMER_ORDERS
POS 1: ORDER_DATE (Range partitioning by month)

Composite Range-Hash

-- Example: Range by date, hash by region
OWNER: SALES, NAME: CUSTOMER_ORDERS
POS 1: ORDER_DATE (Range partitioning)
POS 2: REGION_ID (Hash subpartitioning)

Hash Partitioning

-- Example: Hash distribution
OWNER: FINANCE, NAME: TXN_HASH_IDX
POS 1: TRANSACTION_ID (Hash partitioning)

Analysis Use Cases

1. Partition Design Review

   • Validate partition key choices
   • Ensure proper column ordering
   • Review partitioning strategy effectiveness

2. Performance Analysis

   • Understand partition pruning behavior
   • Identify suboptimal partition keys
   • Plan query optimization strategies

3. Maintenance Planning

   • Plan partition operations
   • Understand partition dependencies
   • Design partition maintenance scripts

4. Schema Documentation

   • Document partitioning architecture
   • Create partition design standards
   • Support migration planning

Advanced Analysis

Partition Pruning Analysis

-- After identifying partition keys, analyze query patterns:
-- Do queries filter on partition key columns?
-- Are queries taking advantage of partition elimination?
-- Is the partition key order optimal for common queries?

Partition Key Effectiveness

-- Check if partition keys provide good data distribution:
SELECT partition_name, num_rows, blocks
FROM dba_tab_partitions
WHERE table_owner = 'SALES'
AND table_name = 'CUSTOMER_ORDERS'
ORDER BY partition_position;

Index Partition Alignment

-- Verify index partitioning matches table partitioning:
-- Look for matching partition keys between tables and indexes
-- Check for locally vs globally partitioned indexes

Filter Examples

Analyze Specific Schema

Enter value for owner: SALES
Enter value for table: %

Find All Partitioned Objects

Enter value for owner: %
Enter value for table: %

Check Specific Table

Enter value for owner: HR
Enter value for table: EMPLOYEE_HISTORY

Find Date-Partitioned Objects

-- After running, look for columns with DATE in the name
-- Common patterns: %_DATE, %_TIME, CREATED%, MODIFIED%

Partition Design Best Practices

Column Selection

• High Selectivity: Choose columns frequently used in WHERE clauses
• Even Distribution: Ensure good data distribution across partitions
• Stable Values: Avoid columns that change frequently

Composite Key Ordering

• Query Patterns: Order columns based on common query predicates
• Selectivity: Most selective column first (for range partitioning)
• Maintenance: Consider partition maintenance requirements

Common Patterns

• Time-based: DATE columns for range partitioning
• Geographic: REGION_ID for list or hash partitioning
• Hash Distribution: ID columns for even distribution
• Functional: Computed values for complex distribution rules

Troubleshooting Partition Issues

Poor Performance

• Check partition pruning: Are queries using partition keys?
• Review key order: Is the most selective column first?
• Analyze distribution: Are partitions roughly equal in size?

Maintenance Problems

• Key stability: Do partition key values change?
• Partition operations: Are ADD/DROP operations efficient?
• Index alignment: Are indexes properly partitioned?

Design Issues

• Too many partitions: May impact performance and management
• Uneven distribution: Some partitions much larger than others
• Wrong key choice: Partition key doesn’t match query patterns

Related Scripts

• dtabhist.sql - Table histogram analysis
• dtable.sql - Table storage analysis
• dindex.sql - Index analysis including partitioned indexes
• dobject.sql - General object information

Integration with Performance Tuning

Query Optimization

-- Use partition key information to:
-- 1. Verify queries include partition key predicates
-- 2. Optimize WHERE clause column order
-- 3. Plan index strategies for partitioned tables

Execution Plan Analysis

-- Check for partition pruning in execution plans:
-- Look for "Pstart" and "Pstop" values
-- Verify partition elimination is occurring
-- Identify queries scanning all partitions

Best Practices

1. Regular Review

   • Periodically review partition key effectiveness
   • Monitor query patterns against partition design
   • Validate partition pruning is occurring

2. Documentation

   • Document partition strategy rationale
   • Maintain partition key standards
   • Create partition design guidelines

3. Performance Monitoring

   • Track partition-wise query performance
   • Monitor partition size distribution
   • Analyze partition maintenance overhead