前面的文章介绍了查看表分区的一些基本信息,比如分区方法、分区所在的dbspace信息等。在这一小节里将向大家介绍如果通过访问IQ的系统表查看跟多表分区相关的详细信息,包括分区键、分区条件等。
下面我们再创建一个"hash 分区"表,以及一个"哈希-范围"分区表。
--创建哈希分区表
CREATE TABLE lineitem
(
l_orderkey int not null,
l_partkey int,
l_suppkey int,
l_linenumber int not null,
l_quantity numeric(10,2),
l_extendedprice numeric(10,2),
l_discount numeric(10,2),
l_tax numeric(10,2),
l_returnflag char(1),
l_linestatus char(1),
l_shipdate date,
l_commitdate date,
l_receiptdate date,
l_shipinstruct char(25),
l_shipmode char(10),
l_comment varchar(44),
PRIMARY KEY(l_orderkey,l_linenumber)
)
PARTITION BY HASH (l_orderkey,l_partkey,l_suppkey);
--创建"哈希-范围"分区表
CREATE TABLE table_2
(
c1 bigint not null,
c2 char(2),
c3 date,
c4 varchar(200),
PRIMARY KEY (c1)
)
PARTITION BY HASH (c4, c2)
SUBPARTITION BY RANGE (c3)
(
P1 VALUES P2 VALUES P3 VALUES)
下面是获得表分区详细信息的脚本
--get_par_info.sql
PARAMETERS table_name ;
select s.table_name,
case
when ps.partition_method = 1 then 'range partition'
when ps.partition_method = 3 and ifnull(ps.subpartition_method,0,ps.subpartition_method) = 0 then 'hash partition'
when ps.partition_method = 3 and ifnull(ps.subpartition_method,0,ps.subpartition_method) = 1 then 'hash-range partition'
else 'unknow partition'
end as partition_type,
tc.column_name as partition_key,
tc1.column_name as sub_partition_key,
p.partition_object_id,
p.partition_name,
p.partition_values
from systab s left outer join syspartitionscheme ps on s.object_id = ps.partitioned_object_id
inner join syspartitionkey spk on spk.partitioned_object_id = ps.partitioned_object_id
left outer join syspartition p on p.partitioned_object_id = ps.partitioned_object_id
inner join systabcol tc on tc.column_id = spk.column_id and s.table_id = tc.table_id
left outer join syssubpartitionkey sspk on sspk.partitioned_object_id = p.partitioned_object_id
left outer join systabcol tc1 on tc1.column_id = sspk.column_id and s.table_id = tc1.table_id
where s.table_name = '{table_name}'
运行dbisql执行上面的sql脚本,并传递表明参数:
dbisql -c "uid=DBA;pwd=sql" -onerror exit -nogui READ get_par_info.sql ['lineitem']
dbisql -c "uid=DBA;pwd=sql" -onerror exit -nogui READ get_par_info.sql ['table_2']