------------不能分区的表:系统表、正在使用的表、临时表、和有聚簇索引的表
----检查sybase版本
select @@version --查看分区情况(sp_helpartition 默认default,system,logsegment)
 ---可以查看 分区类型（partition_type），分区个数（partitions），分区的字段（partition_keys）， 每个分区有多少数据（row_count），
 ---创建时间（create_date），所在段（segment），以及分区的划分名（partition_name），每个分区的划分条件（Partition_Conditions）sp_helpartition  [tablename];
 
 
---查看分区值信息，比如查看范围分区表的分区粒度、列表分区的各个列表值
select c.number,c.text,c.partitionid,p.name
from sysindexes i, syscomments c,syspartitions p
where i.conditionid = c.id and c.partitionid = p.partitionid
and i.id =object_id('tablename')---查看段 
sp_helpsegment --通过 段查看磁盘设置

sp_helpsegment systemsp_helpsegment  ['segment']
 
---日志空间检查
--正常状态：free_pages 占total_pages总数的50%以上
--非正常状态和采取措施：
sp_helpsegment logsegment
--或者 sa权限下
dbcc checktable(syslogs) 
-----dbcc checktbale 命令只能针对日志段拥有独立数据设备的情况，如果数据段和日志混合使用数据设备的只能
-----使用sp_helpsegment logsegment命令，建议使用dbcc checktbale 结果比较直观----去掉段(段要存在) 端名 数据库名 设备名(如果该段只在一个分区上存在，则不需要设备名：不要第三个参数)
sp_dropsegment   'segment', dbname  ,[drivename]----添加段
sp_addsegment  'segment', dbname  ,[drivename]---将表放置在段上 
sp_placeobject 'segment' , tablename ----查看设备
sp_helpdevice  
----可看到数据库空间包括日志(对应数据库) 
sp_spaceused 
 ---可看到数据库占用的数据库设备device的空间信息
sp_helpdb javadb
----数据和日志分离 创建设备： 
USE master 
DISK INIT name = 'FX_DATA_2019', physname = '/sybase/CJQ/FXDATA/FX_DATA_2019.dat' , size = '10000M',dsync = 'false' 
DISK INIT name = 'FX_LOG_2019', physname = '/sybase/CJQ/FXLOG/FX_LOG_2019.dat' , size = '1000M',dsync = 'false' 
 
ALTER DATABASE javadb ON FX_DATA_2019 = '1G' LOG ON FX_LOG_2019 = '1G' 
----如何查看一个语义分区表的分区键
select c.name
from syspartitionkeys pk,syscolumns c
where pk.id = object_id('T_POS_RETAIL_ORDER_HIS')
and pk.id = c.id
and pk.colid = c.colid------如何判断一个表是否是分区表 0 就是没有分区
select count(*) from dbo.syspartitionkeys where id=object_id('T_POS_RETAIL_ORDER_ITEM_HIS')
select * from T_POS_RETAIL_ORDER_HIS partition (3580120);---估计分区数所占用的内存或一定内存所能配置的分区数
 sp_helpconfig 'number of open partitions','1000'

 --检查表分区参数
sp_configure 'enable semantic partitioning'--SA权限帐户设置表分区参数为可用
sp_configure 'enable semantic partitioning', 1 
 -----使用 sapsa 开启 表分区 并且 初始化为800
sp_configure 'number of open partitions',800-----使用 sapsa 开启 查询最多能使用的分区大小 初始化的时候可以增加10%
sp_monitorconfig 'open partitions'

 --- 如要取消分区,必须先将分区修改为 roundrobin,且在一个段上(取消分区必须要先删除索引)。
 alter table T_POS_RETAIL_ORDER_ITEM_HIS partition by roundrobin (part1)

 -------修改表 分区 T_POS_RETAIL_ORDER_HIS_201612 如果不写默认名为 表名+id
 alter TABLE
 T_POS_RETAIL_ORDER_HIS
 partition by Range (ORDER_DATE)(
 T_POS_RETAIL_ORDER_HIS_201612 VALUES <= ('2016-12-31') on FX_DATA_2016,
 T_POS_RETAIL_ORDER_HIS_201701 VALUES <= ('2017-01-31') on FX_DATA_2017,
 T_POS_RETAIL_ORDER_HIS_201702 VALUES <= ('2017-02-28') on FX_DATA_2017,
 T_POS_RETAIL_ORDER_HIS_201703 VALUES <= ('2017-03-31')on FX_DATA_2017,
 T_POS_RETAIL_ORDER_HIS_201704 VALUES <= ('2017-04-30')on FX_DATA_2017,
 T_POS_RETAIL_ORDER_HIS_201705 VALUES <= ('2017-05-31')on FX_DATA_2017,
 T_POS_RETAIL_ORDER_HIS_201706 VALUES <= ('2017-06-30')on FX_DATA_2017,
 T_POS_RETAIL_ORDER_HIS_201707 VALUES <= ('2017-07-31')on FX_DATA_2017,
 T_POS_RETAIL_ORDER_HIS_201708 VALUES <= ('2017-08-31')on FX_DATA_2018,
 T_POS_RETAIL_ORDER_HIS_201709 VALUES <= ('2017-09-30')on FX_DATA_2017,
 T_POS_RETAIL_ORDER_HIS_201710 VALUES <= ('2017-10-31') on FX_DATA_2017,
 T_POS_RETAIL_ORDER_HIS_201711 VALUES <= ('2017-11-30') on FX_DATA_2017,
 T_POS_RETAIL_ORDER_HIS_201712 VALUES <= ('2017-12-31') on FX_DATA_2017,
 T_POS_RETAIL_ORDER_HIS_201801 VALUES <= ('2018-01-31')on FX_DATA_2018,
 T_POS_RETAIL_ORDER_HIS_201802 VALUES <= ('2018-02-28')on FX_DATA_2018,
 T_POS_RETAIL_ORDER_HIS_201803 VALUES <= ('2018-03-31')on FX_DATA_2018,
 T_POS_RETAIL_ORDER_HIS_201804 VALUES <= ('2018-04-30')on FX_DATA_2018,
 T_POS_RETAIL_ORDER_HIS_201805 VALUES <= ('2018-05-31')on FX_DATA_2018,
 T_POS_RETAIL_ORDER_HIS_201806 VALUES <= ('2018-06-30')on FX_DATA_2018,
 T_POS_RETAIL_ORDER_HIS_201807 VALUES <= ('2018-07-31')on FX_DATA_2018,
 T_POS_RETAIL_ORDER_HIS_201808 VALUES <= ('2018-08-31')on FX_DATA_2018,
 T_POS_RETAIL_ORDER_HIS_201809 VALUES <= ('2018-09-30')on FX_DATA_2018,
 T_POS_RETAIL_ORDER_HIS_201810 VALUES <= ('2018-10-31') on FX_DATA_2018,
 T_POS_RETAIL_ORDER_HIS_201811 VALUES <= ('2018-11-30') on FX_DATA_2018,
 T_POS_RETAIL_ORDER_HIS_201812 VALUES <= ('2018-12-31') on FX_DATA_2018)

