MYSQL编程：Oracle平分区表的利用

这些过程被存储和运行在数据库服务器上，以减少在客户端的处理过程，从而最大限度地提高了处理能力，因为通常情况下数据库服务器会运行地更快。存储过程并不是MySQL独有的功能，但是这个最近新增加的功能使得这个数据库比以前更具吸引力了。oracle


在年夜型的企业使用或企业级的数据库使用中，要处置的数据量一般能够到达几十到几百GB，有的乃至能够到TB级。固然存储介质和数据处置手艺的开展也很快，可是仍旧不克不及满意用户的需求，为了利用户的大批的数据在读写操纵和查询中速率更快，Oracle供应了对表和索引举行分区的手艺，以改良年夜型使用体系的功能。

利用分区的长处：

1、加强可用性：假如表的某个分区呈现妨碍，表在其他分区的数据仍旧可用；

2、保护便利：假如表的某个分区呈现妨碍，必要修单数据，只修复该分区便可；

3、平衡I/O：能够把分歧的分区映照到磁盘以均衡I/O，改良全部体系功能；

4、改良查询功能：对分区工具的查询能够仅搜刮本人体贴的分区，进步检索速率。

Oracle数据库供应对表或索引的分区办法有三种：

1、局限分区

2、Hash分区（散列分区）

3、复合分区

上面将以实例的体例分离对这三种分区办法来讲明分区表的利用。为了测试便利，我们先建三个表空间。

createtablespacedinya_space01
datafile/test/demo/oracle/demodata/dinya01.dnfsize50M

createtablespacedinya_space01
datafile/test/demo/oracle/demodata/dinya02.dnfsize50M

createtablespacedinya_space01
datafile/test/demo/oracle/demodata/dinya03.dnfsize50M


1分区表的创立：1.1局限分区
局限分区就是对数据表中的某个值的局限举行分区，依据某个值的局限，决意将该数据存储在哪一个分区上。如依据序号分区，依据营业纪录的创立日期举行分区等。

需求形貌：有一个物料买卖表，表名：material_transactions。该表未来大概有万万级的数据纪录数。请求在建该表的时分利用分区表。这时候候我们可使用序号分区三个区，每一个区中估计存储三万万的数据，也能够利用日期分区，如每五年的数据存储在一个分区上。

依据买卖纪录的序号分区建表：

SQL>createtabledinya_test
2(
3transaction_idnumberprimarykey,
4item_idnumber(8)notnull,
5item_descriptionvarchar2(300),
6transaction_datedatenotnull
7)
8partitionbyrange(transaction_id)
9(
10partitionpart_01valueslessthan(30000000)tablespacedinya_space01,
11partitionpart_02valueslessthan(60000000)tablespacedinya_space02,
12partitionpart_03valueslessthan(maxvalue)tablespacedinya_space03
13);

Tablecreated.

SQL>

建表乐成，依据买卖的序号，买卖ID在三万万以下的纪录将存储在第一个表空间dinya_space01中，分区名为:par_01，在三万万到六万万之间的纪录存储在第二个表空间：dinya_space02中，分区名为：par_02，而买卖ID在六万万以上的纪录存储在第三个表空间dinya_space03中，分区名为par_03.

依据买卖日期分区建表：

SQL>createtabledinya_test
2(
3transaction_idnumberprimarykey,
4item_idnumber(8)notnull,
5item_descriptionvarchar2(300),
6transaction_datedatenotnull
7)
8partitionbyrange(transaction_date)
9(
10partitionpart_01valueslessthan(to_date(2006-01-01,yyyy-mm-dd))tablespacedinya_space01,
11partitionpart_02valueslessthan(to_date(2010-01-01,yyyy-mm-dd))tablespacedinya_space02,
12partitionpart_03valueslessthan(maxvalue)tablespacedinya_space03
13);

Tablecreated.

SQL>

如许我们就分离建了以买卖序号和买卖日期来分区的分区表。每次拔出数据的时分，体系将依据指定的字段的值来主动将纪录存储到制订的分区（表空间）中。

