refs:

https://www.cnblogs.com/knowledgesea/p/3696912.html

https://blog.csdn.net/leshami/article/details/4783702

 

分区表明细信息

创建好分区表后查看相应的信息:

1.查看分区依据列的指定值所在的分区 

--查询分区依据列为10000014的数据在哪个分区上
select $partition.bgPartitionFun(2000000)  --返回值是2，表示此值存在第2个分区,bgPartitionFun为分区方法 

2.查看分区表中，每个非空分区存在的行数

--查看分区表中，每个非空分区存在的行数select $partition.etor_ticket_partition_function(create_time) as partitionNum,count(0) as recordCount
from by_etor_tickets_1
group by  $partition.etor_ticket_partition_function(create_time)

3.查看指定分区中的数据记录 

---查看指定分区中的数据记录
select * from bigorder where $partition.bgPartitionFun(orderid)=2

结果：数据从1000001开始到200W结束

 

 

    4. 查看分区的相关情况

--查看分区及分区范围的情况
select * from sys.partitions where object_id = object_id('by_etor_tickets_fault_1');
select * from sys.partition_range_values;

--查看分区架构情况
select * from sys.partition_schemes;

--查看某一特定分区列值属于哪个分区
select Performance.$partition.Part_func_orders('20050325') as partition_num;

--查看某一特定分区的记录
select * from orders where Performance.$partition.Part_func_orders(orderdate) = 2

--查看各分区所包含的记录数
select $partition.Part_func_orders(orderdate) as partition_num,
  count(*) as record_num
from orders
group by $partition.Part_func_orders(orderdate)
order by $partition.Part_func_orders(orderdate);