0Oracle 对象定义-概念++.ppt

DefineDatabaseObjects DefineDatabaseObjects OBJECTIVESDefineTablespacesDefineTablesandIndexesDefineSegments Extents andBlocks DefineDatabaseObjectsOverview Database Tablespace Tables indexes etc Datafiles Blocks Tablespaces LargestlogicalunitofspaceConsistofoneormoredatafilesSYSTEMtablespacemustexist Tablespace TypesofTablespaces System NonsystemOnline OfflineRead Write Read OnlyTransportable SystemandNonsystemTablespaces SystemAtleastoneDataDictionaryRollbackNonsystemFacilitatesAdministrationUser ApplicationData IndexesRollbackandTemporarySegments OnlineandOfflineTablespaces OnlineAvailabletoapplicationsTypicallytablespacesonline OfflineNotavailabletousers evenifdatabaseisavailableUseformaintenance troubleshooting orhistoricaldatanolongerneeded Online Offline Read WriteandRead OnlyTablespaces Read WriteCreate alter anddropdatabaseobjectsDefault Read OnlyCannotcreateoralterdatabaseobjectsCandropcertainobjectsCanresideonWormdeviceorCDROMEliminateredundantdatabackupsIdealforDataWarehouseapplicationsandLookuptables Read Write Read Only TipsforRead OnlyTablespaces OrganizebymodificationrequirementsFrequencyofdatachangeBackuprequirementSecurityrequirementUserrequirementAssociateddatafilecannotbe ResizedorhaveautoextendsettoonRenamedtoanewdatafilenameordirectoryOffline TransportableTablespaces SystemTablespace TransportableTablespace SystemTablespace ExportImport CopyUtility TransportableTablespace SourceDatabase TargetDatabase UsesofTransportableTablespaces ArchiveOLTPanddatawarehousesystemsTransferdatafromOLTPtodatawarehousestagingsystems LoadingdatamartsfromcentraldatawarehousesPublishdataMoveindexdataandavoidrebuilds Tables StoredinTablespacesOrganizedinrowsandcolumnsContaindata Tablespace Tables indexes etc Datafiles Blocks TableDatatypes CHARVARCHAR2NUMBERDATALOB CustomersNameAddressPhone George 123Cherry555 5555 LargeObjectDatatype StoreUnstructuredInformationTextdocumentsStaticimagesVideoAudio CustomersNameAddress PhotoGreetingGeorge 123Cherry LOBTypes CLOBstorescharacterdataNCLOBstoresdouble bytecharacterdata BLOBstoresbinarydata graphics video soundBFILEstorespointerstoexternalfiles ExternalFileSystem CustomersName HistoryPhotoGreetingGeorge ExternalFileSystem LOBLocators LOBdatastoredseparatefromnon LOBdata BFILEdatastoredseparatefromdatabase TablespaceA TableNameAdd LocatorspointtoLOBandBFILELocations TablespaceB TemporaryTables Storesession transactionspecificdataShowdataonlytocreatingsession Discarddataoncommitorpreservedatauntilsessionends TablespaceA Temp01 Temp02 CustomersIndex Name1AcmeInc 2Zachary s3Marie s4Chimney Indexes OptimizequeriesROWID Block Row File Storedindependentoftables ConsumeminimalspaceReduceI OIndexonWHEREclausecolumns CustomersTable NameAddre 3Marie s4456Si1AcmeInc 1236Ch4Chimney9024F2Zachary s1234Br IndexTypes UniqueDuplicatesnotallowedNon uniqueDuplicatesallowed X IndexTypes SingleColumn IndexTypes Multi ColumnConcatenated composite IndexRowid OrderNoItemNoCust 007000001USA007000002GBSI007000003TEST007234005WEST006989001ABC006989002XYZ ORDERTABLE 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 BitmapIndex BitmapcreatedforeachcolumnvalueCommonUsageVeryLargeTablesLow CardinalityColumnsFeaturesRequiresminimalstorageImprovesperformance F BILLLISAANNTED 10010110 M GENDER FunctionBasedIndex CreateonfunctionsorexpressionsUsergeneratedPL SQLSQLfunctions non aggregate Externalprogram Ccallout 600500300250900 660550330275990 SALARY 1 10 SALARY Function Based Single SQL SELECTsalary salary 1 10FROMemployees PartitionData Decomposetablesandindexes Eachpartitioncanbestoredindifferenttablespace CustomerCustNoCustNameState 1KodakNYOracleCAUSSteelPA6QuestWA7MotorolaTX CustomersAL IN CustomersKS NY CustomersOH WY AdvantagesofPartitions ReducetimefordataloadsandindexcreationIncreaseavailabilityofmission criticaldatabasesCreatepartitionindependenceEnableconcurrentoperationsonseparatepartitionsExecutequeriesonpartitionsinsteadoffulltablescan AdvantagesofPartitions ControldatastorageacrossphysicaldevicesMaintainpartitiontransparencyProgramsdonotneedtoreferencepartitionsProgramscantakeadvantageofpartitions Equipartitions Databaseobjectshavesamelogicalpartitions ExamplesMasteranddetailtableTableandindex OrderTable ItemTable CustomerTable CustomerbyState GlobalPartitionedIndexes CanimproveperformanceCancontainkeysfromallpartitionsofcorrespondingtable CustomerSalesIndex CustomersAL IN CustomersKS NY CustomersOH WY IndexOrganizedTables DatastoredwithB TreeindexTablemusthaveprimarykey whichactsasindex AppropriateforcomplexorunstructureddataSpatialOLAP TablespaceandDatafileRelationship Tablespace Tables indexes etc Datafiles Segment Extent Atablespaceiscreatedwithoneormoredatafiles Datafilesaremadeupofblocks Blocks StorageObjectandSegmentRelationship Createastorageobjectintablespace Tables indexes etc arepreallocatedtosegments SegmentMySegment Blocks DatabaseSegments Setofextentscontainingspecifictypesofdata BootstrapSegment TemporarySegment RollbackSegment IndexSegment DataSegment DataSegments StoresrowsforTablesTablefeaturesContainsuserdataContainsDataDictionarydataRowFeaturesContainunlimitednumberofrowsSpanblocks chaining Columnsstoredsidebyside BootstrapSegment RollbackSegment IndexSegment DataSegment IndexSegments CreatedforeveryindexExistasseparatephysicalsegmentsfromdatasegmentUsuallymuchsmallerthanassociateddatasegment BootstrapSegment RollbackSegment IndexSegment IndexSegment RollbackSegments RecordbeforeimageofmodifieddataAllowchangestobeundonebeforecommitSupportreadconsistencySupportdataconcurrencySupportdatabaserecoveryASYSTEMrollbacksegmentisrequired BootstrapSegment RollbackSegment IndexSegment RollbackSegment RollbackSegmentTypes PrivateMustincludesegmentnameinROLLBACK SEGMENTSparameterPublicApoolofrollbacksegmentsthatmaybeacquiredbyanyinstancemountingdatabaseDeferredCreatedinSYSTEMtablespacewhentablespacetakenofflineandtransactionscannotberolledback RollbackSegmentUsage UndodataiswrittentosegmentEachextentsupportsmultipletransactionsEachtransactionwritestoitsownblock s Ifextentfills automaticallyextendsIfallextentsfill segmentwrapsIfsegmentwraps undodataoverwrittenIfundodatanotavailable erroroccurs Snapshottooold Cannotallocateextent ReadConsistency Whenqueryexecutes CurrenttimedeterminedBlockswithsametimereadNewerblocksreconstructedwithrollbacksegments Customers NameAddress 1AcmeInc 123Ch2Zachary s1234Br3Marie s4456Si4Chimney9024F SELECT FROMcustomer UPDATEcustomer TemporarySegments ProvidetemporaryworkspaceCreatedautomaticallyforjoins indexes sortsCreatedondiskwhenmemoryfullSizedbyDEFAULTSTORAGEclauseoftablespaceReclaimedbySMON BootstrapSegment RollbackSegment IndexSegment TemporarySegment BootstrapSegment ContainsdictionarydefinitionsloadedonopenIsnotuseraccessibleResidesinSYSTEMtablespaceRequiresnoDBAmaintenance BootstrapSegment RollbackSegment IndexSegment BootstrapSegment DatabaseExtent