Partitioning Guidelines For Large Fact Tables
3-30
Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users
WHERE UPI.TABLE_NAME = 'getTableName()'
AND UIP.INDEX_NAME=UPI.INDEX_NAME
AND UIP.STATUS = 'USABLE'
AND UIP.PARTITION_NAME IN
('PART_@DAC_$$CURRENT_YEAR_WID','PART_@DAC_$$PREVIOUS_YEAR_WID');
BEGIN
FOR REC IN C1 LOOP
DBMS_STATS.GATHER_TABLE_STATS(
NULL,
TABNAME => 'getTableName()',
CASCADE => TRUE
PARTNAME => REC.PARTITION_NAME
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
GRANULARITY => 'PARTITION',
METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
DEGREE => DBMS_STATS.DEFAULT_DEGREE);
END LOOP;
END
Note:
Do not use a semicolon (;) at the end of the SQL in the text area.
d.
Click Save.
3.8.3.3.2
How to Create a Table Action to Gather Statistics on a Partitioned Table for Composite
Partitioning
If you use quarterly or monthly partition ranges, substitute the correct
name for the action and in the SQL.
Note:
Do not change the Drop and Create Always or Drop and Create Always Bitmap
properties for the modified indexes. If you deselect these check boxes, DAC will skip
the index actions that are defined.
1.
Log into DAC.
For instructions on logging into DAC, see
Section A.1, "How to Log into DAC."
2.
Go to the Design view, and select the appropriate custom container from the
drop-down list.
3.
From the Menu bar, select Tools, then Seed Data, then Actions, then Table Actions.
4.
In the Table Actions dialog box, click New.
A new record field appears at the top of the list of actions.
5.
In the name field, enter "Quarter Composite Partitioning: Gather Partition Stats."
6.
Click Save.
7.
Double-click in the Value field to open the Value dialog box.
The Value dialog box appears.
8.
Define the SQL script:
a.
Click Add.
A new record field appears at the top of the list of SQL blocks.
b.
Enter the following information in the new record field:
Field
Description
Name
Enter: Gather Partition Stats
Type
Select: Stored Procedure