Oracle9i模式对象

11Oracle9i模式和模式对象22表（table）表（table）为数据库中数据存储的基本单位，其数据按行、列存储。每个表具有一表名和列的集合。每一列有一个列名、数据类型、宽度或精度、比例。一行是对应单个记录的列信息的集合。33不同类型的表RegulartableClusterPartitionedtableIndex-organizedtable44创建表CREATETABLEemployees(empnoNUMBER(4),last_nameVARCHAR2(30)deptnoNUMBER(2))PCTFREE20PCTUSED50STORAGE(INITIAL200KNEXT200KPCTINCREASE0MAXEXTENTS50)TABLESPACEdata01;55删除列SQLaltertablelunartest2dropcolumnOBJECT_NAME;表已更改。SQL或者SQLaltertablelunartest2drop(SUBOBJECT_NAME,OBJECT_ID);表已更改。SQL66将列标志为“未使用（UNUSED）”SQLALTERTABLElunartestSETUNUSED(DATA_OBJECT_ID);表已更改。SQLdesclunartest;或者SQLALTERTABLElunartestSETUNUSED(OBJECT_TYPE,CREATED);表已更改。SQL77删除未使用的列SQLALTERTABLElunartestDROPUNUSEDCOLUMNS;表已更改。SQL或者SQLALTERTABLElunartestDROPUNUSEDCOLUMNSCHECKPOINT20;表已更改。SQL88并行创建（复制）表CREATETABLEtestSTORAGE(INITIAL200KNEXT200KPCTINCREASE0MAXEXTENTS50)NOLOGGINGPARALLELTABLESPACEUSERSASSELECT*FROMUSER_OBJECTS;99何时能够并行执行？Create部分：•在CREATETABLEΛASSELECT语句中包含了PARALLEL子句•指定了ALTERSESSIONFORCEPARALLELDDL语句Select部分：•查询中包含了一个并行提示说明PARALLEL，或CREATE部分包含PARALLEL•至少在一个查询中指定的表需要全表扫描或索引范围扫描跨越多个分区1010删除约束–使用下属命令删除约束:用下属命令删除表及外键ALTERTABLEtestDROPCONSTRAINTtest_ln_uk;DROPTABLEtestCASCADECONSTRAINTS;1111创建临时表•CREATEGLOBALTEMPORARYTABLE•所有会话都可以看到临时表的定义，但只有将数据插入该表中的会话才能看见临时表中的数据•ONCOMMITDELETEROWS子句(缺省)指出，每次事务提交后舍弃表（删除所有行）•ONCOMMITPRESERVEROWS子句指出，结束会话时舍弃表（删除所有行）1212FreespaceafterdeleteUnusedblockHighwatermarkExtentID01234ExtentID01234UsedblockHighWaterMarkAfterinsertsAfterdeletes1313找出HighWaterMark:ExtentID01234HighwatermarkLAST_USED_EXTENT_FILE_ID,LAST_USED_EXTENT_BLOCK_IDTOTAL_BLOCKSUNUSED_BLOCKSDBMS_SPACE.UNUSED_SPACE1414FreespaceafterdeleteUnusedblockHighwatermark释放之前Usedblock释放未使用的空间ALTERTABLEscott.employeesDEALLOCATEUNUSED;释放之后012341515清空表TRUNCATETABLEscott.employees;ExtentID01HighwatermarkFreespace1616删除表DROPTABLEscott.departmentsCASCADECONSTRAINTS;1717检索表的信息DBA_OBJECTSOWNEROBJECT_NAMEOBJECT_IDDATA_OBJECT_IDCREATEDDBA_SEGMENTSOWNERSEGMENT_NAMETABLESPACE_NAMEHEADER_FILEHEADER_BLOCKDBA_TABLESOWNERTABLE_NAMEPCT_FREEPCT_USEDINITIAL_EXTENTNEXT_EXTENTMIN_EXTENTSMAX_EXTENTSPCT_INCREASECACHEBLOCKSEMPTY_BLOCKSCHAIN_CNT1818视图（view)•一个视图（view)是由一个或多个表（或其他视图）中的数据的一种定制的表示，是用一个查询定义。•视图不分配任何存储空间，视图不真正地包含数据•视图的优点：通过限制对表的行预定义集合的存取，为表提供附加的安全性隐藏数据复杂性。为用户简化命令为基本表的数据提供另一种观点。可将应用隔离基本表定义的修改用于不用视图无法表示的查询。可用于保存复杂查询。1919聚集（cluster）一个聚集是一组表，将具有同一公共列值的行存储在一起，并且它们经常一起使用。这些公共列构成聚集码。2020索引(index)•索引是为提高数据检索的性能而建立•索引可建立在一表的一列或多列上2121索引分类逻辑上–单列或组合索引–唯一非唯一索引注意：组合索引最多可以为32个列，并且，所有列的大小不能超过块的1/3物理上–分区或非分区–B树–正常或反向键–位图2222DML操作对索引的影响•插入操作导致在适当的块中插入索引项•删除行只导致逻辑删除索引项，删除的行所占用的空间不能用于新项，直到删除块中的所有项•PCTFREE对索引没有影响232323索引行格式•每个索引行有唯一的表示•createuniqueindexinameontname(a,b,c)•createindexinameontname(a,b,c)Label：存储列数和锁定信息abcrowidlabelabcrowidlabel2424KEYROWIDEMPNO(BLOCK#ROW#FILE#)------------------------12570000000F.0002.000128770000000F.0006.000145670000000F.0004.000166570000000F.0003.000189670000000F.0005.000196370000000F.0001.000199470000000F.0000.0001............逆向键值索引IndexonEMP(EMPNO)EMPtableEMPNOENAMEJOB...------------------7499ALLENSALESMAN7369SMITHCLERK7521WARDSALESMAN...7566JONESMANAGER7654MARTINSALESMAN7698BLAKEMANAGER7782CLARKMANAGER........................252525Bitmap索引row1234NameJoeJaneJohnJamesM_StatusSingleMarriedDivorcedMarriedStatePACACAPAGenderMFMMM_Status-IDXSingleMarriedDivorcedState-IDXCAPAGender-IDXMF1000010000100110100110110100Selectcount(*)fromcustomerswhereM_Status=marriedANDState=CAANDGender=M2626更适合Bitmap的情况•当表很大（如数百万行），且关键字列的基数低（如‘性别’列）时，即该列有非常少的独特值例•当查询经常使用涉及OR运算符的多个WHERE条件组合时•当关键字列上存在只读或很少的更新操作时2727B树索引和位图索引B树•适合高基数的列•更新关键字列的费用相对较低•使用OR谓词的查询效率低•对OLTP有用位图•适合低基数的列•更新关键字列的费用非常昂贵•使用OR谓词的查询效率高•对数据仓库有用2828建立通常的B-Tree索引CREATE[UNIQUE]INDEX[schema.]indexON[schema.]table(column[ASC|DESC][,column[ASC|DESC]]...)[TABLESPACEtablespace][PCTFREEinteger][INITRANSinteger][MAXTRANSinteger][storage-clause][LOGGING|NOLOGGING][NOSORT]2929索引和空值•B*Tree索引不存储完全NULL(空)的条目，但是位图索引和聚族索引存储完全NULL(空)的条目。•oracle中除了groupby会认为(NULL,NULL)=(NULL,NULL)，其他任何情况下的空值条目比较都视为不同的空值，即(NULL,NULL)(NULL,NULL)•为了保证真正的唯一性，每个唯一约束（uniqueindex）应该至少有一个NOTNULL字段。3030索引和视图如果基本表中定义了索引，一般情况下还是应该想办法用到那些索引3131一些子句说明•INITRANS指定每块中预先分配的事务项的数目，缺省值和最小值为2•MAXTRANS限制分配给每块的事务项的数目，缺省值为2553232建立B-TREE的例子CREATEINDEXtest_idxONtest(a)PCTFREE30STORAGE(INITIAL200KNEXT200KPCTINCREASE0MAXEXTENTS50)TABLESPACEindx;3333建立索引的原则–平衡查询和DML需求–放在一个单独的表空间内（非SYSTEM,RBS,TABLE）–使用统一的区大小(经验值：五块的倍数或表空间MININUMEXTENT大小的倍数）–考虑对大索引使用NOLOGGING–如果新的关键字值有可能位于当前范围内,则设置高PCTFREE3434建立逆向索引CREATE[UNIQUE]INDEX[schema.]indexON[schema.]table(column[ASC|DESC][,column[ASC|DESC]]...)[TABLESPACEtablespace][PCTFREEinteger][INITRANSinteger][MAXTRANSinteger][storage-clause][LOGGING|NOLOGGING]REVERSE（注意，这里不能使用nosort）3535建立逆向索引的例子SQLCREATEUNIQUEINDEXtest_rsidx2ONtest(b)REVERSE3PCTFREE304STORAGE(INITIAL200KNEXT200K5PCTINCREASE0MAXEXTENTS50)6TABLESPACEindx;索引已创建。SQL3636建立Bitmap索引CREATEBITMAPINDEX[schema.]indexON[schema.]table(column[ASC|DESC][,column[ASC|DESC]]...)[TABLESPACEtablespace][PCTFREEinteger][INITRANSinteger][MAXTRANSinteger][storage-clause][LOGGING|NOLOGGING][NOSORT]注意，Bitmap不能