固然，我们还能够依据需求，利用两个字段的局限散布来分区，如partitionbyrange(transaction_id,transaction_date),分区前提中的值也做响应的改动，请读者自行测试。


1.2Hash分区（散列分区）
散列分区为经由过程指定分区编号来匀称散布数据的一种分区范例，由于经由过程在I/O设备长进行散列分区，使得这些分区巨细分歧。如将物料买卖表的数据依据买卖ID散列地寄存在指定的三个表空间中：

SQL>createtabledinya_test
2(
3transaction_idnumberprimarykey,
4item_idnumber(8)notnull,
5item_descriptionvarchar2(300),
6transaction_datedate
7)
8partitionbyhash(transaction_id)
9(
10partitionpart_01tablespacedinya_space01,
11partitionpart_02tablespacedinya_space02,
12partitionpart_03tablespacedinya_space03
13);

Tablecreated.

SQL>

建表乐成，此时拔出数据，体系将按transaction_id将纪录散列地拔出三个分区中，这里也就是三个分歧的表空间中。


1.3复合分区
偶然候我们必要依据局限分区后，每一个分区内的数据再散列地散布在几个表空间中，如许我们就要利用复合分区。复合分区是先利用局限分区，然后在每一个分区内再利用散列分区的一种分区办法，如将物料买卖的纪录定时间分区，然后每一个分区中的数据分三个子分区，将数据散列地存储在三个指定的表空间中：

SQL>createtabledinya_test
2(
3transaction_idnumberprimarykey,
4item_idnumber(8)notnull,
5item_descriptionvarchar2(300),
6transaction_datedate
7)
8partitionbyrange(transaction_date)subpartitionbyhash(transaction_id)
9subpartitions3storein(dinya_space01,dinya_space02,dinya_space03)
10(
11partitionpart_01valueslessthan(to_date(2006-01-01,yyyy-mm-dd)),
12partitionpart_02valueslessthan(to_date(2010-01-01,yyyy-mm-dd)),
13partitionpart_03valueslessthan(maxvalue)
14);

Tablecreated.

SQL>

该例中，先是依据买卖日期举行局限分区，然后依据买卖的ID将纪录散列地存储在三个表空间中。


2分区表操纵
以上懂得了三种分区表的建表办法，上面将利用实践的数据并针对按日期的局限分区来测试分区表的数据纪录的操纵。
2.1拔出纪录：
SQL>insertintodinya_testvalues(1,12,BOOKS,sysdate);
1rowcreated.
SQL>insertintodinya_testvalues(2,12,BOOKS,sysdate+30);
1rowcreated.
SQL>insertintodinya_testvalues(3,12,BOOKS,to_date(2006-05-30,yyyy-mm-dd));
1rowcreated.
SQL>insertintodinya_testvalues(4,12,BOOKS,to_date(2007-06-23,yyyy-mm-dd));
1rowcreated.
SQL>insertintodinya_testvalues(5,12,BOOKS,to_date(2011-02-26,yyyy-mm-dd));
1rowcreated.
SQL>insertintodinya_testvalues(6,12,BOOKS,to_date(2011-04-30,yyyy-mm-dd));
1rowcreated.
SQL>commit;
Commitcomplete.
SQL>

按下面的建表了局，2006年前的数据将存储在第一个分区part_01上，而2006年到2010年的买卖数据将存储在第二个分区part_02上，2010年今后的纪录存储在第三个分区part_03上。
2.2查询分区表纪录：
SQL>select*fromdinya_testpartition(part_01);

TRANSACTION_IDITEM_IDITEM_DESCRIPTIONTRANSACTION_DATE
--------------------------------------------------------------------------------
112BOOKS2005-1-1414:19:
212BOOKS2005-2-1314:19:
SQL>

SQL>select*fromdinya_testpartition(part_02);

TRANSACTION_IDITEM_IDITEM_DESCRIPTIONTRANSACTION_DATE
--------------------------------------------------------------------------------
312BOOKS2006-5-30
412BOOKS2007-6-23
SQL>

