Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

E17122-08
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

PARTITION

The PARTITION function returns the name of the partition in which a value is stored.

Return Value

Text

Syntax

PARTITION (partition_template_values)

Parameters

partition_template_values

An expression that represents one or more values of the partition template (for example, the name of a partition template or a QDR).

Examples

Example 8-60 Retrieving the Name of a Partition

Assume that you have defined the following objects.

DEFINE time DIMENSION TEXT
DEFINE product DIMENSION TEXT
DEFINE time_parentrel RELATION time <time>
DEFINE partition_sales_by_year PARTITION TEMPLATE <time product> -
PARTITION BY LIST (time)(PARTITION time_2003 VALUES -
('2003','Dec2003', 'Jan2003','31Dec2003','01Dec2003','31Jan2003','01Jan2003')-
<time product> PARTITION time_2002 VALUES -
('2002', 'Dec2002', 'Jan2002', '31Dec2002', '01Dec2002','31Jan2002','01Jan2002')-
 <time product>)
DEFINE sales DECIMAL <partition_sales_by_year<time product>>

Assume that these object have the values shown in the following report.

REPORT DOWN PARTITION(partition_sales_by_year) time product sales
 
PARTITION(PART
ITION_SALES_BY
_YEAR)            TIME     PRODUCT     SALES
-------------- ---------- ---------- ----------
TIME_2003      2003       00001              NA
TIME_2003      Dec2003    00001              NA
TIME_2003      Jan2003    00001              NA
TIME_2003      31Dec2003  00001           14.78
TIME_2003      01Dec2003  00001           15.52
TIME_2003      31Jan2003  00001           13.61
TIME_2003      01Jan2003  00001           10.39
TIME_2003      2003       00002              NA
TIME_2003      Dec2003    00002              NA
TIME_2003      Jan2003    00002              NA
TIME_2003      31Dec2003  00002           16.05
TIME_2003      01Dec2003  00002           12.27
TIME_2003      31Jan2003  00002           10.83
TIME_2003      01Jan2003  00002           11.07
TIME_2002      2002       00001              NA
TIME_2002      Dec2002    00001              NA
TIME_2002      Jan2002    00001              NA
TIME_2002      31Dec2002  00001           18.80
TIME_2002      01Dec2002  00001           13.64
TIME_2002      31Jan2002  00001           12.41
TIME_2002      01Jan2002  00001           16.97
TIME_2002      2002       00002              NA
TIME_2002      Dec2002    00002              NA
TIME_2002      Jan2002    00002              NA
TIME_2002      31Dec2002  00002           17.47
TIME_2002      01Dec2002  00002           16.58
TIME_2002      31Jan2002  00002           18.94
TIME_2002      01Jan2002  00002           18.36
         

As shown in the following code, you can use the PARTITION function to retrieve the names of the partition in which a value is stored.

SHOW partition_sales_by_year
<2003, 00001>

" Use a QDR to identify the partition of a specific time value
SHOW PARTITION(partition_sales_by_year (time '31Jan2002'))
TIME_2002
 
REPORT DOWN time PARTITION(partition_sales_by_year)
 
               PARTITION(PARTITION_S
               ----ALES_BY_YEAR)----
               -------PRODUCT-------
TIME             00001      00002
-------------- ---------- ----------
2003           TIME_2003  TIME_2003
2002           TIME_2002  TIME_2002
Dec2003        TIME_2003  TIME_2003
Jan2003        TIME_2003  TIME_2003
Dec2002        TIME_2002  TIME_2002
Jan2002        TIME_2002  TIME_2002
31Dec2003      TIME_2003  TIME_2003
01Dec2003      TIME_2003  TIME_2003
31Jan2003      TIME_2003  TIME_2003
01Jan2003      TIME_2003  TIME_2003
31Dec2002      TIME_2002  TIME_2002
01Dec2002      TIME_2002  TIME_2002
31Jan2002      TIME_2002  TIME_2002
01Jan2002      TIME_2002  TIME_2002