CHGDFN

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

dimension NTEXT | TEXT | NUMBER [p, s]

     dwmqy-dimname { {BEGINNING | ENDING} phase | {EARLIER | LATER} n}

     concat [NOT] UNIQUE

varname {ADD |DROP} AGGCOUNT

varname [DROP] NULLTRACKING

Parameters

varname

The name of the variable whose segment size you want to set.

SEGWIDTH

Indicates explicit sizing of a variable's segments. See "Understanding Variable Segments" for more information.

partitioned-varname

Specifies the name of a partitioned variable whose partitions you want to modify.

DROP partition-instance

ADD partition-instance

Removes or adds the specified partitions from the partitioned variable. See the DEFINE VARIABLE command for a complete description of the partition-instance argument.

DEFINE partition

DELETE [CLEAR] partition-instance

Removes or adds the specified partitions from the partition template object. See the DEFINE PARTITION TEMPLATE command 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 partitioned by it.

RENAME PARTITION old-name new-name

Renames the specified partitions in the partition template object.

BASE ADD dimensionlist

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.

length-dim...

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.

conjoint

composite

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.

BTREE

BTREE64

HASH

NOHASH

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 system 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, 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 is 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.

COMPOSITE

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.

DIMENSION

Indicates changing a named composite into a conjoint dimension.

composite_dimension

The name of a composite that has a composite as a base dimension.

Note:

In Oracle Database 11g, you cannot define a nested composite. Consequently, you only use the UNNEST keyword with nested composites that were defined in an earlier release and then imported into Oracle Database 11g.

dimension

The name of a TEXT, NTEXT or NUMBER dimension

NTEXT

Specifies that the statement changes the data type of a TEXT dimension to NTEXT

TEXT

Specifies that the statement changes the data type of a NTEXT dimension to TEXT

NUMBER [p, s]

Specifies that the statement changes the data type of a TEXT, NTEXT, or NUMBER dimension to NUMBER with the precision specified by p and the scale specified by s.

dwmqy-dimname

Specifies or changes the phase of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR.

BEGINNING phase

ENDING phase

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 the DATEORDER option.

EARLIER n

LATER n

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.

[NOT] UNIQUE

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 the DEFINE DIMENSION CONCAT command for more information on concat dimensions.

ADD AGGOUNT

Adds an Aggcount variable to the specified variable. See the WITH AGGCOUNT phrase of the DEFINE VARIABLE command for more information about Aggcount variables.

DROP AGGOUNT

Removes an Aggcount variable from the specified variable. See the WITH AGGCOUNT phrase of the DEFINE VARIABLE command for more information about Aggcount variables.

NULLTRACKING

Adds NA2 bits to the specified variable if the variable does not have NA2 bits. For more information on NA2 bits and null tracking, see "NA2 Bits and Null Tracking" .

DROP NULLTRACKING

Removes NA2 bits to the specified variable if the variable has NA2 bits.

Usage 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 holds 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 behavior 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 grows 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 is 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 is 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:

1. Export the variables and relations that use this dimension set to an EIF file.

2. Execute a MAINTAIN DELETE ALL statement for a dimension in the dimension set.

3. Optimally, execute a CHGDFN statement for a variable or relation with this dimension set, and increase the value of the length arguments to the SEGWIDTH keyword.

4. 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 must change a conjoint dimension that was originally defined with the BTREE algorithm to a NOHASH conjoint, you can use the following method:

1. Export the conjoint dimension and all the objects dimensioned by it to an EIF file.

2. Delete all the objects dimensioned by the conjoint dimension, and then delete the conjoint itself.

3. Redefine the conjoint as a NOHASH conjoint.

4. 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 causes 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

For an example of removing null tracking from a variable, see Example 9-104, "Defining a Variable with Null Tracking".

DEFINE d.sales VARIABLE DECIMAL <month SPARSE<market product>>

CHGDFN d.sales SEGWIDTH 150 100000

CHGDFN d.sales SEGWIDTH 150 0

DEFINE f.costs VARIABLE DECIMAL <geog SPARSE<product channel> time>

CHGDFN f.costs SEGWIDTH 100 300000 0

DEFINE fiscal DIMENSION year BEGINNING '06 01 96'
CHGDFN fiscal BEGINNING '01 01 97'

DEFINE reg.dist.ccdim DIMENSION CONCAT(region district)
REPORT W 22 reg.dist.ccdim

REG.DIST.CCDIM
--------------------
<region: East>
<region: Central>
<region: West>
<district: Boston>
<district: Atlanta>
<district: Chicago>
<district: Dallas>
<district: Denver>
<district: Seattle>

CHGDFN reg.dist.ccd BASE ADD store_id
REPORT W 22 reg.dist.ccd

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>