SQL>select*fromdinya_testpartition(part_03);

TRANSACTION_IDITEM_IDITEM_DESCRIPTIONTRANSACTION_DATE
--------------------------------------------------------------------------------
512BOOKS2011-2-26
612BOOKS2011-4-30
SQL>

从查询的了局能够看出，拔出的数据已依据买卖工夫局限存储在分歧的分区中。这里是指定了分区的查询，固然也能够不指定分区，间接实行select*fromdinya_test查询全体纪录。在也检索的数据量很年夜的时分，指定分区会年夜年夜进步检索速率。


2.3更新分区表的纪录：
SQL>updatedinya_testpartition(part_01)tsett.item_description=DESKwheret.transaction_id=1;

1rowupdated.

SQL>commit;

Commitcomplete.

SQL>



这里将第一个分区中的买卖ID=1的纪录中的item_description字段更新为“DESK”，能够看到已乐成更新了一笔记录。可是当更新的时分指定了分区，而依据查询的纪录不在该分区中时，将不会更新数据，请看上面的例子：



SQL>updatedinya_testpartition(part_01)tsett.item_description=DESKwheret.transaction_id=6;

0rowsupdated.

SQL>commit;

Commitcomplete.

SQL>

指定了在第一个分区中更新纪录，可是前提中限定买卖ID为6，而查询全表，买卖ID为6的纪录在第三个分区中，如许该条语句将不会更新纪录。


2.4删除分区表纪录：
SQL>deletefromdinya_testpartition(part_02)twheret.transaction_id=4;



1rowdeleted.



SQL>commit;



Commitcomplete.



SQL>



下面例子删除第二个分区part_02中的买卖纪录ID为4的一笔记录，和更新数据不异，假如指定了分区，而前提中的数据又不在该分区中时，将不会删除任何数据。


3分区表索引的利用：
分区表和一样平常表一样能够创建索引，分区表能够创立部分索引和全局索引。当分区中呈现很多事件而且要包管一切分区中的数据纪录的独一性时接纳全局索引。
3.1部分索引分区的创建：
SQL>createindexdinya_idx_tondinya_test(item_id)
2local
3(
4partitionidx_1tablespacedinya_space01,
5partitionidx_2tablespacedinya_space02,
6partitionidx_3tablespacedinya_space03
7);

Indexcreated.

SQL>

看查询的实行企图，从上面的实行企图能够看出，体系已利用了索引：

SQL>select*fromdinya_testpartition(part_01)twheret.item_id=12;



ExecutionPlan

----------------------------------------------------------

0SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=1Bytes=187)

10TABLEACCESS(BYLOCALINDEXROWID)OFDINYA_TEST(Cost=

2Card=1Bytes=187)

21INDEX(RANGESCAN)OFDINYA_IDX_T(NON-UNIQUE)(Cost=1

Card=1)

Statistics

----------------------------------------------------------

0recursivecalls

0dbblockgets

4consistentgets

0physicalreads

0redosize

334bytessentviaSQL*Nettoclient

309bytesreceivedviaSQL*Netfromclient

2SQL*Netroundtripsto/fromclient

1sorts(memory)

0sorts(disk)

2rowsprocessed



SQL>


3.2全局索引分区的创建。
全局索引创建时global子句同意指定索引的局限值，这个局限值为索引字段的局限值：

SQL>createindexdinya_idx_tondinya_test(item_id)
2globalpartitionbyrange(item_id)
3(
4partitionidx_1valueslessthan(1000)tablespacedinya_space01,
5partitionidx_2valueslessthan(10000)tablespacedinya_space02,
6partitionidx_3valueslessthan(maxvalue)tablespacedinya_space03
7);

Indexcreated.

SQL>

本例中对表的item_id字段创建索引分区，固然也能够不指定索引分区名间接对全部表创建索引，如：

SQL>createindexdinya_idx_tondinya_test(item_id);

Indexcreated.

SQL>

一样的，对全局索引依据实行企图能够看出索引已可使用：

SQL>select*fromdinya_testtwheret.item_id=12;



