Oracle对象定义英文版).ppt

Copyright2001GlobalBusinessSolutions,Inc.,DefineDatabaseObjects,Copyright2001GlobalBusinessSolutions,Inc.,DefineDatabaseObjects,OBJECTIVESDefineTablespacesDefineTablesandIndexesDefineSegments,Extents,andBlocks,Copyright2001GlobalBusinessSolutions,Inc.,DefineDatabaseObjectsOverview,Database,Tablespace,Tables,indexes,etc.,Datafiles,Blocks,Copyright2001GlobalBusinessSolutions,Inc.,Tablespaces,LargestlogicalunitofspaceConsistofoneormoredatafilesSYSTEMtablespacemustexist,Tablespace,Copyright2001GlobalBusinessSolutions,Inc.,TypesofTablespaces,System/NonsystemOnline/OfflineRead-Write/Read-OnlyTransportable,Copyright2001GlobalBusinessSolutions,Inc.,SystemandNonsystemTablespaces,SystemAtleastoneDataDictionaryRollbackNonsystemFacilitatesAdministrationUser/ApplicationData,IndexesRollbackandTemporarySegments,Copyright2001GlobalBusinessSolutions,Inc.,OnlineandOfflineTablespaces,OnlineAvailabletoapplicationsTypicallytablespacesonline,OfflineNotavailabletousers,evenifdatabaseisavailableUseformaintenance,troubleshooting,orhistoricaldatanolongerneeded,Online,Offline,Copyright2001GlobalBusinessSolutions,Inc.,Read-WriteandRead-OnlyTablespaces,Read-WriteCreate,alter,anddropdatabaseobjectsDefault,Read-OnlyCannotcreateoralterdatabaseobjectsCandropcertainobjectsCanresideonWormdeviceorCDROMEliminateredundantdatabackupsIdealforDataWarehouseapplicationsandLookuptables,Read-Write,Read-Only,Copyright2001GlobalBusinessSolutions,Inc.,TipsforRead-OnlyTablespaces,OrganizebymodificationrequirementsFrequencyofdatachangeBackuprequirementSecurityrequirementUserrequirementAssociateddatafilecannotbe:ResizedorhaveautoextendsettoonRenamedtoanewdatafilenameordirectoryOffline,Copyright2001GlobalBusinessSolutions,Inc.,TransportableTablespaces,SystemTablespace,TransportableTablespace,SystemTablespace,ExportImport,CopyUtility,TransportableTablespace,SourceDatabase,TargetDatabase,Copyright2001GlobalBusinessSolutions,Inc.,UsesofTransportableTablespaces,ArchiveOLTPanddatawarehousesystemsTransferdatafromOLTPtodatawarehousestagingsystems,LoadingdatamartsfromcentraldatawarehousesPublishdataMoveindexdataandavoidrebuilds,Copyright2001GlobalBusinessSolutions,Inc.,Tables,StoredinTablespacesOrganizedinrowsandcolumnsContaindata,Tablespace,Tables,indexes,etc.,Datafiles,Blocks,Copyright2001GlobalBusinessSolutions,Inc.,TableDatatypes,CHARVARCHAR2NUMBERDATALOB,CustomersNameAddressPhone.George.123Cherry555-5555,Copyright2001GlobalBusinessSolutions,Inc.,LargeObjectDatatype,StoreUnstructuredInformationTextdocumentsStaticimagesVideoAudio,CustomersNameAddress.PhotoGreetingGeorge.123Cherry,Copyright2001GlobalBusinessSolutions,Inc.,LOBTypes,CLOBstorescharacterdataNCLOBstoresdouble-bytecharacterdata,BLOBstoresbinarydata:graphics,video,soundBFILEstorespointerstoexternalfiles,ExternalFileSystem,CustomersNameHistoryPhotoGreetingGeorge,Copyright2001GlobalBusinessSolutions,Inc.,ExternalFileSystem,LOBLocators,LOBdatastoredseparatefromnon-LOBdata,BFILEdatastoredseparatefromdatabase,TablespaceA,TableNameAdd,*(LocatorspointtoLOBandBFILELocations),TablespaceB,Copyright2001GlobalBusinessSolutions,Inc.,TemporaryTables,Storesession/transactionspecificdataShowdataonlytocreatingsession,Discarddataoncommitorpreservedatauntilsessionends,TablespaceA,Temp01,Temp02,Copyright2001GlobalBusinessSolutions,Inc.,CustomersIndex#Name1AcmeInc.2Zacharys3Maries4Chimney,Indexes,OptimizequeriesROWID(Block#.Row#.File#)Storedindependentoftables,ConsumeminimalspaceReduceI/OIndexonWHEREclausecolumns,CustomersTable#NameAddre.3Maries4456Si1AcmeInc.1236Ch4Chimney9024F2Zacharys1234Br,Copyright2001GlobalBusinessSolutions,Inc.,IndexTypes,UniqueDuplicatesnotallowedNon-uniqueDuplicatesallowed,X,Copyright2001GlobalBusinessSolutions,Inc.,IndexTypes,SingleColumn,Copyright2001GlobalBusinessSolutions,Inc.,IndexTypes,Multi-ColumnConcatenated,composite,IndexRowid,OrderNoItemNoCust.,007000001USA007000002GBSI007000003TEST007234005WEST006989001ABC006989002XYZ,ORDERTABLE,Copyright2001GlobalBusinessSolutions,Inc.,BalancedIndexTree(B*Tree),ROOT,BRANCH,ROWIDPOINTER,DATASEGMENT,0,000020004000,00002000,200124993000,300140014999,500070009000,50005999,700072008100,90009999,ROWID|1/4”RolledSheet|100Feet|5Days|C.O.D.,LEAF,Copyright2001GlobalBusinessSolutions,Inc.,BitmapIndex,BitmapcreatedforeachcolumnvalueCommonUsageVeryLargeTablesLow-CardinalityColumnsFeaturesRequiresminimalstorageImprovesperformance,F,BILLLISAANNTED,10010110,M,GENDER,Copyright2001GlobalBusinessSolutions,Inc.,FunctionBasedIndex,CreateonfunctionsorexpressionsUsergeneratedPL/SQLSQLfunctions(non-aggregate)Externalprogram(Ccallout),600500300250900,660550330275990,SALARY*1.10,SALARY,Function-Based,Single,SQLSELECTsalary,salary*1.10FROMemployees;,Copyright2001GlobalBusinessSolutions,Inc.,PartitionData,Decomposetablesandindexes,Eachpartitioncanbestoredindifferenttablespace,CustomerCustNoCustNameState1KodakNYOracleCAUSSteelPA6QuestWA7MotorolaTX,CustomersALIN,CustomersKSNY,CustomersOHWY,Copyright2001GlobalBusinessSolutions,Inc.,AdvantagesofPartitions,ReducetimefordataloadsandindexcreationIncreaseavailabilityofmission-criticaldatabasesCreatepartitionindependenceEnableconcurrentoperationsonseparatepartitionsExecutequeriesonpartitionsinsteadoffulltablescan,Copyright2001GlobalBusinessSolutions,Inc.,AdvantagesofPartitions,ControldatastorageacrossphysicaldevicesMaintainpartitiontransparencyProgramsdonotneedtoreferencepartitionsProgramscantakeadvantageofpartitions,Copyright2001GlobalBusinessSolutions,Inc.,Equipartitions,Databaseobjectshavesamelogicalpartitions,ExamplesMasteranddetailtableTableandindex,OrderTable,ItemTable,CustomerTable,CustomerbyState,Copyright2001GlobalBusinessSolutions,Inc.,GlobalPartitionedIndexes,CanimproveperformanceCancontainkeysfromallpartitionsofcorrespondingtable,CustomerSalesIndex,CustomersALIN,CustomersKSNY,CustomersOHWY,Copyright2001GlobalBusinessSolutions,Inc.,IndexOrganizedTables,DatastoredwithB*TreeindexTablemusthaveprimarykey,whichactsasindex,AppropriateforcomplexorunstructureddataSpatialOLAP,Copyright2001GlobalBusinessSolutions,Inc.,TablespaceandDatafileRelationship,Tablespace,Tables,indexes,etc.,Datafiles,Segment,Extent,Atablespaceiscreatedwithoneormoredatafiles,Datafilesaremadeupofblocks,Blocks,Copyright2001GlobalBusinessSolutions,Inc.,StorageObjectandSegmentRelationship,Createastorageobjectintablespace,Tables,indexes,etc.arepreallocatedtosegments,SegmentMySegment,Blocks,Copyright2001GlobalBusinessSolutions,Inc.,DatabaseSegments,Setofextentscontainingspecifictypesofdata,BootstrapSegment,TemporarySegment,RollbackSegment,IndexSegment,DataSegment,Copyright2001GlobalBusinessSolutions,Inc.,DataSegments,StoresrowsforTablesTablefeaturesContainsuserdataContainsDataDictionarydataRowFeaturesContainunlimitednumberofrowsSpanblocks(chaining)Columnsstoredsidebyside,BootstrapSegment,RollbackSegment,IndexSegment,DataSegment,Copyright2001GlobalBusinessSolutions,Inc.,IndexSegments,CreatedforeveryindexExistasseparatephysicalsegmentsfromdatasegmentUsuallymuchsmallerthanassociateddatasegment,BootstrapSegment,RollbackSegment,IndexSegment,IndexSegment,Copyright2001GlobalBusinessSolutions,Inc.,RollbackSegments,RecordbeforeimageofmodifieddataAllowchangestobeundonebeforecommitSupportreadconsistencySupportdataconcurrencySupportdatabaserecoveryASYSTEMrollbacksegmentisrequired,BootstrapSegment,RollbackSegment,IndexSegment,RollbackSegment,Copyright2001GlobalBusinessSolutions,Inc.,RollbackSegmentTypes,PrivateMustincludesegmentnameinROLLBACK_SEGMENTSparameterPublicApoolofrollbacksegmentsthatmaybeacquiredbyanyinstancemountingdatabaseDeferredCreatedinSYSTEMtablespacewhentablespacetakenofflineandtransactionscannotberolledback,Copyright2001GlobalBusinessSolutions,Inc.,RollbackSegmentUsage,UndodataiswrittentosegmentEachextentsupportsmultipletransactionsEachtransactionwritestoitsownblock(s)Ifextentfills,automaticallyextendsIfallextentsfill,segmentwrapsIfsegmentwraps,undodataoverwrittenIfundodatanotavailable,erroroccurs,“Snapshottooold”“Cannotallocateextent”,Copyright2001GlobalBusinessSolutions,Inc.,ReadConsistency,Whenqueryexecutes:CurrenttimedeterminedBlockswithsametimereadNewerblocksreconstructedwithrollbacksegments,Customers#NameAddress1AcmeInc.123Ch2Zacharys1234Br3Maries4456Si4Chimney9024F,SELECT*FROMcustomer,UPDATEcustomer.,Copyright2001GlobalBusinessSolutions,Inc.,TemporarySegments,ProvidetemporaryworkspaceCreatedautomaticallyforjoins,indexes,sortsCreatedondiskwhenmemoryfullSizedbyDEFAULTSTORAGEclauseoftablespaceReclaimedbySMON,BootstrapSegment,RollbackSegment,IndexSegment,TemporarySegment,Copyright2001GlobalBusinessSolutions,Inc.,BootstrapSegment,ContainsdictionarydefinitionsloadedonopenIsnotuseraccessibleResidesinSYSTEMtablespaceRequiresnoDBAmaintenance,BootstrapSegment,RollbackSegment,IndexSegment,Bootstrap