Working with Partitioned Tables The Unpleasant Details

1
Working with Partitioned Tables The Unpleasant
Details

• Brian HitchcockOCP 8, 8i, 9i DBA
• Sun Microsystems
• brian.hitchcock_at_sun.com
• brhora_at_aol.com

NoCOUG
Brian Hitchcock November 13, 2003
Page 1
2
The Application History

• Website click-stream data
• Large amounts of data generated daily
• Users want to access data immediately
• Next day was acceptable
• Users want 14 months of data on-line
• Performance was poor
• Huge tables, millions of rows
• Inserting new data very slow
• Retrieving data very slow
• User generate SQL
• No controls on the quality of the SQL

3
Application How it Works

• Website click-stream log files sent to db machine
  daily
• Database tables setup with weekly partitioning
• 3 main tables
• Range partitioning on date
• New data added to db once per day
• Load must finish before users can access each day
• Failure of load process, users cant work, must
  load 2 days data to catch up

4
The Application Act I

• Consultants setup weekly partitioning
• Vendor scripts all setup for daily partitioning
• We had to setup our own maintenance scripts
• Once per week
• Create new week partition
• oldest weekly partition
• Analyze new partition
• Vendor tech support wont support weekly
  partitioning
• 14 months of data on-line, 60 weekly partitions

5
The Application Act II

• The Consultant Is Gone (call Brian)
• Vendor wants us to upgrade
• New app version is much faster (trust us)
• Need to move to daily partitioning
• Users agree to 7 months data online
• Improve performance overall (less total data)
• We can use vendors maintenance scripts
• Reduced headcount makes our scripts hard to
  maintain
• Easier to get support from vendor tech support

6
Project Phases

• As I inherited the project
• 14 months weekly partitions
• Vendor Upgrade I
• Move to daily partitioning
• Drop 7 months of data
• Vendor Upgrade II
• Required renaming of index partitions
• Must match table partition names

7
What Are Partitioned Tables?

• Single Big Table
• Many sub-tables (partitions)
• Each partition
• Acts like a separate table
• Index (partition) for each partition
• Performance
• Load to separate partition(s)
• Analyze each partition separately
• SQL can be satisfied with small subset of entire
  table
• Partition pruning
• Answer to a Jeopardy question
• Alternate term for DBA job security

8
Why Use Them?

• Performance
• Faster SQL
• Partition Pruning
• Faster Analyze
• Only analyze single partition
• Logical
• Application data is partitioned (weekly for
  this case)
• Easy to add new partition, drop oldest partition
• Faster than deleting some rows from larger table
• Faster than inserting new rows into larger table

9
Partitioned Table
Table_1 Partition_1
Table_1

Data Data 10/01/2003
Data Data 09/01/2003
Data Data 08/01/2003
Data Data 07/01/2003
Column A Column B Date

Data Data 10/01/2003
Data Data 09/01/2003
Data Data 08/01/2003
Data Data 07/01/2003
Data Data 06/01/2003
Data Data 05/01/2003
Data Data 04/01/2003
Data Data 03/01/2003
Data Data 02/01/2003
Data Data 01/01/2003
Data Data 12/01/2002
Data Data 11/01/2002
Column A Column B Date

Data Data 06/01/2003
Data Data 05/01/2003
Data Data 04/01/2003
Data Data 03/01/2003
Column A Column B Date
Table_1 Partition_2

Data Data 02/01/2003
Data Data 01/01/2003
Data Data 12/01/2002
Data Data 11/01/2002
Column A Column B Date
Non-Partitioned
Table_1 Partition_3
Partitioned
10
Inserting Data

• Oracle examines
• Inserted value of column used for range
  partitioning
• High_value of existing partitions
• Rows with values greater than or equal to the
  highest high_value
• If MAXVALUE used, inserted
• If MAXVALUE not used, rejected

11
Partitioned Table
Values gt or 10/02/2003 rejected

Data Data 10/01/2003
Data Data 09/01/2003
Data Data 08/01/2003
Data Data 07/01/2003
Column A Column B Date
Partition_1 high_value 10/02/2003
Rows with range values less than 10/02/2003 And gt
or to 06/02/2003 Will be inserted into
Partition_1

Data Data 06/01/2003
Data Data 05/01/2003
Data Data 04/01/2003
Data Data 03/01/2003
Column A Column B Date
Partition_2 high_value 06/02/2003
Rows with range values less than 06/02/2003 And gt
or 02/02/2003 Will be inserted into Partition_2

Data Data 02/01/2003
Data Data 01/01/2003
Data Data 12/01/2002
Data Data 11/01/2002
Column A Column B Date
Partition_3 high_value 02/02/2003
Rows with range values less than 02/02/2003 Will
be inserted into Partition_3
Partitioned
12
Partitioned Table MAXVALUE

Data Data 10/01/2003
Data Data 09/01/2003
Data Data 08/01/2003
Data Data 07/01/2003
Column A Column B Date
Partition 1 high_value MAXVALUE
Rows with range values greater than or to
06/02/2003 Will be inserted into Partition 1

Data Data 06/01/2003
Data Data 05/01/2003
Data Data 04/01/2003
Data Data 03/01/2003
Column A Column B Date
Partition 2 high_value 06/02/2003
Rows with range values less than 06/02/2003 Will
be inserted into Partition 2