ExecutionPlan

----------------------------------------------------------

0SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=3Bytes=561)

10TABLEACCESS(BYGLOBALINDEXROWID)OFDINYA_TEST(Cost

=2Card=3Bytes=561)



21INDEX(RANGESCAN)OFDINYA_IDX_T(NON-UNIQUE)(Cost=1

Card=3)

Statistics

----------------------------------------------------------

5recursivecalls

0dbblockgets

10consistentgets

0physicalreads

0redosize

420bytessentviaSQL*Nettoclient

309bytesreceivedviaSQL*Netfromclient

2SQL*Netroundtripsto/fromclient

3sorts(memory)

0sorts(disk)

5rowsprocessed



SQL>


4分区表的保护：
懂得了分区表的创建、索引的创建、表和索引的利用后，在使用的还要常常对分区举行保护和办理。一样平常保护和办理的内容包含：增添一个分区，兼并一个分区及删除分区等等。上面以局限分区为例申明增添、兼并、删除分区的一样平常操纵：
4.1增添一个分区:
SQL>altertabledinya_test
2addpartitionpart_04valueslessthan(to_date(2012-01-01,yyyy-mm-dd))tablespacedinya_spa
ce03;

Tablealtered.

SQL>



增添一个分区的时分，增添的分区的前提必需年夜于现有分区的最年夜值，不然体系将提醒ORA-14074partitionboundmustcollatehigherthanthatofthelastpartition毛病。


4.2兼并一个分区：
SQL>altertabledinya_testmergepartitionspart_01,part_02intopartitionpart_02;

Tablealtered.

SQL>

在本例中将原本的表的part_01分区和part_02分区举行了兼并，兼并后的分区为part_02,假如在兼并的时分把兼并后的分区定为part_01的时分，体系将提醒ORA-14275cannotreuselower-boundpartitionasresultingpartition毛病。


4.3删除分区：
SQL>altertabledinya_testdroppartitionpart_01;

Tablealtered.

SQL>



删除分区表的一个分区后，查询该表的数据时显现，该分区中的数据已全体丧失，以是实行删除分区举措时要稳重，确保先备份数据后再实行，或将分区兼并。


5总结：
必要申明的是，本文在举例说名分区表事件操纵的时分，都指定了分区，由于指定了分区，体系在实行的时分则只操纵该分区的纪录，进步了数据处置的速率。不要指定分区间接操纵数据也是能够的。在分区表上建索引及多索引的利用和非分区表一样。别的，由于在保护分区的时分大概对分区的索引会发生必定的影响，大概必要在保护以后重修索引，相干内容请参考分区表索引部分的文档。


根据Evans的调查报告，“MySQL的使用在未来将继续呈成长趋势。”

始终遗憾SQLServer的登陆无法分配CPU/内存占用等指标数。如果你的SQLServer给别人分配了一个只可以读几个表的权限，而这个家伙疯狂的死循环进行连接查询，会给你的系统带来很大的负担。

分区表效率问题肯定是大家关心的问题。在我的试验中，如果按照分区字段进行的查询（过滤）效率会高于未分区表的相同语句。但是如果按照非分区字段进行查询，效率会低于未分区表的相同语句。

需要注意的一点，也是我使用过程中发现的一个问题。在建立function->schema->table后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区表会自动变为非分区表。这一点很让我纳闷。

SP4是一个累积性的ServicePack，包含自以前的ServicePack发布以来所有的修补程序（包括MS03-031安全公告）。

以前的DTS轻盈简单。但是现在的SSIS虽然功能强大了很多，但是总是让人感觉太麻烦。看看论坛中询问SSIS的贴子就知道。做的功能太强大了，往往会有很多用户不会用了

原来公司用过MYSQL自己也只是建个表写个SQL

可能有的朋友会抱怨集成的orderby，其实如果使用ranking函数,Orderby是少不了的。如果担心Orderby会影响效率，可以为orderby的字段建立聚集索引，查询计划会忽略orderby操作（因为本来就是排序的嘛）。