alter TABLE
 T_POS_RETAIL_ORDER_HIS
 partition by Range (ORDER_DATE)(
 T_POS_RETAIL_ORDER_HIS_201612 VALUES <= ('2016-12-31') on 'default',
 T_POS_RETAIL_ORDER_HIS_201701 VALUES <= ('2017-01-31') on 'default',
 T_POS_RETAIL_ORDER_HIS_201702 VALUES <= ('2017-02-28') on 'default',
 T_POS_RETAIL_ORDER_HIS_201703 VALUES <= ('2017-03-31') on 'default',
 T_POS_RETAIL_ORDER_HIS_201704 VALUES <= ('2017-04-30') on 'default',
 T_POS_RETAIL_ORDER_HIS_201705 VALUES <= ('2017-05-31') on 'default',
 T_POS_RETAIL_ORDER_HIS_201706 VALUES <= ('2017-06-30') on 'default',
 T_POS_RETAIL_ORDER_HIS_201707 VALUES <= ('2017-07-31') on 'default',
 T_POS_RETAIL_ORDER_HIS_201708 VALUES <= ('2017-08-31') on 'default',
 T_POS_RETAIL_ORDER_HIS_201709 VALUES <= ('2017-09-30') on 'default',
 T_POS_RETAIL_ORDER_HIS_201710 VALUES <= ('2017-10-31') on 'default',
 T_POS_RETAIL_ORDER_HIS_201711 VALUES <= ('2017-11-30') on 'default',
 T_POS_RETAIL_ORDER_HIS_201712 VALUES <= ('2017-12-31') on 'default',
 T_POS_RETAIL_ORDER_HIS_201801 VALUES <= ('2018-01-31') on 'default',
 T_POS_RETAIL_ORDER_HIS_201802 VALUES <= ('2018-02-28') on 'default',
 T_POS_RETAIL_ORDER_HIS_201803 VALUES <= ('2018-03-31') on 'default',
 T_POS_RETAIL_ORDER_HIS_201804 VALUES <= ('2018-04-30') on 'default',
 T_POS_RETAIL_ORDER_HIS_201805 VALUES <= ('2018-05-31') on 'default',
 T_POS_RETAIL_ORDER_HIS_201806 VALUES <= ('2018-06-30') on 'default',
 T_POS_RETAIL_ORDER_HIS_201807 VALUES <= ('2018-07-31') on 'default',
 T_POS_RETAIL_ORDER_HIS_201808 VALUES <= ('2018-08-31') on 'default',
 T_POS_RETAIL_ORDER_HIS_201809 VALUES <= ('2018-09-30') on 'default',
 T_POS_RETAIL_ORDER_HIS_201810 VALUES <= ('2018-10-31') on 'default',
 T_POS_RETAIL_ORDER_HIS_201811 VALUES <= ('2018-11-30') on 'default',
 T_POS_RETAIL_ORDER_HIS_201812 VALUES <= ('2018-12-31') on 'default') 
-----删除索引 DROP INDEX <索引名>; 删除索引时，系统会从数据字典中删去有关该索引的
DROP INDEX T_POS_RETAIL_ORDER_HIS.I_T_POS_RETAIL_ORDER_HIS;
DROP INDEX T_POS_RETAIL_ORDER_ITEM_HIS.I_T_POS_RETAIL_ORDER_ITEM_HIS----创建索引(默认ASC 升序) ---并将聚簇索引放在 default 段
CREATE CLUSTERED INDEX I_T_POS_RETAIL_ORDER_HIS ON T_POS_RETAIL_ORDER_HIS(ORDER_DATE ASC,ORDER_NO ASC) on 'default';
CREATE UNIQUE INDEX I_T_POS_RETAIL_ORDER_ITEM_HIS ON T_POS_RETAIL_ORDER_ITEM_HIS(ORDER_DATE ASC,ORDER_NO ASC,ITEM_NO) on 'default';----只分区没建立索引 4s 分区同时建立索引 0.4s(如果在一个分区时更快 0.15s) 没有索引和分区 20s 建立索引0.5 
SELECT
COUNT(1)
FROM T_POS_RETAIL_ORDER_ITEM_HIS
WHERE ORDER_DATE BETWEEN '2017-01-01' and '2017-08-31'