Oracle教程 2023-02-23

Oracle 视图 ALL_CUBE_HIERARCHIES 官方解释，作用，如何使用详细说明

Oracle视图ALL_CUBE_HIERARCHIES为用户提供维度层级的定义和信息。该视图用于查询用户对某一维度聚合内每个层次的父/子、同级、子树、范围等信息，这些信息可以提供给用户在查询SPARSE CUBE时进行性能优化。

使用时需要指定CUBE_NAME参数，用于指定查询的维度层级来自什么CUBE。以下是使用ALL_CUBE_HIERARCHIES视图的示例：

— 获取维度timecube_level的层级定义
SELECT * FROM ALL_CUBE_HIERARCHIES WHERE CUBE_NAME = ‘TIME_CUBE’ AND LEVEL_NAME = ‘LEVEL’;

— 获取timecube_level的同级信息
SELECT SIBLING_NAME, SIBLING_LEVEL FROM ALL_CUBE_HIERARCHIES WHERE CUBE_NAME = ‘TIME_CUBE’ AND LEVEL_NAME = ‘LEVEL’;

ALL_CUBE_HIERARCHIES describes the OLAP dimension hierarchies accessible to the current user.

Related Views

DBA_CUBE_HIERARCHIES describes all OLAP dimension hierarchies in the database.
USER_CUBE_HIERARCHIES describes the OLAP dimension hierarchies owned by the current user. This view does not display the OWNER column.

Column	Datatype	NULL	Description
`OWNER`	`VARCHAR2(128)`	`NOT NULL`	Owner of the dimension
`DIMENSION_NAME`	`VARCHAR2(128)`	`NOT NULL`	Name of a dimension, such as `TIME`
`HIERARCHY_NAME`	`VARCHAR2(128)`	`NOT NULL`	Name of a hierarchy for the dimension, such as `CALENDAR`
`HIERARCHY_ID`	`NUMBER`	`NOT NULL`	ID of the hierarchy
`HIERARCHY_TYPE`	`VARCHAR2(5)`		Type of the hierarchy: `LEVEL` `VALUE`
`DESCRIPTION`	`NVARCHAR2(300)`		Description of the hierarchy in the session language
`IS_RAGGED`	`NUMBER`		Indicates whether ragged hierarchies are permitted in subsequent builds. User dimensions that are enabled for materialized views and Time dimensions are set to `0`. Builds then check the data for ragged hierarchies and fail if one is detected. When User dimensions are set to `1`, the builds do not check for ragged hierarchies.
`IS_SKIP_LEVEL`	`NUMBER`		Indicates whether skip-level hierarchies are permitted in subsequent builds. User dimensions that are enabled for materialized views and Time dimensions are set to `0`. Builds then check the data for skip-level hierarchies and fail if one is detected. When User dimensions are set to `1`, the builds do not check for skip-level hierarchies.
`REFRESH_MVIEW_NAME`	`VARCHAR2(200)`		Name of the Refresh Materialized View associated with the hierarchy
`CUSTOM_ORDER`	`CLOB`		The textual representation of the sort orderby clause used to load dimension members of the hierarchy into the AW