Data Data 02/01/2003
Data Data 01/01/2003
Data Data 12/01/2002
Data Data 11/01/2002
Column A Column B Date
Partition 3 high_value 02/02/2003
Rows with range values less than 02/02/2003 Will
be inserted into Partition 3
Partitioned Using MAXVALUE
13
Partitioning -- Example
CREATE TABLE stock_xactions (stock_symbol
CHAR(5), stock_series CHAR(1), num_shares
NUMBER(10), price NUMBER(5,2), trade_date
DATE) STORAGE (INITIAL 100K NEXT 50K)
LOGGING PARTITION BY RANGE (trade_date) (PARTITION
sx1992 VALUES LESS THAN (TO_DATE('01-JAN-1993','D
D-MON-YYYY')), PARTITION sx1993 VALUES LESS THAN
(TO_DATE('01-JAN-1994','DD-MON-YYYY')), PARTITION
sx1994 VALUES LESS THAN (MAXVALUE)) select
from user_tab_partitions TABLE_NAME
COM PARTITION_NAME SUBPARTITI
HIGH_VALUE -----------------------------
--- ------------------------------
---------- --------------------------------------
------------------------------- STOCK_XACTIONS
N0 SX1992
0 TO_DATE(' 1993-01-01 000000',
'SYYYY-MM-DD HH24MISS', STOCK_XACTIONS
N0 SX1993
0 TO_DATE(' 1994-01-01 000000',
'SYYYY-MM-DD HH24MISS', STOCK_XACTIONS
N0 SX1994
0 MAXVALUE 3 rows selected.
14
Weekly Partition Maintenance

• Once per week
• Compute high_value of next weekly partition
• Create partition for next week
• Drop oldest weekly partition
• Rebuild indexes
• Or drop and recreate indexes
• Load data into new partition
• Analyze new partition
• Once loaded, analyzed
• Maximum read performance
• Partition no longer has inserts or analyze

15
Weekly Maintenance
Add New Partition Load Data, Analyze
New
Latest
Existing Partitions



14 months, 60 weekly partitions






Existing Partitions
Oldest
Drop Oldest Partition
16
Partitions and Tablespaces

• For performance, want to control placement of
  partitions
• New, data being loaded
• Existing, data being retrieved
• Use separate tablespace for each partition
• Spread across 8 filesystems (8 sets of disks)
• Tablespaces of sequential table partitions
• Same for index partitions but offset
• Corresponding table/index partitions always on
  different filesystems

17
Partitions and Tablespaces

• Design Goal
• Partition being loaded, analyzed
• One separate disks from partitions being read
• Assumes most queries read data from most recent
  partitions
• If partition_1 being loaded
• Partitions 2, 3, 4 can be read without
  conflicting with the load, analyze
• Partitions can be used for fine-grained placement
  of data on disk

18
A Plug for Certification

• I didnt know anything about Partitioned Tables
• Wasnt planning to use them
• Memorized what I needed to pass the OCP exam
• Within a month
• Phone rings Can you help with partitioned
  tables?

19
Types of Partitioning

• Range
• Partition based on value of column(s) of table
• Date
• Part Number
• SSN
• Hash
• Rows spread evenly across all partitions
• List (9i)
• Partitions based on user-specified lists of values

20
Partitioning Details

• Create partitions
• You need to name each partition
• If you dont
• Oracle names them for you
• The names are not easy to use
• Many other partitioning possibilities
• This is not a training course on partitioned
  tables
• Only cover what I actually used
• More options with 9i, this was for 8i

21
What About Indexes?

• Indexes of Partitioned Tables
• Local
• Index partitions always align with table
  partitions
• Global
• Different partitioning from table partitioning
• Become invalid when
• Add, drop, split existing partitions
• SQL to manually rebuild invalid or unusable index
  partitions

22
Partition Maintenance

• Depends on your application
• May need to handle
• Adding new
• Dropping old
• Splitting
• Analyzing

23
Partitioned Table Info

• System Tables
• dba_tables
• dba_tab_partitions
• dba_indexes
• dba_ind_partitions
• dba_part_key_columns

24
Partitioned Table Info -- SQL

• Number of partitioned tables
• select count() from dba_tables where
  partitioned'YES'
• Name of each partitioned table
• select table_name, partitioned from dba_tables
  where partitioned'YES' order by table_name
• Number of partitions in each partitioned table
• select table_name, count() from
  dba_tab_partitions group by table_name
• Same info for indexes
• select count() from dba_indexes where
  partitioned'YES'
• select index_name, partitioned from dba_indexes
  where partitioned'YES' order by index_name
• select index_name, count() from
  dba_ind_partitions group by index_name

25
Partitioned Table Info -- SQL

• Number of columns used, range partitioning
• select name, object_type, count() from
  dba_part_key_columns group by name, object_type
• Partitioning key columns
• select from dba_part_key_columns
• Indexes on each partitioned table
• select table_name, index_name from dba_indexes
  where tablespace_name is NULL order by
  table_name, index_name

26
Partitioned Table Info -- SQL

• High values for table partitions
• set long 30
• select table_name, tablespace_name,
  partition_name, high_value from
  dba_tab_partitions order by table_name,
  tablespace_name, partition_name
• High values for index partitions
• set long 30
• select index_name, tablespace_name,
  partition_name, high_value from
  dba_ind_partitions order by index_name,
  tablespace_name, partition_name

27
Partition Range

• How to find range for each partition?
• Only have high_value for each partition
• Have to examine ordered list of high_value
• Ranges not stored in system tables
• Range of values is dynamic
• Range of highest partition changes as higher
  values inserted (assumes MAXVALUES used)
• Same for lowest partition
• Examine ordered list of high_value
• Cant order by LONG
• high_value is a LONG

28
Partition Range

• Need ordered list of high_values
• Use DBA_TAB_PARTITIONS
• partition_position
• High_value
• Use partition_position?
• Oracle docs only say
• position of the partition within the table
• What exactly does that mean?

29
Partition Range

• Partition_Position
• Numbers the partitions in order of high_value
• Numbers change with each add, split, drop of any
  partition
• Is dynamic
• What to do?
• I decided to use high_value because it doesnt
  change as partitions are manipulated
• But, cant order by
• More on this later

30
Vendor Upgrade I

• Existing 14 months data
• Weekly partitions
• Reduce to 7 months
• Hard to find the correct partitions to drop
• Split each weekly partition
• 7 daily partitions

31
Do the Math

• 7 Months (36 weeks)
• 252 daily partitions
• Times 3 tables
• Times 11 indexes
• Total of 3528 partitions (756 table 2772
  index)
• Vendor upgrade
• Requires specific, rigid partition naming scheme
• I dont get to pick the partition names

32
Oracle Utilities?

• There arent any that
• Split tables based on your requirements
• Take a set of existing partitions, tablespaces
• Move to new partitions, tablespaces
• No utility to map from one partitioning to
  another
• As the number of partitions grows, so does the
  work involved to shift from one partitioning
  scheme to another
• The same things that make partitioned tables so
  good for performance make them a lot more work to
  maintain
• Lots of small pieces to maintain

33
Partition Split Issues

• When splitting
• Need new tablespaces
• Have existing tablespaces
• Split SQL sends both table and index partitions
  to same new tablespace
• New tablespace needs to be 2x final size
• Separate tablespace for new index partitions
• Reclaim disk space from new table partition
  tablespace
• Overall need 3x disk space during splits
• Need to reclaim this disk space after move to
  daily partitions

34
Disk Space Needed
Original Partitions
Existing Table Partition TP1
Existing Index Partition IP1
Tablespace Ind1
Tablespace Tab1
Split Partitions
Tablespace Tab2
Tablespace Ind2
Post-split Table Partition TP1a, Index Partition
IP1a
Post-split Table Partition TP1b, Index Partition
IP1b
Tablespace Tab3
Tablespace Ind3
Tablespace Tab4
Tablespace Ind4
Table Partition TP1a
Index Partition IP1a
Table Partition TP1b
Index Partition IP1b
Separate Table/Index Partitions

• Need 3x original tablespace (disk space)

Assuming index partition is same size as table
partition Assumes indexes not dropped before split
35
Disk Space Reclaimed
EMPTY TBLSPC
Existing Table Partition TP1
Existing Index Partition IP1
Tablespace Ind1
Tablespace Tab1
Tablespace Tab2
Tablespace Ind2
Post-split Table Partition TP1a, Index Partition
IP1a
Post-split Table Partition TP1b, Index Partition
IP1b
Tablespace Tab3
Tablespace Ind3
Tablespace Tab4
Tablespace Ind4
Table Partition TP1a
Index Partition IP1a
Table Partition TP1b
Index Partition IP1b

• After split, need to reclaim 2/3 of disk space

36
Tasks for Daily Partitioning

• Create new tablespaces
• Single tablespace for partitions of each table
• Twice as big as final size
• Single tablespace for partitions of each index
• Drop existing indexes (weekly partitions)
• Total disk space needed reduced from 3x to 2x
• Split weekly partitions into daily partitions
• Re-create indexes
• Daily partitions created automatically
• Reclaim disk space

37
Move to Daily Partitioning

• Reduce to 7 months week partitions
• SQL to drop existing weekly partitions simple to
  generate
• Hard to see which partitions to drop since you
  cant order by high_value
• Split Existing Weekly Partitions
• Generate SQL for one week
• Cut/paste/edit for other weekly partitions
• Tedious, error prone
• SQL isnt consistent enough to make automation
  straight-forward
• 37 weekly partitions need splitting

38
Weekly Partition Split
High_value 2003-05-10
-- existing weekly partition BRH_TAB_PART_5
High_value 2003-05-09
partition BRH_TAB_PART_5_nexta
BRH_TAB_ PART_5_7
2003-05-10
High_value 2003-05-08
partition BRH_TAB_PART_5_nextb
BRH_TAB_ PART_5_6
2003-05-09
High_value 2003-05-07
partition BRH_TAB_PART_5_nextc
BRH_TAB_ PART_5_5
2003-05-08
High_value 2003-05-06
partition BRH_TAB_PART_5_nextd
BRH_TAB_ PART_5_4
2003-05-07
High_value 2003-05-05
BRH_TAB_PART_5_nexte
BRH_TAB_ PART_5_3
2003-05-06
a
BRH_TAB_ PART_5_2
BRH_TAB_ PART_5_1
High_value 2003-05-04
2003-05-05
BRH_TAB_ PART_5_7
BRH_TAB_ PART_5_3
BRH_TAB_ PART_5_4
BRH_TAB_ PART_5_6
BRH_TAB_ PART_5_5
BRH_TAB_ PART_5_2
BRH_TAB_ PART_5_1
39
Weekly Partition Split SQL
-- existing weekly partition BRH_TAB_PART_5
TO_DATE('2003-05-10 000000' alter
table brhuser.BRH_TABLE split partition
BRH_TAB_PART_5 at ( TO_DATE('2003-05-09
000000', 'SYYYY-MM-DD HH24MISS',
'NLS_CALENDARGREGORIAN')) into (partition
BRH_TAB_PART_5_nexta tablespace BRH_TAB_TBLSPC,
partition BRH_TAB_PART_5_7 tablespace
BRH_TAB_TBLSPC) alter table brhuser.BRH_TABLE
split partition BRH_TAB_PART_5_nexta at (
TO_DATE('2003-05-08 000000', 'SYYYY-MM-DD
HH24MISS', 'NLS_CALENDARGREGORIAN')) into
(partition BRH_TAB_PART_5_nextb tablespace
BRH_TAB_TBLSPC, partition BRH_TAB_PART_5_6
tablespace BRH_TAB_TBLSPC) alter table
brhuser.BRH_TABLE split partition
BRH_TAB_PART_5_nextb at ( TO_DATE('2003-05-07
000000', 'SYYYY-MM-DD HH24MISS',
'NLS_CALENDARGREGORIAN')) into (partition
BRH_TAB_PART_5_nextc tablespace BRH_TAB_TBLSPC,
partition BRH_TAB_PART_5_5 tablespace
BRH_TAB_TBLSPC) alter table brhuser.BRH_TABLE
split partition BRH_TAB_PART_5_nextc at (
TO_DATE('2003-05-06 000000', 'SYYYY-MM-DD
HH24MISS', 'NLS_CALENDARGREGORIAN')) into
(partition BRH_TAB_PART_5_nextd tablespace
BRH_TAB_TBLSPC, partition BRH_TAB_PART_5_4
tablespace BRH_TAB_TBLSPC) alter table
brhuser.BRH_TABLE split partition
BRH_TAB_PART_5_nextd at ( TO_DATE('2003-05-05
000000', 'SYYYY-MM-DD HH24MISS',
'NLS_CALENDARGREGORIAN')) into (partition
BRH_TAB_PART_5_nexte tablespace BRH_TAB_TBLSPC,
partition BRH_TAB_PART_5_3 tablespace
BRH_TAB_TBLSPC) alter table brhuser.BRH_TABLE
split partition BRH_TAB_PART_5_nexte at (
TO_DATE('2003-05-04 000000', 'SYYYY-MM-DD
HH24MISS', 'NLS_CALENDARGREGORIAN')) into
(partition BRH_TAB_PART_5_1 tablespace
BRH_TAB_TBLSPC, partition BRH_TAB_PART_5_2
tablespace BRH_TAB_TBLSPC)
40
Weekly Partition Split SQL

• Split partition SQL
• Requires two new partition names
• Splits existing partition by specifying new
  high_value
• Existing data moves to two new partitions based
  on above or below new high_value
• If you dont specify partition names, Oracle
  names them for you
• Oracle partition names are not intuitive
• New partitions move to specified tablespaces
• New index partitions also moved to same
  tablespaces
• Need to move new index partitions to separate
  tablespace(s) as separate step
• Or, drop indexes, recreate after all splits done
• Indexes split automatically as table splits

41
Vendor Upgrade II

• Now that daily partitioning is setup
• Vendor upgrade fails
• Specific index partition naming requirements
• Our home-grown weekly partitioning didnt meet
  these
• We need this done by Monday
• Labor Day Weekend
• I didnt want to do this manually again
• Need to rename existing index partition names
• Number in partition name must agree with number
  in table partition name
• Lots of SQL that must be perfect

42
But Wait, Theres More

• Vendor had executed a script that partially
  renamed some partitions
• I had to fix this
• Existing names of these partitions conflicted
  with the names I had to generate
• Vendor upgrade process requires specific format
  for daily partition name and number
• Where is the Vendor DBA?

43
How to Automate?

• What needs to be done
• Document existing names of table and index
  partitions
• Rename index partitions using vendor requirements
• Deal with vendors mistakes some partitions
  already renamed
• Develop process to deal with this and future needs

44
SQL

• Document existing table and index partition names
• Create new names for index partitions
• Index partitions use same numbering as
  corresponding table partition names
• Generate all SQL statements to alter existing
  index partition names
• Document all intermediate steps

45
Details

• High Value of each partition stored in LONG
  column
• LONG datatype is special
• Cant be used in any SQL function
• Cant order by etc.
• In SQLPlus, set LONG 100 to see all of high_value

46
SQL Script

• Need to order by high value to show existing
  weekly partitions
• How to get around LONG limitations?
• Export
• LONG is converted to a string
• Import
• Import string into VARCHAR2 column
• Can order by on the VARCHAR2 column

47
Table of Index Partition Names

• Store data about existing table and index
  partitions
• Use SQL to create new index partition names
  within the table
• Flexible
• Self-documenting
• Select from table to generate SQL for
• Changing index partition names
• Fix vendor partition naming mistakes

48
For Each Partitioned Index

• Create table
• Table name
• Table tablespace name
• Table partition name
• Table partition high value (text version)
• Index name
• Index tablespace name
• Index partition name (Existing)
• Index partition high value (text version)
• Temporary index partition name
• Final index partition name (used for renaming)

49
Index Partition Renaming

• The process
• Spool existing table and index info
• Including high_value for each partition
• Use SQLLoader to insert info into table
• Use SQL to insert temporary and final index
  partition names into table
• Temporary index partition name deals with any
  name conflicts
• Use SQL to generate all SQL statements to
  actually change index partition names

50
SQL Used
--gt generate file of table partitions for
BRH_TABLE... sqlplus -s brhuser_at_BRH_DB1 spool
BRH_TABLE_partition_data_05242003.txt set long
30 set pagesize 1500 set linesize 150 set echo
off set feedback off set heading off select
SUBSTR(table_name, 1, 25), ',',
SUBSTR(tablespace_name,1,20), ',',
SUBSTR(partition_name,1,20),',"', high_value, '"'
from dba_tab_partitions where table_name'BRH_TABL
E' order by partition_name spool
off exit example data BRH_TABLE ,
BRH_TAB_TBLSPC , BRH_TAB_PART_1 ," TO_DATE('
2003-05-04 000000' " BRH_TABLE ,
BRH_TAB_TBLSPC , BRH_TAB_PART_10 ," TO_DATE('
2003-05-13 000000' " BRH_TABLE ,
BRH_TAB_TBLSPC , BRH_TAB_PART_100 ," TO_DATE('
2002-12-02 000000' " BRH_TABLE ,
BRH_TAB_TBLSPC , BRH_TAB_PART_101 ," TO_DATE('
2002-12-03 000000' " BRH_TABLE ,
BRH_TAB_TBLSPC , BRH_TAB_PART_102 ," TO_DATE('
2002-12-04 000000' "
51
SQL Used

• ? create table to load this data into...
• ? This table holds the existing table partition
  data ordered by partition name
• create table BRH_TABLE_part_data_t
• (table_name VARCHAR(30), tab_tblspc_name
  VARCHAR2(25), tab_part_name VARCHAR2(25),
  tab_high_value_text VARCHAR2(35))
• --gt sqlldr control file to load this data...
• sqlldr_load_BRH_TABLE_partition_data.ctl
• load data
• infile BRH_TABLE_partition_data_05242003.txt'
• into table BRH_TABLE_part_data_t
• fields terminated by ',' optionally enclosed by
  '"'
• (table_name, tab_tblspc_name, tab_part_name,
  tab_high_value_text)
• --gt sqlldr command...
• sqlldr useridpsycho/psycho controlsqlldr_load_BR
  H_TABLE_partition_data.ctl logsqlldr_load_BRH_TAB
  LE_partition_data.log

52
SQL Used
--gt trim the high_value_text update
BRH_TABLE_part_data_t set tab_high_value_textSUBS
TR(tab_high_value_text,12,19) commit Example
data BRH_TABLE BRH_TAB_TBLSPC
BRH_TAB_PART_1 2003-05-04
000000 BRH_TABLE BRH_TAB_TBLSPC
BRH_TAB_PART_10 2003-05-13 000000 BRH_TABLE
BRH_TAB_TBLSPC BRH_TAB_PART_100
2002-12-02 000000 BRH_TABLE
BRH_TAB_TBLSPC BRH_TAB_PART_101 2002-12-03
000000 BRH_TABLE BRH_TAB_TBLSPC
BRH_TAB_PART_102 2002-12-04 000000
53
SQL Used
--gt create table to hold same data but in
high_value order... create table
BRH_TABLE_part_data (table_name
VARCHAR(30), tab_tblspc_name VARCHAR2(25), tab_par
t_name VARCHAR2(25), tab_high_value_text
VARCHAR2(35)) --gt populate the new
table... insert into BRH_TABLE_part_data (select
from BRH_TABLE_part_data_t) order by
tab_high_value_text select from
BRH_TABLE_part_data commit repeat for the
other 2 partitioned tables...
54
SQL Used
Now, for the index partitions, for each of 11
indexes, generate file of index partitions for
BRH_INDEX... spool BRH_INDEX_partition_data_05252
003.txt set long 30 set pagesize 1500 set
linesize 150 set echo off set feedback off set
heading off select SUBSTR(index_name, 1, 25),
',', SUBSTR(tablespace_name,1,20), ',',
SUBSTR(partition_name,1,20),',"', high_value, '"'
from dba_ind_partitions where index_name'X_BRH_IN
DEX' order by partition_name spool off example
data X_BRH_INDEX, BRH_IND_TBLSPC ,
BRH_IND_PART_246 ," TO_DATE(' 2003-05-04
000000' " X_BRH_INDEX, BRH_IND_TBLSPC ,
BRH_IND_PART_10 ," TO_DATE(' 2003-05-13 000000'
" X_BRH_INDEX, BRH_IND_TBLSPC ,
BRH_IND_PART_93 ," TO_DATE(' 2002-12-02 000000'
" X_BRH_INDEX, BRH_IND_TBLSPC ,
BRH_IND_PART_94 ," TO_DATE(' 2002-12-03 000000'
" X_BRH_INDEX, BRH_IND_TBLSPC ,
BRH_IND_PART_95 ," TO_DATE(' 2002-12-04 000000'
" note how these partition numbers do not line
up with the table partition numbers, and, note
that these 5 rows were spread all over the spool
file making it very hard to see which table/index
partition names didn't have the same partition
numbers...
55
SQL Used
--gt create table to load this data
into... create table BRH_INDEX_part_data_t (index
_name VARCHAR(30), ind_tblspc_name VARCHAR2(25),
ind_part_name VARCHAR2(25), ind_high_value_text
VARCHAR2(35)) --gt sqlldr control file to load
this data... sqlldr_load_BRH_INDEX_partition_data
.ctl load data infile BRH_INDEX
_partition_data_05252003.txt' into table
BRH_INDEX _part_data_t fields terminated by ','
optionally enclosed by '"' (index_name,
ind_tblspc_name, ind_part_name,
ind_high_value_text) --gt sqlldr
command... sqlldr useridpsycho/psycho
controlsqlldr_load_ BRH_INDEX _partition_data.ctl
logsqlldr_load_ BRH_INDEX _partition_data.log
56
SQL Used
--gt trim the high_value_text update
BRH_INDEX_part_data_t set ind_high_value_textSUBS
TR(ind_high_value_text,12,19) commit --gt
create table to hold same data but in high_value
order... create table BRH_INDEX_part_data (index_
name VARCHAR(30), ind_tblspc_name
VARCHAR2(25), ind_part_name VARCHAR2(25), ind_high
_value_text VARCHAR2(35))
57
SQL Used
--gt populate the new table... insert into
BRH_INDEX_part_data (select from
BRH_INDEX_part_data_t) order by
ind_high_value_text select from
BRH_INDEX_part_data example data X_BRH_INDEX
BRH_IND_TBLSPC BRH_IND _PART_15
2002-09-15 000000 X_BRH_INDEX BRH_IND
_TBLSPC BRH_IND _PART_16 2002-09-16
000000 X_BRH_INDEX BRH_IND _TBLSPC
BRH_IND _PART_17 2002-09-17
000000 X_BRH_INDEX BRH_IND _TBLSPC
BRH_IND _PART_18 2002-09-18
000000 X_BRH_INDEX BRH_IND _TBLSPC
BRH_IND _PART_19 2002-09-19
000000 commit repeat for the other 10
indexes
58
SQL Used
we now have partition info for all 3 tables, 11
indexes in separate tables, all ordered by
high_value -- still very difficult to see which
partition names don't line up between tables and
indexes since they are all in separate
tables INDEX_NAME IND_TBLSPC_NAME
IND_PART_NAME IND_HIGH_VALUE_TEXT ------------
------------------
-------------------------
-------------------------
-------------------------------
---- X_BRH_INDEX BRH_IND_TBLSPC
BRH_IND_PART_15 2002-09-15 000000 X_BRH_INDE
X BRH_IND_TBLSPC
BRH_IND_PART_16 2002-09-16
000000 X_BRH_INDEX BRH_IND_TBLSPC
BRH_IND_PART_17 2002-09-17 000000 ... X_BRH_
INDEX BRH_IND_TBLSPC BRH_IND_PART_250
2003-05-08 000000 X_BRH_INDEX
BRH_IND_TBLSPC BRH_IND_PART_251 2003-05-09
000000 X_BRH_INDEX BRH_IND_TBLSPC
BRH_IND_PART _252 2003-05-10
000000 X_BRH_INDEX BRH_IND_TBLSPC
BRH_IND_PART_8 2003-05-11 000000 X_BRH_INDEX
BRH_IND_TBLSPC BRH_IND_PART_9
2003-05-12 000000 X_BRH_INDEX
BRH_IND_TBLSPC BRH_IND_PART_10 2003-05-13
000000 X_BRH_INDEX BRH_IND_TBLSPC
BRH_IND_PART_11 2003-05-14 000000 X_BRH_INDE
X BRH_IND_TBLSPC BRH_IND_PART_12
2003-05-15 000000 X_BRH_INDEX
BRH_IND_TBLSPC BRH_IND_PART_13 2003-05-16
000000 X_BRH_INDEX BRH_IND_TBLSPC
BRH_IND_PART_14 2003-05-17 000000 X_BRH_INDE
X BRH_IND_TBLSPC BRH_TAB_PART_15
2003-05-18 000000 X_BRH_INDEX
BRH_IND_TBLSPC BRH_TAB_PART_16 2003-05-19
000000 X_BRH_INDEX BRH_IND_TBLSPC
BRH_TAB_PART_17 2003-05-20 000000 X_BRH_INDE
X BRH_IND_TBLSPC BRH_TAB_PART_18
2003-05-21 000000 X_BRH_INDEX
BRH_IND_TBLSPC BRH_TAB_PART_19 2003-05-22
000000 X_BRH_INDEX BRH_IND_TBLSPC
BRH_TAB_PART_20 2003-05-23
000000 X_BRH_INDEX BRH_IND_TBLSPC
BRH_TAB_PART_21 2003-05-24 000000
59
SQL Used

• For this index
• Partition names progress
• ltindex namegt_15 to 252
• ltindex namegt_8 to 14
• lttable namegt_15 to 21
• When ordered by high_value
• the 15 to 21 are the partition names messed up by
  the vendor script...
• other indexes are ltindex namegt_1 to 252
• table for these indexes has partition names
  running 22-252, 1-21

60
SQL Used
for each index, create table that will contain
the table partition names and the index partition
names create table BRH_INDEX_TI_PART_NAMES (table
_name 2 VARCHAR2(30), tab_tblspc_name
VARCHAR2(25), tab_part_name VARCHAR2(25), tab_high
_value_text VARCHAR2(35), index_name
VARCHAR2(30), ind_tblspc_name VARCHAR2(25), ind_pa
rt_name VARCHAR2(25), ind_high_value_text
VARCHAR2(35), temp_ind_part_name
VARCHAR2(25), final_ind_part_name
VARCHAR2(25)) --gt insert table partition name
info insert into BRH_INDEX_TI_PART_NAMES
(table_name, tab_tblspc_name, tab_part_name,
tab_high_value_text) (select from
BRH_TABLE_part_data) order by tab_high_value_text
--gt insert index partition name info insert into
BRH_INDEX_TI_PART_NAMES (index_name,
ind_tblspc_name, ind_part_name,
ind_high_value_text) (select from
BRH_INDEX_part_data) order by ind_high_value_text
--gt DUH!!! this creates 252 rows of table
partition data followed by 252 rows of index
partition data...
61
SQL Used

• try again
• --gt insert table partition name info
• insert into BRH_INDEX_TI_PART_NAMES (table_name,
  tab_tblspc_name, tab_part_name,
  tab_high_value_text)
• (select from BRH_TABLE_part_data) order by
  tab_high_value_text
• update BRH_INDEX_TI_PART_NAMES a
• set (index_name, ind_tblspc_name, ind_part_name,
  ind_high_value_text)
• (select index_name , ind_tblspc_name,
  ind_part_name, ind_high_value_text
• from BRH_INDEX_part_data b
• where a.tab_high_value_textb.ind_high_value_text)
  

62
SQL Used
Now we can look at table partition names and
index partition names at the same time selected
from BRH_INDEX_TI_PART_NAMES TAB_PART_NAME
TAB_HIGH_VALUE_TEXT IND_PART_NAME
IND_HIGH_VALUE_TEXT ------------------------------
------------------------- ------------------
------- ----------------------------------- BRH_
TAB_PART_22 2002-09-15 000000
BRH_IND_PART_15 2002-09-15 000000 BRH_TAB_PA
RT_23 2002-09-16 000000
BRH_IND_PART_16 2002-09-16 000000 BRH_TAB_PA
RT_24 2002-09-17 000000
BRH_IND_PART_17 2002-09-17 000000 BRH_TAB_PA
RT_25 2002-09-18 000000
BRH_IND_PART_18 2002-09-18 000000 BRH_TAB_PA
RT_26 2002-09-19 000000
BRH_IND_PART_19 2002-09-19 000000 ... BRH_TA
B_PART_5 2003-05-08 000000
BRH_IND_PART_250 2003-05-08 000000 BRH_TAB_PA
RT_7 2003-05-10 000000
BRH_IND_PART_252 2003-05-10 000000 BRH_TAB_PA
RT_9 2003-05-12 000000
BRH_IND_PART_9 2003-05-12 000000 BRH_TAB_PA
RT_11 2003-05-14 000000
BRH_IND_PART_11 2003-05-14 000000 BRH_TAB_PA
RT_13 2003-05-16 000000
BRH_IND_PART_13 2003-05-16 000000 BRH_TAB_PA
RT_15 2003-05-18 000000
BRH_TAB_PART_15 2003-05-18 000000 BRH_TAB_PAR
T_17 2003-05-20 000000
BRH_TAB_PART_17 2003-05-20 000000 ... BRH_TAB
_PART_10 2003-05-13 000000
BRH_IND_PART_10 2003-05-13 000000 BRH_TAB_PA
RT_12 2003-05-15 000000
BRH_IND_PART_12 2003-05-15 000000 BRH_TAB_PA
RT_14 2003-05-17 000000
BRH_IND_PART_14 2003-05-17 000000 BRH_TAB_PA
RT_16 2003-05-19 000000
BRH_TAB_PART_16 2003-05-19 000000 BRH_TAB_PAR
T_18 2003-05-21 000000
BRH_TAB_PART_18 2003-05-21 000000 BRH_TAB_PAR
T_20 2003-05-23 000000
BRH_TAB_PART_20 2003-05-23 000000
63
SQL Used
Note the additional columns TEMP_IND_PART_NAME,
FINAL_IND_PART_NAME these are going to hold the
two new names for each partition some of the
index partitions already have the vendor
specified naming convention, if we simply changed
all existing index partition names to the vendor
convention, we would get errors since, for some
partitions, the partition name is already in
use... update BRH_INDEX_TI_PART_NAMES set
temp_ind_part_nameTRIM(ind_part_name)'_temp'
be careful to order by high_value... SQLgt select
from BRH_INDEX_TI_PART_NAMES order by
tab_high_value_text TABLE_NAME
TAB_TBLSPC_NAME TAB_PART_NAME
TAB_HIGH_VALUE_TEXT INDEX_NAME
IND_TBLSPC_NAME IND_PART_NAME
IND_HIGH_VALUE_TEXT TEMP_IND_PART_NAME FINA
L_IND_PART_NAME ------------------------------
------------------------- ------------------------
- -----------------------------------
------------------------------ -------------------
------ -------------------------
-----------------------------------
------------------------- ------------------------
- BRH_TABLE BRH_TAB_TBLSPC
BRH_TAB_PART_22 2002-09-15 000000
X_BRH_INDEX BRH_IND_TBLSPC
BRH_IND_PART_15 2002-09-15 000000
BRH_IND_PART_15_temp BRH_TABLE
BRH_TAB_TBLSPC BRH_TAB_PART_23 2002-09-16
000000 X_BRH_INDEX
BRH_IND_TBLSPC BRH_IND_PART_16 2002-09-16
000000 BRH_IND_PART_16_temp BRH_TABLE
BRH_TAB_TBLSPC BRH_TAB_PART_24
2002-09-17 000000 X_BRH_INDEX
BRH_IND_TBLSPC BRH_IND_PART_17 2002-09-17
000000 BRH_IND_PART_17_temp BRH_TABLE
BRH_TAB_TBLSPC BRH_TAB_PART_25
2002-09-18 000000 X_BRH_INDEX
BRH_IND_TBLSPC BRH_IND_PART_18 2002-09-18
000000 BRH_IND_PART_18_temp BRH_TABLE
BRH_TAB_TBLSPC BRH_TAB_PART_26
2002-09-19 000000 X_BRH_INDEX
BRH_IND_TBLSPC BRH_IND_PART_19 2002-09-19
000000 BRH_IND_PART_19_temp
64
SQL Used
update BRH_INDEX_TI_PART_NAMES set
temp_ind_part_nameTRIM(ind_part_name)'_temp'
fix index partition names that were messed up by
vendor script update BRH_INDEX_TI_PART_NAMES set
temp_ind_part_name'BRH_INtmp'SUBSTR(temp_ind_pa
rt_name,12,25) where temp_ind_part_name like
'BRH_TAB_PART' example BRH_TAB_PART_15_temp
becomes BRH_INtmp_15_temp --gt start building
final index partition name, start with the table
partition name... this gives us the number for
the final index partition name, i.e. the same as
the table partition name number update
BRH_INDEX_TI_PART_NAMES set final_ind_part_nameta
b_part_name this replaces the table name with
the vendor required spec for the index partition
name update BRH_INDEX_TI_PART_NAMES set
final_ind_part_name'BRH_IND_PART'SUBSTR(final_i
nd_part_name,12,25)
65
SQL Used
Finally, we have all the data we need to
construct the SQL statements to alter all the
index partition names First, rename all the
index partition names to the temporary index
partition names to deal with duplicate
conflicts SQLgt select 'alter index
analysis.'TRIM(index_name)' rename partition
'TRIM(ind_part_name)' TO 'TRIM(temp_ind_part
_name)' from BRH_INDEX_TI_PART_NAMES order by
tab_high_value_text alter index
analysis.X_BRH_INDEX rename partition
BRH_IND_PART_15 TO BRH_IND_PART_15_temp alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_16 TO BRH_IND_PART_16_temp alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_17 TO BRH_IND_PART_17_temp alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_18 TO BRH_IND_PART_18_temp alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_19 TO BRH_IND_PART_19_temp alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_20 TO BRH_IND_PART_20_temp alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_21 TO BRH_IND_PART_21_temp alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_22 TO BRH_IND_PART_22_temp alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_23 TO BRH_IND_PART_23_temp ... alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_13 TO BRH_IND_PART_13_temp alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_14 TO BRH_IND_PART_14_temp alter
index analysis.X_BRH_INDEX rename partition
BRH_TAB_PART_15 TO BRH_INtmp_15_temp alter index
analysis.X_BRH_INDEX rename partition
BRH_TAB_PART_16 TO BRH_INtmp_16_temp alter index
analysis.X_BRH_INDEX rename partition
BRH_TAB_PART_17 TO BRH_INtmp_17_temp alter index
analysis.X_BRH_INDEX rename partition
BRH_TAB_PART_18 TO BRH_INtmp_18_temp alter index
analysis.X_BRH_INDEX rename partition
BRH_TAB_PART_19 TO BRH_INtmp_19_temp alter index
analysis.X_BRH_INDEX rename partition
BRH_TAB_PART_20 TO BRH_INtmp_20_temp alter index
analysis.X_BRH_INDEX rename partition
BRH_TAB_PART_21 TO BRH_INtmp_21_temp
66
SQL Used
Second, rename index partition names to their
final names using the same number(s) as the table
partition numbers select 'alter index
analysis.'TRIM(index_name)' rename partition
'TRIM(temp_ind_part_name)' TO
'TRIM(final_ind_part_name)' from
BRH_INDEX_TI_PART_NAMES order by
tab_high_value_text alter index
analysis.X_BRH_INDEX rename partition
BRH_IND_PART_15_temp TO BRH_IND_PART_22 alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_16_temp TO BRH_IND_PART_23 alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_17_temp TO BRH_IND_PART_24 alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_18_temp TO BRH_IND_PART_25 alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_19_temp TO BRH_IND_PART_26 alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_20_temp TO BRH_IND_PART_27 alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_21_temp TO BRH_IND_PART_28 alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_22_temp TO BRH_IND_PART_29 alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_23_temp TO BRH_IND_PART_30 ... alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_13_temp TO BRH_IND_PART_13 alter
index analysis.X_BRH_INDEX rename partition
BRH_IND_PART_14_temp TO BRH_IND_PART_14 alter
index analysis.X_BRH_INDEX rename partition
BRH_INtmp_15_temp TO BRH_IND_PART_15 alter index
analysis.X_BRH_INDEX rename partition
BRH_INtmp_16_temp TO BRH_IND_PART_16 alter index
analysis.X_BRH_INDEX rename partition
BRH_INtmp_17_temp TO BRH_IND_PART_17 alter index
analysis.X_BRH_INDEX rename partition
BRH_INtmp_18_temp TO BRH_IND_PART_18 alter index
analysis.X_BRH_INDEX rename partition
BRH_INtmp_19_temp TO BRH_IND_PART_19 alter index
analysis.X_BRH_INDEX rename partition
BRH_INtmp_20_temp TO BRH_IND_PART_20 alter index
analysis.X_BRH_INDEX rename partition
BRH_INtmp_21_temp TO BRH_IND_PART_21
67
SQL Used
the SQL is spooled to separate files and executed
against the db spool BRH_INDEX_TI_PART_NAMES_tmp_
SQL.txt select 'alter index analysis.'TRIM(index
_name)' rename partition 'TRIM(ind_part_name)
' TO 'TRIM(temp_ind_part_name)'' from
BRH_INDEX_TI_PART_NAMES order by
tab_high_value_text spool off spool
BRH_INDEX_TI_PART_NAMES_final_SQL.txt select
'alter index analysis.'TRIM(index_name)'
rename partition 'TRIM(temp_ind_part_name)'
TO 'TRIM(final_ind_part_name)'' from
BRH_INDEX_TI_PART_NAMES order by
tab_high_value_text spool off --gt repeat for
the other 10 indexes (11 indexes total)
68
Script Testing

• Use development environment
• Subset of production data
• Same number of daily partitions as production
• Rename existing index partitions

69
Result?

• Scripts ran without error
• Everyone is happy
• Prepared to do it all again if needed
• No need for cut/paste
• Demonstrated method to handle high_value
• LONG datatype work-around

70
Performance Issues

• After all this
• Are you sure you are getting the benefits?
• Explain plan shows partitions accessed
• Partition Pruning
• Optimizer can tell which partitions arent
  involved in completing the query
• Need to check
• Partition pruning is happening
• Where clause can prevent this
• User generated SQL is problematic

71
Explain Plan -- Good
explain plan set Statement_Id 'TEST' for CREATE
TABLE BRHPSYCHO1 NOLOGGING AS SELECT
t161.brh_col1 AS col0, t168.brh_col2 AS col1,
t172.brh_col2 AS col2, t176.brh_col2 AS
col3, COUNT(DISTINCT t161.brh_col3) AS
countcol FROM brh_table1 t161, brh_table2 t168,
brh_table2 t172, brh_table2 t176, brh_table2
t185 WHERE t172.brh_col4 t176.brh_col3 AND
t168.brh_col4 t172.brh_col3 AND t161.brh_col5
t185.brh_col3 AND t185.brh_col4 t168.brh_col3
AND t161.brh_col6 gt TO_DATE('2001/08/08
000000','YYYY/MM/DD HH24MISS')
AND t161.brh_col6 lt TO_DATE('2001/08/08235959',
'YYYY/MM/DDHH24MISS') AND t185.brh_col7 gt
TO_DATE('2001/08/08 000000','YYYY/MM/DD
HH24MISS') AND t185.brh_col7 lt
TO_DATE('2001/08/08 235959','YYYY/MM/DDHH24MIS
S') AND t168.brh_col7 gt TO_DATE('2001/08/08
000000','YYYY/MM/DD HH24MISS')
AND t168.brh_col7 lt TO_DATE('2001/08/08
235959','YYYY/MM/DD HH24MISS')
AND t172.brh_col7 gt TO_DATE('2001/08/08
000000','YYYY/MM/DD HH24MISS')
AND t172.brh_col7 lt TO_DATE('2001/08/08
235959','YYYY/MM/DD HH24MISS')
AND t176.brh_col7 gt TO_DATE('2001/08/08
000000','YYYY/MM/DD HH24MISS')
AND t176.brh_col7 lt TO_DATE('2001/08/08
235959','YYYY/MM/DDHH24MISS') GROUP BY
t161.brh_col1, t168.brh_col2, t172.brh_col2,
t176.brh_col2
72
Explain Plan -- Good
--------------------------------------------------
------------------------------ Operation
Name Rows Bytes Cost Pstart
Pstop -------------------------------------------
------------------------------------- CREATE
TABLE STATEMENT 4 448
67 LOAD AS SELECT

SORT GROUP BY 4 448
67 NESTED LOOPS
4 448 55
NESTED LOOPS 4 372
43 NESTED LOOPS
4 272 31
NESTED LOOPS 4 172
19 TABLE ACCESS BY
LOCbrh_table1 4 92 7 1
1 INDEX RANGE SCAN brh_ind1 4
3 1 1
-------------------------------------------------
------------------------------- TABLE ACCESS
BY LOCbrh_table2 28 560 3 2
2 INDEX RANGE SCAN brh_ind2
28 2 2 2 TABLE
ACCESS BY LOCAbrh_table2 28 700 3
2 2 INDEX RANGE SCAN
brh_ind2 28 2 2
2 TABLE ACCESS BY LOC brh_table2
28 700 3 2 2 INDEX
RANGE SCAN brh_ind2 28 2
2 2 TABLE ACCESS BY LOCAL
brh_table2 28 532 3 2
2 INDEX RANGE SCAN brh_ind2
28 2 2 2
-------------------------------------------------
------------------------------- ORACLE_HOME/rdbm
s/admin/utlxpls (use correct version!)
73
Explain Plan -- Bad
explain plan set Statement_Id 'TEST' for CREATE
TABLE BRHPSYCHO1 NOLOGGING AS SELECT
t161.brh_col1 AS col0, t168.brh_col2 AS col1,
t172.brh_col2 AS col2, t176.brh_col2 AS
col3, COUNT(DISTINCT t161.brh_col3) AS
countcol FROM brh_table1 t161, brh_table2 t168,
brh_table2 t172, brh_table2 t176, brh_table2
t185 WHERE t172.brh_col4 t176.brh_col3 AND
t168.brh_col4 t172.brh_col3 AND t161.brh_col5
t185.brh_col3 AND t185.brh_col4 t168.brh_col3
AND t161.brh_col6 gt TO_DATE('2001/08/08
000000','YYYY/MM/DD HH24MISS')
AND t161.brh_col6 lt TO_DATE('2001/08/08
235959','YYYY/MM/DD HH24MISS')
AND t185.brh_col7 gt TO_DATE('2001/08/08
000000','YYYY/MM/DD HH24MISS') OR
t185.brh_col30 AND t185.brh_col7 lt
TO_DATE('2001/08/08 235959','YYYY/MM/DD
HH24MISS') OR t185.brh_col30 AND t168.brh_col7
gt TO_DATE('2001/08/08 000000','YYYY/MM/DD
HH24MISS') OR t168.brh_col30 AND t168.brh_col7
lt TO_DATE('2001/08/08 235959','YYYY/MM/DD
HH24MISS') OR t168.brh_col30 AND t172.brh_col7
gt TO_DATE('2001/08/08 000000','YYYY/MM/DD
HH24MISS') OR t172.brh_col30 AND t172.brh_col7
lt TO_DATE('2001/08/08 235959','YYYY/MM/DD
HH24MISS') OR t172.brh_col30 AND t176.brh_col7
gt TO_DATE('2001/08/08 000000','YYYY/MM/DD
HH24MISS') AND t176.brh_col7 lt
TO_DATE('2001/08/08 235959','YYYY/MM/DDHH24MIS
S') GROUP BY t161.brh_col1, t168.brh_col2,
t172.brh_col2, t176.brh_col2
74
Explain Plan -- Bad
Plan Table ---------------------------------------
-----------------------------------------
Operation Name Rows Bytes
Cost Pstart Pstop -------------------------
--------------------------------------------------
----- CREATE TABLE STATEMENT
425432244376070970810
LOAD AS SELECT
SORT GROUP BY
4254322443760
SORT GROUP BY 4254322443760
SORT GROUP BY
4254322443760
CONCATENATION
MERGE JOIN
CARTESIAN 4 172 83
MERGE JOIN CARTESIA 4
272 167 MERGE JOIN
CARTESI 4 92
Plan Table -----------------------------
--------------------------------------------------
- MERGE JOIN CARTES 4 448
335 PARTITION RANGE
1 10
TABLE ACCESS BYbrh_table2 4M
115M 21 1 10 INDEX
RANGE SCbrh_ind2 4M 20
1 10 SORT JOIN
4M 87M TABLE
ACCESS BYbrh_table2 4M 87M 21
2 2 INDEX RANGE SCbrh_ind3
4M 20 2 2 SORT
JOIN 4 92
PARTITION RANGE
1 9 TABLE ACCESS
FUbrh_table1 4 92 7 1
9 SORT JOIN 4M
115M
75
Explain Plan -- Bad
Plan Table ---------------------------------------
-----------------------------------------
PARTITION RANGE A
1 10 TABLE ACCESS
FULbrh_table2 4M 115M 21 1
10 SORT JOIN 4M
92M PARTITION
RANGE AL 1
10 TABLE ACCESS FULLbrh_table2
4M 92M 19 1 10 MERGE JOIN
CARTESIAN 4 172 83
MERGE JOIN CARTESIA 4
272 167 NESTED
LOOPS 4 448 335
MERGE JOIN CARTES 4
92 PARTITION
RANGE 1
2 TABLE ACCESS BYbrh_table2
4M 115M 21 1 2 Plan Table
--------------------------------------------------
------------------------------ INDEX
RANGE SCbrh_ind2 4M 20
1 2 SORT JOIN 4
92 PARTITION
RANGE 1
9 TABLE ACCESS Fbrh_table1 4
92 7 1 9 PARTITION
RANGE A 1
10 TABLE ACCESS FULbrh_table2
4M 87M 21 1 10 SORT
JOIN 4M 115M
PARTITION RANGE A
1 10 TABLE
ACCESS FULbrh_table2 4M 115M 21
1 10 SORT JOIN
4M 92M
PARTITION RANGE AL
1 10
76
Explain Plan -- Bad
Plan Table ---------------------------------------
-----------------------------------------
TABLE ACCESS FULLbrh_table2 4M 92M
19 1 10 NESTED LOOPS
4 372 251 MERGE
JOIN CARTESIA 4 172 83
MERGE JOIN CARTESI
4 448 335 MERGE
JOIN CARTES 4 92
PARTITION RANGE
1 10 TABLE
ACCESS BYbrh_table2 4M 115M 21
1 10 INDEX RANGE SCbrh_ind2
4M 20 1 10 SORT
JOIN 4 92
PARTITION RANGE
1 9 TABLE
ACCESS Fbrh_table1 4 92 7
1 9 Plan Table -------------------------
--------------------------------------------------
----- SORT JOIN 4M 87M
PARTITION RANGE
1 10
TABLE ACCESS FUbrh_table2 4M 87M
21 1 10 SORT JOIN
4M 92M
PARTITION RANGE A
1 10 TABLE ACCESS
FULbrh_table2 4M 92M 19 1
10 PARTITION RANGE ITE
2 10 TABLE ACCESS
FULL brh_table2 4M 115M 21 2
10 MERGE JOIN CARTESIAN 4
172 83 NESTED
LOOPS 4 448 335
NESTED LOOPS 4 372
251
77
Explain Plan -- Bad
Plan Table ---------------------------------------
-----------------------------------------
MERGE JOIN CARTES 4 92
PARTITION RANGE
1 2
TABLE ACCESS BYbrh_table2 4M 115M 21
1 2 INDEX RANGE
SCbrh_ind2 4M 20 1
2 SORT JOIN 4 92
PARTITION RANGE
1 9
TABLE ACCESS Fbrh_table1 4 92
7 1 9 PARTITION RANGE A
1 10
TABLE ACCESS FULbrh_table2 4M 115M
21 1 10 PARTITION RANGE AL
1 10
TABLE ACCESS FULLbrh_table2 4M 87M
21 1 10 Plan Table
--------------------------------------------------
------------------------------ SORT JOIN
4M 92M
PARTITION RANGE AL
1 10 TABLE ACCESS
FULLbrh_table2 4M 92M 19 1
10 NESTED LOOPS 4 272
167 NESTED LOOP
4 448 335 MERGE
JOIN CARTESI 4 372 251
MERGE JOIN CARTES
4 92
PARTITION RANGE
1 10 TABLE ACCESS
BYbrh_table2 4M 92M 19 1
10 INDEX RANGE SCbrh_ind2 4M
18 1 10 SORT JOIN
4 92
78
Explain Plan -- Bad
Plan Table ---------------------------------------
-----------------------------------------
PARTITION RANGE
1 9 TABLE ACCESS
Fbrh_table1 4 92 7 1
9 SORT JOIN 4M 115M
PARTITION RANGE
1 10
TABLE ACCESS FUbrh_table2 4M 115M
21 1 10 PARTITION RANGE AL
1 10
TABLE ACCESS FULLbrh_table2 4M 87M
21 1 10 PARTITION RANGE ITE
2 10
TABLE ACCESS FULL brh_table2 4M 115M
21 2 10 NESTED LOOPS
4 448 335 NESTED
LOOP 4 372 251
Plan Table --------------------------------
------------------------------------------------
NESTED LOOPS 4 272 167
MERGE JOIN CARTES
4 92
PARTITION RANGE
1 2 TABLE ACCESS
BYbrh_table2 4M 92M 19 1
2 INDEX RANGE SCbrh_ind2 4M
18 1 2 SORT JOIN
4 92
PARTITION RANGE
1 9 TABLE ACCESS
Fbrh_table1 4 92 7 1
9 PARTITION RANGE A
1 10 TABLE ACCESS
FULbrh_table2 4M 115M 21 1
10