| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-02 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-02 |
|
|
View PDF |
The CHGDFN command enables you to change certain aspects of the definitions of analytic workspace objects.
Before you can use CHGDFN to change the definition of an object, use CONSIDER to make that object definition the current definition.
Note:
You cannot use CHGDFN to change definitions of objects that are in an analytic workspace that is attached in multiwriter mode.Syntax
CHGDFN desired-change
where desired-change is one of the following:
varname SEGWIDTH length dim...
partitioned-varname {DROP | ADD } (partition-instance...)
partition-template {DEFINE | DELETE [CLEAR] } (partition-instance...)
partition-template RENAME PARTITION old-name new-name
{conjoint | composite} {HASH | BTREE | NOHASH}
concat BASE ADD dimensionlist
conjoint COMPOSITE
composite DIMENSION
dwmqy-dimname { {BEGINNING | ENDING} phase | {EARLIER | LATER} n}
concat [NOT] UNIQUE
varname {ADD | REMOVE} AGGCOUNT
Arguments
The name of the variable whose segment size you want to set.
Indicates explicit sizing of a variable's segments. See "Understanding Variable Segments" for more information.
Specifies the name of a partitioned variable whose partitions you want to modify.
Removes or adds the specified partitions from the partitioned variable. See DEFINE VARIABLE for a complete description of the partition-instance argument.
Removes or adds the specified partitions from the partition template object. See DEFINE PARTITION TEMPLATE for a complete description of the partition-instance argument.
When you include the optional CLEAR keyword, Oracle OLAP also drops any corresponding partitions in the variables that are partitioned using the partition template object. In other words, including CLEAR is the same as issuing an additional CHGDFN statements to DROP the partition from the variables partitoned by it.
Renames the specified partitions in the partition template object.
Adds the dimension or dimensions specified by dimensionlist to the base dimensions of the concat dimension.When you add one or more dimensions as base dimensions of a concat, then Oracle OLAP appends the dimensions to the existing list of base dimensions of the concat. Objects that are dimensioned by the concat, or objects that are dimensioned by a concat that has the altered concat as a base dimension, gain additional NA values. You cannot add as a base dimension a dimension that is already a component of the concat dimension.
Segment width is specified as the maximum number of values in each segment for each dimension or composite in the variable's dimension list. The first length-dim is the number of values for the dimension or composite in the first position of the dimension list in the variable's definition (that is, the fastest-varying dimension or composite), the second length-dim is the number of values for the dimension or composite in the second position in the dimension list, and so on.
For the index syntax, the name of the conjoint dimension or composite whose index algorithm you want to change. For the conjoint-to-composite syntax, the name of the conjoint dimension you want to change to a composite. For the composite-to-dim syntax, the name of the composite you want to change to a conjoint dimension. You cannot change a conjoint dimension to a composite when the conjoint is a dimension of a formula.
Indicates the index algorithm used to load and access values of your conjoint dimension or composite without losing data in objects defined with the conjoint or composite. A composite cannot be changed to NOHASH. A conjoint can be changed to NOHASH only when it was originally defined as HASH. See "Changing the Index Algorithm of a Conjoint from BTREE to NOHASH".
HASH, NOHASH, and BTREE are different index algorithms used to load and access the values of a conjoint dimension or composite. (BTREE64 can only be used with composites.) HASH is the default for conjoints. The default for composites is determined by the SPARSEINDEX option, which has a default value of BTREE. The index algorithm affects the performance of loading and accessing large conjoints or composites. Performance varies depending on your machine configuration, the organization of your data, and the design of your application.
BTREE is a standard indexing method that is recommended for composites and conjoint dimensions. Use BTREE as the default unless you are an advanced user and have a special need that requires HASH or NOHASH. BTREE tends to group similar values together, which results in better locality of access.
BTREE 64 can only be used with composites. It specifies the creation of a highly-scalable b-tree index to relate composite values to base dimension values. For a variable that is dimensioned by a BTREE64 composite, like a BTREE composite, Oracle OLAP creates array elements (that is, variable cells) only for those dimension values that are stored in the tuples of the composite; it does not create a cell for every value in the base dimensions. However, unlike a BTREE composite, a BTREE64 composite supports b-trees greater than 2 gigabytes
HASH is a standard indexing method that can be used for composites or conjoint dimensions that have only 2 or 3 base dimensions. One advantage to using HASH is that it results in a small amount of code. However, HASH is generally not recommended. Using HASH results in a very large index table, which can be too large to fit into memory.
NOHASH can only be used with conjoint dimensions. It can be advantageous to use NOHASH when there is little memory available and the conjoint dimension has only 2 or 3 base dimensions.Also, you can use NOHASH when you load a very large initial amount of data. When you use NOHASH, the data will be loaded in a way that makes it easy to access that data after it has been loaded. Once the data is loaded, change the definition of the conjoint dimension back to BTREE to ensure good performance. Otherwise, performance is likely to suffer, especially when the conjoint dimension has 4 or more base dimensions. See "Changing the Index Algorithm of a Conjoint from BTREE to NOHASH".
Tip:
You can do performance testing to determine which algorithm provides the best performance for your situation. For example, suppose a data load executes well at first, then slows down drastically. Use CHGDFN to change the index algorithm from BTREE to NOHASH. Try the data load again to determine whether or not using NOHASH improves performance. You can then use CHGDFN to change the index algorithm back to BTREE. Note, however, that changing the index algorithm of a large conjoint dimension or composite from one algorithm type to another may take a considerable amount of time and that the CHGDFN command cannot be interrupted.Indicates changing a conjoint dimension into a named composite. There are some restrictions on changing conjoint dimensions to composites; when a conjoint has the NOHASH index algorithm or when it has permissions, you cannot change it to a composite.
Indicates changing a named composite into a conjoint dimension.
Specifies or changes the phase of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR.
Specifies the beginning phase or ending phase of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. You must specify the phase as a date, giving the month, day, and year, enclosed in single quotes, using any of the input styles that are valid for variable values with a data type of DATE. When you specify a date with an ambiguous meaning (such as '03 05 97'), the date is interpreted according to the current setting of the DATEORDER option. For more information about specifying dates, see DATEORDER.
n is an INTEGER expression that increments or decrements the period on which the DAY, WEEK, MONTH, QUARTER, or YEAR dimension's phase begins or ends. For example, for a WEEK dimension whose current begin phase is Monday, specify LATER 2 to change the phase to Wednesday.
When you include NOT, changes a unique concat dimension to a nonunique concat. When you do not include NOT, changes a nonunique concat dimension to a unique concat dimension. See DEFINE DIMENSION CONCAT for more information on concat dimensions.
Adds an Aggcount variable to the specified variable. See the WITH AGGCOUNT phrase of the DEFINE VARIABLE statement for more information about Aggcount variables.
Removes an Aggcount variable from the specified variable. See the WITH AGGCOUNT phrase of the DEFINE VARIABLE statement for more information about Aggcount variables.
Notes
Understanding Variable Segments
A segment is contiguous disk space reserved for a portion of the total number of values a variable holds. For example, for a variable dimensioned by month with a SEGWIDTH of 150 and product with a SEGWIDTH of 90,000, each segment will hold up to 150 x 90,000 values of the variable. The number of segments in a variable affects the performance of data loading and data accessing.
When you do not specify CHGDFN SEGWIDTH, the default behavior is to assign a segwidth of 0 (zero) on non-composite dimensions and a large value for composites that are not the slowest-varying in the dimension set. This allows new dimension and composite values to be added in most situations without greatly increasing the number of segments and degrading performance.
When you specify SEGWIDTH, you must specify a number, 0 (zero), or nonzero, for every dimension and composite of the variable.
When you set the value of SEGWIDTH for a dimension to 0, Oracle OLAP will grow segments in that dimension as needed, minimizing the number of segments but not changing any existing segments. You can always specify 0 (zero for the slowest-varying dimension, because the data for any values that are later added to that dimension will be appended to the existing data in the variable's last segment.
The segment size that you specify is used not only for the variable you designate as varname, but also for all other variables and relations that are defined with the same combination of dimensions and composites in the same order. The DEFINE command sets the SEGWIDTH at the time it creates a variable or relation. Changing the SEGWIDTH affects any new variable or relation that you subsequently create. The changed SEGWIDTH setting does not apply to previously existing variables or relations.
The time it takes to do data loads on a variable depends on how many pages are brought into memory and then written back out. This number can be affected by how a variable is divided into segments. Too many segments (thousands to millions) can degrade performance. See "Reducing the Number of Segments".
The number of segments also affects data access. The time it takes to report a variable depends on how many values are brought into memory. You decide how many segments your variable should have based on your data loading and data accessing patterns.
DEFINE provides default segments. In most cases, you can use the default segments so that you do not have to use CHGDFN SEGWIDTH to manually control the size of segments. However, you may be able to improve performance by specifying the segment size instead of using the defaults.When you are not sure what your segment size should be, use the maximum anticipated number of values for each dimension or composite as the length arguments to SEGWIDTH. Then only one segment will be created for the variable.
Reducing the Number of Segments
You can use OBJ (NUMSEGS) to find out if you have too many segments for objects that have a particular dimension set. When you find that you do, you can reduce the number of segments by following these steps:
Export the variables and relations that use this dimension set to an EIF file.
Execute a MAINTAIN DELETE ALL statement for one of the dimensions in the dimension set.
Optimally, execute a CHGDFN statement for one of the variables or relations with this dimension set, and increase the value of the length arguments to the SEGWIDTH keyword.
From the EIF file, import all the values you exported in Step 1.
Changing the Index Algorithm of a Conjoint from BTREE to NOHASH
When you need to change a conjoint dimension that was originally defined with the BTREE algorithm to a NOHASH conjoint, you can use the following method:
Export the conjoint dimension and all the objects dimensioned by it to an EIF file.
Delete all the objects dimensioned by the conjoint dimension, and then delete the conjoint itself.
Redefine the conjoint as a NOHASH conjoint.
Import the conjoint dimension and the objects dimensioned by it from the EIF file. The NOHASH attribute on the definition at the time of the import will cause the conjoint dimension to be read in as a NOHASH conjoint.
Changing an Unnamed Composite to a Named Conjoint Dimension
When you want to change an unnamed composite into a conjoint dimension, you can use a RENAME statement to change the unnamed composite into a named composite, and then use CHGDFN to change the named composite into a conjoint dimension.
Examples
Example 11-1 Using CHGDFN SEGWIDTH
Suppose you have a variable called d.sales that is dimensioned by month and by a composite with the base dimensions market and product. The definition of d.sales looks like the following.
DEFINE d.sales VARIABLE DECIMAL <month SPARSE<market product>>
Suppose you want to have only one segment in the d.sales variable. You estimate that the month dimension will eventually have 150 values and the composite will have 100,000. The following statement will create one segment for the d.sales variable.
CHGDFN d.sales SEGWIDTH 150 100000
However, a better way to specify segment size for d.sales is to specify 0 for the slowest-varying dimension.
CHGDFN d.sales SEGWIDTH 150 0
Suppose you want one segment for a variable defined with a composite and two dimensions. For example, suppose you have a variable called f.costs with the following definition.
DEFINE f.costs VARIABLE DECIMAL <geog SPARSE<product channel> time>
You estimate the geog dimension will have 100 values and the composite will have 300,000. You do not have to estimate the number of values for the time dimension, because it is the slowest-varying dimension. The following statement will create one segment for the f.costs variable.
CHGDFN f.costs SEGWIDTH 100 300000 0
Example 11-2 Adding an External Partition to a Variable
Assume that your analytic workspace has a sales variable with two external partitions—one partition for sales in 2002 and another partition for sales in 2003. The following definitions are used to define the sales variable.
DEFINE YEAR_2003 DIMENSION TEXT
DEFINE YEAR_2002 DIMENSION TEXT
DEFINE PRODUCT DIMENSION TEXT
DEFINE SALES_2003 VARIABLE DECIMAL <YEAR_2003 PRODUCT>
DEFINE SALES_2002 VARIABLE DECIMAL <YEAR_2002 PRODUCT>
DEFINE TIME DIMENSION CONCAT (YEAR_2003 YEAR_2002 YEAR_2004) UNIQUE
DEFINE PART_TEMP_SALES_BY_YEAR PARTITION TEMPLATE <TIME PRODUCT> -
PARTITION BY CONCAT (TIME) -
(PARTITION PARTITION_2002 <YEAR_2002 PRODUCT> -
PARTITION PARTITION_2003 <YEAR_2003 PRODUCT>)
DEFINE SALES VARIABLE DECIMAL <PART_TEMP_SALES_BY_YEAR <TIME PRODUCT>> -
(PARTITION PARTITION_2002 EXTERNAL SALES_2002 -
PARTITION PARTITION_2003 EXTERNAL SALES_2003)
Assume that you want to add data for the year 2004 to sales. Before you can add the data, you need to add an external partition to sales to hold data. To add an external partition to sales, you take the following steps:
Issue the following DEFINE statements to add a definitions for a dimension for the values for 2004 and a sales variable to hold 2004 data.
DEFINE YEAR_2004 DIMENSION TEXT DEFINE SALES_2004 VARIABLE DECIMAL <YEAR_2004 PRODUCT>
Issue the following CHGDFN statements to add the year_2004 dimension to the time dimension, a partition for 2004 to the partition template used by sales and to the sales variable, itself.
CHGDFN time BASE ADD year_2004 CHGDFN part_temp_sales_by_year - DEFINE(PARTITION partition_2004 <year_2004 product>) CHGDFN sales ADD (PARTITION partition_2004 EXTERNAL sales_2004)
Now time, part_temp_sales_by_year, and sales have the following definitions.
DEFINE TIME DIMENSION CONCAT (YEAR_2003 YEAR_2002 YEAR_2004) UNIQUE
DEFINE PART_TEMP_SALES_BY_YEAR PARTITION TEMPLATE <TIME PRODUCT> -
PARTITION BY CONCAT (TIME) -
(PARTITION PARTITION_2002 <YEAR_2002 PRODUCT> -
PARTITION PARTITION_2003 <YEAR_2003 PRODUCT> -
PARTITION PARTITION_2004 <YEAR_2004 PRODUCT>)
DEFINE SALES VARIABLE DECIMAL <PART_TEMP_SALES_BY_YEAR <TIME PRODUCT>> -
(PARTITION PARTITION_2002 EXTERNAL SALES_2002 -
PARTITION PARTITION_2003 EXTERNAL SALES_2003 -
PARTITION PARTITION_2004 EXTERNAL SALES_2004)
After you populate the year_2004 dimension, you issue the following REPORT statement. You can see that the sales variable has a partition for 2004 data.
REPORT DOWN PARTITION(part_temp_sales_by_year) time product sales PARTITION(PART_TEMP_SALES_BY_YEAR) TIME PRODUCT SALES ----------------------------------- ---------- ---------- ---------- PARTITION_2002 01Jan2002 00001 14.44 ... PARTITION_2003 01Jan2003 00001 10.00 ... PARTITION_2004 01Jan2004 00001 NA ... PARTITION_2004 Jan2004 00001 NA ... PARTITION_2004 2004 00001 NA PARTITION_2004 01Jan2004 00002 NA ... PARTITION_2004 2004 00002 NA
Example 11-3 Changing the Phase of a YEAR Dimension
The following statements first create a dimension of type YEAR for a fiscal year, then use CHGDFN to switch to a new time phase for the fiscal year.
DEFINE fiscal DIMENSION year BEGINNING '06 01 96' CHGDFN fiscal BEGINNING '01 01 97'
Example 11-4 Adding a Base Dimension to a Concat Dimension
The following statements create a nonunique concat dimension named reg.dist.ccdim that has the region and district dimensions as its base dimensions and report the values of the concat.
DEFINE reg.dist.ccdim DIMENSION CONCAT(region district) REPORT W 22 reg.dist.ccdim
The preceding statement produces the following output.
REG.DIST.CCDIM -------------------- <region: East> <region: Central> <region: West> <district: Boston> <district: Atlanta> <district: Chicago> <district: Dallas> <district: Denver> <district: Seattle>
The following statements add the store_id dimension as a base to the concat dimension and then report the values of the concat again.
CHGDFN reg.dist.ccd BASE ADD store_id REPORT W 22 reg.dist.ccd
The preceding statement produces the following output.
REG.DIST.CCD ---------------------- <region: East> <region: Central> <region: West> <district: Boston> ... <district: Seattle> <store_id: 10> <store_id: 20> <store_id: 30> <store_id: 100> ... <store_id: 500> <store_id: 510>
