Sybase Table Partitioning

------------tables that cannot be partitioned: system tables, tables in use, temporary tables, and tables with clustered indexes
----Checking the Sybase version
SELECT @ @version

--View partition status (sp_helpartition default default,system,logsegment)
---can see the partition type (Partition_type), the number of partitions (partitions), the fields of the partition (Partition_keys), the number of data (Row_count) per partition,
---creation time (create_date), Segment (segment), and partition name (Partition_name), partitioning criteria for each partition (Partition_conditions)

sp_helpartition [TableName];

---View partition value information, such as viewing the partition granularity of a range partition table, list values

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 ')

---viewing segments
Sp_helpsegment

--View disk settings by Segment

Sp_helpsegment system

sp_helpsegment [' segment ']

---log space check
--normal state: Free_pages accounted for more than 50% of total_pages total
-Abnormal state and taking measures:
Sp_helpsegment logsegment
--or under SA permissions
DBCC CHECKTABLE (syslogs)
-----DBCC CHECKTBALE command can only be used for log segments that have separate data devices, if data segments and logs are mixed with data devices only
-----using the sp_helpsegment logsegment command, it is recommended that you use DBCC CHECKTBALE results to be more intuitive

----Remove the segment (segment to exist) end name Database name Device name (if the segment exists on only one partition, no device name is required: no third parameter)
Sp_dropsegment ' segment ', dbname, [DriveName]

----adding segments
Sp_addsegment ' segment ', dbname, [DriveName]

---Place a table on a segment
Sp_placeobject ' segment ', TableName

----Viewing devices
Sp_helpdevice

----can see the database space including the log (corresponding database)
sp_spaceused

---can see the spatial information for database device devices that the database occupies

sp_helpdb javadb

To create a device----data and log separation:
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 '
----How to view a partition key for a semantic partition table
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

------How to tell if a table is partitioned table 0 is no partition
Select COUNT (*) from Dbo.syspartitionkeys where id=object_id (' T_pos_retail_order_item_his ')
SELECT * from T_pos_retail_order_his partition (3580120);

---Estimate the number of partitions that are occupied by the number of partitions or the amount of memory that can be configured
Sp_helpconfig ' Number of open partitions ', ' 1000 '

--Check Table partition parameters
sp_configure ' Enable semantic partitioning '

--SA Permissions account setting table partition parameter is available
sp_configure ' enable semantic partitioning ', 1

-----Use SAPSA to open the table partition and initialize it to 800
sp_configure ' number of open partitions ', 800

-----can be increased by 10% when initialized with the maximum partition size that can be used with SAPSA open queries
Sp_monitorconfig ' Open partitions '

---to cancel a partition, you must first modify the partition to Roundrobin, and on one segment (the partition must first be removed).
ALTER TABLE T_pos_retail_order_item_his partition by Roundrobin (part1)

-------Modify the table partition t_pos_retail_order_his_201612 if you do not write the default name as the table name +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 < index name >; When you delete an index, the system deletes the index from the data dictionary.
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

----Create an index (default ASC Ascending)---and place the clustered index in the default segment
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 ';

----partition does not have an index 4s partition at the same time build index 0.4s (if more than 0.15s at one partition) no index and partition 20s index 0.5
SELECT
COUNT (1)
From T_pos_retail_order_item_his
WHERE order_date between ' 2017-01-01 ' and ' 2017-08-31 '

Sybase Table Partitioning