第11章 其他表类型ppt课件

.,1,临时表外部表分区表,第11章其他表类型,.,2,本章要点,了解临时表的特点。熟练掌握不同类别的临时表的创建和使用。熟练掌握外部表的创建。掌握在创建外部表时可以指定的一些子句。熟练掌握不同类型的分区表的创建。掌握增加分区的不同方法。掌握合并与删除分区的方法。,.,3,11.1临时表,临时表中的数据在特定条件下自动释放。它与普通的数据表一样，只需要一次创建，其结构从创建到删除的整个期间都是有效的（其结构将一直存在）。,.,4,11.1.1临时表的特点,临时表主要有如下特点：临时表只有在用户向表中添加数据时，Oracle才会为其分配存储空间。为临时表分配的空间来自临时表空间，这避免了与永久对象的数据争用存储空间。在临时表中存储数据是以事务或会话为基础的。当用户当前的事务结束或会话终止时，临时表占用的存储空间将被释放，存储的数据也随着丢失。用户可以在临时表上建立索引、视图和触发器等。,.,5,11.1.2临时表的类别,根据临时表中数据被释放的时间不同，临时表分为事务级别的临时表和会话级别的临时表两类。(1)事务级别临时表事务级别临时表中的数据只在事务生命周期中存在。当一个事务结束(提交或者回滚事务)，Oracle自动清除该表中的数据。创建事务级别临时表时，需要使用ONCOMMITDELETEROWS子句。(2)会话级别临时表会话级别临时表中的数据只在会话生命周期之中存在，当用户退出，会话结束的时候，Oracle自动清除临时表中数据。创建会话级别临时表时，需要使用ONCOMMITPRESERVEROWS子句。,.,6,11.1.3临时表的创建与使用,使用CREATEGLOBALTEMPORARYTABLE语句创建临时表使用ONCOMMIT子句说明临时表的类型，默认为事务级别的临时表。,.,7,11.1.3临时表的创建与使用,1创建事务级别临时表CREATEGLOBALTEMPORARYTABLEtran_temp(IDNUMBER(2)PRIMARYKEY,nameVARCHAR2(20)ONCOMMITDELETEROWS;2使用事务级别临时表与普通表的使用方法相同，都可以执行SELECT语句、INSERT语句、UPDATE语句等。示例,.,8,11.1.3临时表的创建与使用,3创建会话级别临时表CREATEGLOBALTEMPORARYTABLEsess_temp(IDNUMBER(2)PRIMARYKEY,nameVARCHAR2(20)ONCOMMITPRESERVEROWS;4使用会话级别临时表会话级别的临时表中的数据，当用户退出会话结束时，Oracle自动清除该临时表中的数据。示例,.,9,11.1.3临时表的创建与使用,5删除临时表使用DROPTABLE语句。删除该表后，该表的相关内容也从user_tables视图中删除。下列语句将删除前面所创建的临时表sess_temp：DROPTABLEsess_temp;,.,10,11.2外部表,外部表是引用在数据库以外的文件系统中存储的数据的只读表，也就是说，外部表所要读取的数据存储在Oracle数据库外部的文件中，并且只能读取这些数据，不能进行数据写入。外部表就像一个视图，允许用户用SQL语句访问外部数据（不需要将数据装载到数据库中）。,.,11,11.2.1使用外部表读取外部文件,创建外部表，需要在CREATETABLE语句中，添加ORGANIZATIONEXTERNAL子句，在该子句中可以指定下面所示的一些选项：TYPE：指定所使用的访问驱动程序，默认访问驱动程序是ORACLE_LOADER。DEFAULTDIRECTORY：指定所使用的目录对象，该目录对象指向外部数据文件所在目录。LOCATION：指定源数据文件。ACCESSPARAMETERS：设置访问驱动程序进行数据格式转换时的参数。FIELDSTERMINATEDBY：指定字段之间的分隔符。,.,12,11.2.1使用外部表读取外部文件,CREATEDIRECTORYexternal_cardASE:external;CREATETABLEe_card(cardidNUMBER(10),cardnumberCHAR(9),studentidNUMBER(10),registertiemDATE)ORGANIZATIONEXTERNAL(TYPEoracle_loaderDEFAULTDIRECTORYexternal_cardACCESSPARAMETERS(FIELDSTERMINATEDBY,)LOCATION(card.txt);说明：在使用外部表的时候，需要先建立目录对象（数据文件所在目录）。,.,13,11.2.1使用外部表读取外部文件,在创建外部表时，可以指定一些子句来对错误进行处理。处理外部表错误的子句包括：REJECTLIMIT：指定在数据类型转换期间允许出现的错误个数BADFILE(或NOBADFILE)：如果指定NOBADFILE子句，则Oracle将会忽略所有数据类型转换错误。LOGFILE(或NOLOGFILE)：LOGFILE子句用来指定一个记录错误信息的日志文件。如果使用NOLOGFILE子句，则不会将错误信息写入任何日志文件中。,.,14,使用BADFILE和REJECTLIMIT子句示例,CREATETABLEe_card(cardidNUMBER(10),cardnumberNUMBER(9),studentidNUMBER(10),registertiemDATE)ORGANIZATIONEXTERNAL(TYPEoracle_loaderDEFAULTDIRECTORYexternal_cardACCESSPARAMETERS(RECORDSDELIMITEDBYNEWLINEBADFILEcard_bad.txtFIELDSTERMINATEDBY,)LOCATION(card.txt)REJECTLIMITUNLIMITED;,CREATEDIRECTORYdatadirASc:data;CREATETABLEstudent_ext(snochar(7),snamevarchar2(20),ssexchar(4),birthdayDATE,sdeptvarchar2(20)ORGANIZATIONEXTERNAL(TYPEoracle_loaderDEFAULTDIRECTORYdatadirACCESSPARAMETERS(recordsdelimitedbynewlinefieldsterminatedby|missingfieldvaluesarenull(sno,sname,ssex,birthdaychardate_formatdatemaskyyyy-mm-dd,sdept)LOCATION(student.txt);,.,16,创建Excel数据的外部表,CREATETABLEcourse_ext(cnochar(7),cnamevarchar2(20)ORGANIZATIONEXTERNAL(TYPEoracle_loaderDEFAULTDIRECTORYdatadirACCESSPARAMETERS(recordsdelimitedbynewlinebadfilebad_course.txtlogfilelog_course.txtfieldsterminatedby,)LOCATION(course.csv);,需要使用.csv文件,.,17,11.3分区表,在大型的数据库应用中，需要处理的数据量可以达到几十到几百GB，甚至达到TB级。分区表是将一个非常大的表分割成较小的片段(分区)。Oracle数据库对表或索引分区的方法有5种：范围分区、散列分区、列表分区、组合范围散列分区和组合范围列表分区。在创建分区表时，应该根据不同类型分区表的特点，选择合适的分区类型。,.,18,分区原则,表的大小超过2GB要对一个表进行并行DML操作，必须分区为了平衡硬盘的I/O操作，将一个表分散存储在不同的表空间中，必须对它进行分区如果需要将表一部分设置为只读，另一部分为可更新的，必须对表进行分区。,.,19,1.使用RANGE关键字创建范围,概述范围分区是按照分区列值的范围来对表进行分区的。通过PARTITIONBYRANGE子句说明根据范围进行分区，其后括号中列出分区列，可以进行多列分区。每个分区以PARTITION关键字开头，其后是分区名。VALUESLESSTHAN子句用于设置分区中分区列值的范围。可以对每个分区的存储进行设置，也可以对所有分区采用默认的存储设置。,.,20,示例,创建一个分区表，将学生信息根据其出生日期进行分区，将1980年1月1日前出生的学生信息保存在ORCLTBS1表空间中，将1980年1月1日到1990年1月1日出生的学生信息保存在ORCLTBS2表空间中，将其他学生信息保存在ORCLTBS3表空间中。,CREATETABLEstudent_range(snoNUMBER(6)PRIMARYKEY,snameVARCHAR2(10),sageint,birthdayDATE)PARTITIONBYRANGE(birthday)(PARTITIONp1VALUESLESSTHAN(TO_DATE(1980-1-1,YYYY-MM-DD)TABLESPACEORCLTBS1,PARTITIONp2VALUESLESSTHAN(TO_DATE(1990-1-1,YYYY-MM-DD)TABLESPACEORCLTBS2,PARTITIONp3VALUESLESSTHAN(MAXVALUE)TABLESPACEORCLTBS3);,.,22,2.使用LIST关键字创建列表分区表,概述如果分区列的值并不能划分范围（非数值类型或日期类型），同时分区列的取值范围只是一个包含少数值的集合，则可以对表进行列表分区（LIST），如按地区、性别等分区。与范围分区不同，列表分区不支持多列分区，只能根据一个单独的列来进行分区。创建列表分区时需要指定分区列和分区描述。通过PARTITIONBYLIST子句说明根据列表进行分区，其后括号中列出分区列。每个分区以PARTITION关键字开头，其后是分区名。VALUES子句用于设置分区所对应的分区列取值。,.,23,示例,创建一个分区表，将学生信息按性别不同进行分区，男学生信息保存在表空间ORCLTBS1中，而女学生信息保存在ORCLTBS2中。,CREATETABLEstudent_list(snoNUMBER(6)PRIMARYKEY,snameVARCHAR2(10),sexCHAR(2)CHECK(sexin(M,F)PARTITIONBYLIST(sex)(PARTITIONstudent_maleVALUES(M)TABLESPACEORCLTBS1,PARTITIONstudent_femaleVALUES(F)TABLESPACEORCLTBS2);,.,25,3.使用HASH关键字创建散列分区表,概述在进行范围分区或列表分区时，由于无法对各个分区中可能具有的记录数量进行预测，可能导致数据在各个分区中分布不均衡，某个分区中数据很多，而某个分区中数据很少。此时可以采用散列分区（HASH）方法，在指定数量的分区中均等地分配数据。为了创建散列分区，需要指定分区列、分区数量或单独的分区描述。通过PARTITIONBYHASH指定分区方法，其后的括号指定分区列。使用PARTITION子句指定每个分区名称和其存储空间。或者使用PARTITIONS子句指定分区数量，用STOREIN子句指定分区存储空间。,.,26,示例,创建一个分区表，根据学号将学生信息均匀分布到ORCLTBS1和ORCLTBS2两个表空间中。,CREATETABLEstudent_hash(snoNUMBER(6)PRIMARYKEY,snameVARCHAR2(10)PARTITIONBYHASH(sno)(PARTITIONp1TABLESPACEORCLTBS1,PARTITIONp2TABLESPACEORCLTBS2);或：CREATETABLEstudent_hash2(snoNUMBER(6)PRIMARYKEY,snameVARCHAR2(10)PARTITIONBYHASH(sno)PARTITIONS2STOREIN(ORCLTBS1,ORCLTBS2);,.,28,4.复合分区,复合分区包括：范围-列表复合分区范围-散列复合分区。创建复合分区时需要指定分区方法（PARTITIONBYRANGE）分区列子分区方法（SUBPARTITIONBYHASH，SUBPARTITIONBYLIST）子分区列每个分区中子分区数量或子分区的描述。,范围-列表复合分区范围-列表复合分区先对表进行范围分区，然后再对每个分区进行列表分区，即在一个范围分区中创建多个列表子分区。范围-列表复合分区示例创建一个范围-列表复合分区表，将1980年1月1日前出生的男、女学生信息分别保存在ORCLTBS1和ORCLTBS2表空间中，1980年1月1日到1990年1月1日出生的男、女学生信息分别保存在ORCLTBS3和ORCLTBS4表空间中，其他学生信息保存在ORCLTBS5表空间中。,CREATETABLEstudent_range_list(snoNUMBER(6)PRIMARYKEY,snameVARCHAR2(10),sexCHAR(2)CHECK(sexIN(M,F),sageNUMBER(4),birthdayDATE)PARTITIONBYRANGE(birthday)SUBPARTITIONBYLIST(sex)(PARTITIONp1VALUESLESSTHAN(TO_DATE(1980-1-1,YYYY-MM-DD)(SUBPARTITIONp1_sub1VALUES(M)TABLESPACEORCLTBS1,SUBPARTITIONp1_sub2VALUES(F)TABLESPACEORCLTBS2),PARTITIONp2VALUESLESSTHAN(TO_DATE(1990-1-1,YYYY-MM-DD)(SUBPARTITIONp2_sub1VALUES(M)TABLESPACEORCLTBS3,SUBPARTITIONp2_sub2VALUES(F)TABLESPACEORCLTBS4),PARTITIONp3VALUESLESSTHAN(MAXVALUE)TABLESPACEORCLTBS5);,范围-散列复合分区范围-散列复合分区先对表进行范围分区，然后再对每个分区进行散列分区，即在一个范围分区中创建多个散列子分区。示例创建一个范围-散列复合分区表，将1980年1月1日前出生的学生信息均匀地保存在ORCLTBS1和ORCLTBS2表空间中，1980年1月1日到1990年1月1日出生的学生信息保存在ORCLTBS3和ORCLTBS4表空间中，其他学生信息保存在ORCLTBS5表空间中。,CREATETABLEstudent_range_hash(snoNUMBER(6)PRIMARYKEY,snameVARCHAR2(10),sageNUMBER(4),birthdayDATE)PARTITIONBYRANGE(birthday)SUBPARTITIONBYHASH(sage)(PARTITIONp1VALUESLESSTHAN(TO_DATE(1980-1-1,YYYY-MM-DD)(SUBPARTITIONp1_sub1TABLESPACEORCLTBS1,SUBPARTITIONp1_sub2TABLESPACEORCLTBS2),PARTITIONp2VALUESLESSTHAN(TO_DATE(1990-1-1,YYYY-MM-DD)(SUBPARTITIONp2_sub1TABLESPACEORCLTBS3,SUBPARTITIONp2_sub2TABLESPACEORCLTBS4),PARTITIONp3VALUESLESSTHAN(MAXVALUE)TABLESPACEORCLTBS5);,.,33,11.3.6增加分区,增加表分区适应于所有的分区表形式，其语法如下:ALTERTABLEtable_nameADDPARTITION.在增加分区时，需要注意以下两点：在最后一个分区之后增加分区，分区值必须大于当前分区中的最大值。如果当前存在MAXVALUE或DEFAULT值的分区，那么在增加分区时会出现错误。这种情况只能采用分隔分区的方法，具体来说是指定SPLITPARTITION子句。,为范围分区表增加分区CREATETABLEstudent_range(snoNUMBER(6)PRIMARYKEY,snameVARCHAR2(10),sageint,birthdayDATE)PARTITIONBYRANGE(birthday)(PARTITIONp1VALUESLESSTHAN(TO_DATE(1980-1-1,YYYY-MM-DD)TABLESPACEORCLTBS1,PARTITIONp2VALUESLESSTHAN(TO_DATE(1990-1-1,YYYY-MM-DD)TABLESPACEORCLTBS2,PARTITIONp3VALUESLESSTHAN(MAXVALUE)TABLESPACEORCLTBS3);（1）在最后一个分区之后增加分区ALTERTABLEstudent_rangeSPLITPARTITIONp3at(TO_DATE(2000-1-1,YYYY-MM-DD)INTO(PARTITIONp3TABLESPACEORCLTBS3,PARTITIONp4TABLESPACEORCLTBS4);,为范围分区表增加分区CREATETABLEstudent_range(snoNUMBER(6)PRIMARYKEY,snameVARCHAR2(10),sageint,birthdayDATE)PARTITIONBYRANGE(birthday)(PARTITIONp1VALUESLESSTHAN(TO_DATE(1980-1-1,YYYY-MM-DD)TABLESPACEORCLTBS1,PARTITIONp2VALUESLESSTHAN(TO_DATE(1990-1-1,YYYY-MM-DD)TABLESPACEORCLTBS2,PARTITIONp3VALUESLESSTHAN(TO_DATE(2000-1-1,YYYY-MM-DD)TABLESPACEORCLTBS3);（1）在最后一个分区之后增加分区ALTERTABLEstudent_rangeADDPARTITIONp4VALUESLESSTHAN(TO_DATE(2020-1-1,YYYY-MM-DD);,为范围分区表增加分区CREATETABLEstudent_range(snoNUMBER(6)PRIMARYKEY,snameVARCHAR2(10),sageint,birthdayDATE)PARTITIONBYRANGE(birthday)(PARTITIONp1VALUESLESSTHAN(TO_DATE(1980-1-1,YYYY-MM-DD)TABLESPACEORCLTBS1,PARTITIONp2VALUESLESSTHAN(TO_DATE(1990-1-1,YYYY-MM-DD)TABLESPACEORCLTBS2,PARTITIONp3VALUESLESSTHAN(TO_DATE(2000-1-1,YYYY-MM-DD)TABLESPACEORCLTBS3);（2）在分区