社区 › MSSQL数据库开发 › MSSQL教程之split命令支解分区的办法

冷月葬花魂 发表于 2015-1-16 22:38:07

MSSQL教程之split命令支解分区的办法

用一个库#bak_database存放这些历史数据。
SQL>selectPARTITIONEDfromdba_tableswheretable_name=LYTUSAGE;

PAR
---
YES


SQL>selectPARTITION_NAME,HIGH_VALUEfromdba_tab_partitionswhereTABLE_NAME=LYTUSAGE;

PARTITION_NAMEHIGH_VALUE
--------------------------------------------------------------------------------------------------------------
LYTUSAGE_200401TO_DATE(2004-02-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200402TO_DATE(2004-03-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200403TO_DATE(2004-04-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200404TO_DATE(2004-05-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200405TO_DATE(2004-06-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200406TO_DATE(2004-07-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200407TO_DATE(2004-08-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200408TO_DATE(2004-09-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200409TO_DATE(2004-10-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200410TO_DATE(2004-11-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200411TO_DATE(2004-12-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200412TO_DATE(2005-01-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200501TO_DATE(2005-02-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200502TO_DATE(2005-03-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200503TO_DATE(2005-04-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200504TO_DATE(2005-05-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200505TO_DATE(2005-06-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200506TO_DATE(2005-07-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200507TO_DATE(2005-08-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200508TO_DATE(2005-09-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200509TO_DATE(2005-10-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200510TO_DATE(2005-11-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200511TO_DATE(2005-12-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200512TO_DATE(2006-01-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA

24rowsselected.

删除2005年1月和2月的分区：
SQL>altertablelytusagedroppartitionLYTUSAGE_200501;

Tablealtered.

SQL>altertablelytusagedroppartitionLYTUSAGE_200502;

Tablealtered.

SQL>selectPARTITION_NAME,HIGH_VALUE,TABLESPACE_NAMEfromdba_tab_partitionswheretable_name=LYTUSAGE;

PARTITION_NAMEHIGH_VALUE
--------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------

LYTUSAGE_200401TO_DATE(2004-02-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200402TO_DATE(2004-03-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200403TO_DATE(2004-04-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200404TO_DATE(2004-05-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200405TO_DATE(2004-06-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200406TO_DATE(2004-07-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200407TO_DATE(2004-08-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200408TO_DATE(2004-09-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200409TO_DATE(2004-10-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200410TO_DATE(2004-11-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200411TO_DATE(2004-12-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200412TO_DATE(2005-01-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200503TO_DATE(2005-04-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200504TO_DATE(2005-05-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200505TO_DATE(2005-06-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200506TO_DATE(2005-07-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200507TO_DATE(2005-08-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200508TO_DATE(2005-09-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200509TO_DATE(2005-10-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200510TO_DATE(2005-11-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200511TO_DATE(2005-12-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS

LYTUSAGE_200512TO_DATE(2006-01-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
WACOS


22rowsselected.


SQL>ALTERTABLELYTUSAGE
SPLITPARTITIONLYTUSAGE_200503AT(TO_DATE(2005-03-0100:00:00,SYYYY-MM-DDHH24:MI:SS))
INTO(partitionLYTUSAGE_200502tablespacewacos,partitionLYTUSAGE_200503tablespacewacos)UPDATEGLOBALINDEXES;

Tablealtered.

SQL>ALTERTABLELYTUSAGE
SPLITPARTITIONLYTUSAGE_200502AT(TO_DATE(2005-02-0100:00:00,SYYYY-MM-DDHH24:MI:SS))
INTO(partitionLYTUSAGE_200501tablespacewacos,partitionLYTUSAGE_200502tablespacewacos)UPDATEGLOBALINDEXES;

Tablealtered.

PARTITION_NAMEHIGH_VALUE
--------------------------------------------------------------------------------------------------------------
LYTUSAGE_200412TO_DATE(2005-01-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200401TO_DATE(2004-02-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200402TO_DATE(2004-03-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200403TO_DATE(2004-04-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200404TO_DATE(2004-05-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200405TO_DATE(2004-06-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200406TO_DATE(2004-07-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200407TO_DATE(2004-08-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200408TO_DATE(2004-09-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200409TO_DATE(2004-10-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200410TO_DATE(2004-11-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200411TO_DATE(2004-12-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200503TO_DATE(2005-04-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200504TO_DATE(2005-05-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200505TO_DATE(2005-06-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200506TO_DATE(2005-07-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200507TO_DATE(2005-08-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200508TO_DATE(2005-09-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200509TO_DATE(2005-10-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200510TO_DATE(2005-11-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200511TO_DATE(2005-12-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200512TO_DATE(2006-01-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200501TO_DATE(2005-02-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA
LYTUSAGE_200502TO_DATE(2005-03-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA

24rowsselected.

SQL>altertablelytusagedroppartitionLYTUSAGE_200511;

Tablealtered.

SQL>altertablelytusagedroppartitionLYTUSAGE_200512;

Tablealtered.

SQL>ALTERTABLELYTUSAGEADDPARTITIONLYTUSAGE_200511
VALUESLESSTHAN(TO_DATE(2005-12-0100:00:00,SYYYY-MM-DDHH24:MI:SS))
TABLESPACEwacos
STORAGE(
INITIAL1M
NEXT10M
MINEXTENTS1
MAXEXTENTSunlimited
PCTINCREASE0)
PCTFREE5
PCTUSED95
NOLOGGING;

Tablealtered.

ALTERTABLELYTUSAGEADDPARTITIONLYTUSAGE_200512
VALUESLESSTHAN(TO_DATE(2006-01-0100:00:00,SYYYY-MM-DDHH24:MI:SS))
TABLESPACEwacos
STORAGE(
INITIAL1M
NEXT10M
MINEXTENTS1
MAXEXTENTSunlimited
PCTINCREASE0)
PCTFREE5
PCTUSED95
NOLOGGING;

Tablealtered.

优化的SQL查询算法，有效地提高查询速度

灵魂腐蚀 发表于 2015-1-19 19:50:24

不过话说回来了，绝大多数的性能优化准则与对sqlserver存储的结构理解息息相关

因胸联盟 发表于 2015-1-25 18:09:19

现在是在考虑:如果写到服务器端,我一下搞他个10个存储过程导过去,那久之服务器不就成垃圾箱了吗?即便优化了我的中间层.

兰色精灵 发表于 2015-2-3 12:39:50

无法深入到数据库系统层面去了解和探究

小女巫 发表于 2015-2-9 00:46:12

其实可以做一下类比，Oracle等数据库产品老早就支持了java编程，而且提供了java池参数作为用户配置接口。但是现在有哪些系统大批使用了java存储过程？！连Oracle自己的应用都不用为什么？！

金色的骷髅 发表于 2015-2-26 15:52:24

多加的系统视图和实时系统信息这些东西对DBA挑优非常有帮助，但是感觉粒度还是不太细。

柔情似水 发表于 2015-3-8 15:50:33

如果处理少量数据,比如几百条记录的数据,我不知道这两种情况哪个效率更高,如果处理大量数据呢?比如有表中有20万条记录.

莫相离 发表于 2015-3-16 03:49:19

我是新手，正在学习数据库和操作系统，深感理论的泛广，唯有一步一步来，但是又感觉时间不够，收集了很多资料却总是没能认真的看完，希望有一个讨论板块，大家共同解决，共同分享，共同努力

分手快乐 发表于 2015-3-22 19:35:09

对于数据库来说，查询是数据库的灵魂，那么SQL查询效率究竟效率如何呢？下文将带对SQL查询的相关问题进行讨论，供您参考。

查看完整版本: MSSQL教程之split命令支解分区的办法