데이터베이스 내에서 현재 사용자가 접근 권한을 가진 분할 클래스에 대한 정보를 보여준다.
속성명 |
데이터 타입 |
설명 |
---|---|---|
class_name |
VARCHAR(255) |
클래스명 |
partition_name |
VARCHAR(255) |
파티션명 |
partition_class_name |
VARCHAR(255) |
파티션 클래스 명 |
partition_type |
VARCHAR(32) |
파티션 타입 |
partition_expr |
VARCHAR(255) |
파티션 표현식 |
partition_values |
SEQUENCE OF |
RANGE - MIN/MAX value |
CREATE VCLASS db_partition
(sp_name, sp_type, return_type, arg_count, lang, target, owner)
AS
SELECT p.class_of.class_name AS class_name, p.pname AS partition_name,
p.class_of.class_name || '__p__' || p.pname AS partition_class_name,
CASE WHEN p.ptype = 0 THEN 'HASH'
WHEN p.ptype = 1 THEN 'RANGE'
ELSE 'LIST' ENDASpartition_type,
TRIM(SUBSTRING( pi.pexpr FROM 8 FOR (POSITION(' FROM ' IN pi.pexpr)-8))) AS
partition_expression,
p.pvalues AS partition_values
FROM _db_partition p,
( select * from _db_partition sp
where sp.class_of = p.class_of AND sp.pname is null) pi
WHERE p.pname is not null AND
( CURRENT_USER = 'DBA'
OR
{p.class_of.owner.name} SUBSETEQ
( SELECT SET{CURRENT_USER} + COALESCE(SUM(SET{t.g.name}), SET{})
FROM db_user u, TABLE(groups) AS t(g)
WHERE u.name = CURRENT_USER
)
OR
{p.class_of} SUBSETEQ
( SELECT SUM(SET{au.class_of})
FROM _db_auth au
WHERE {au.grantee.name} SUBSETEQ
( SELECT SET{CURRENT_USER} + COALESCE(SUM(SET{t.g.name}), SET{})
FROM db_user u, TABLE(groups) AS t(g)
WHERE u.name = CURRENT_USER) AND
au.auth_type = 'SELECT'
)
)
다음 예제에서는 participant2 클래스의 현재 구성된 분할 정보를 조회한다. (영역 분할 정의의 예제 참조)
csql> select * from db_partition where class_name = 'participant2';
csql> ;x
=== <Result of SELECT Command in Line 2> ===
class_name partition_name partition_class_name partition_type partition_expr partition_values
====================================================================================================================================
'participant2' 'before_2000' 'participant2__p__before_2000' 'RANGE' 'host_year' {NULL, 2000}
'participant2' 'before_2008' 'participant2__p__before_2008' 'RANGE' 'host_year' {2000, 2008}