版权说明：本文档由用户提供并上传，收益归属内容提供方，若内容存在侵权，请进行举报或认领
文档简介
DefineDatabaseObjectsDefineDatabaseObjectsOBJECTIVESDefineTablespacesDefineTablesandIndexesDefineSegments,Extents,andBlocksDefineDatabaseObjectsOverviewDatabaseTablespaceTables,indexes,etc.DatafilesSegmentExtentBlocksTablespacesLargestlogicalunitofspaceConsistofoneormoredatafilesSYSTEMtablespacemustexistTablespaceTypesofTablespacesSystem/NonsystemOnline/OfflineRead-Write/Read-OnlyTransportableSystemandNonsystemTablespacesSystemAtleastoneDataDictionaryRollbackNonsystemFacilitatesAdministrationUser/ApplicationData,IndexesRollbackandTemporarySegmentsOnlineandOfflineTablespacesOnlineAvailabletoapplicationsTypicallytablespacesonlineOfflineNotavailabletousers,evenifdatabaseisavailableUseformaintenance,troubleshooting,orhistoricaldatanolongerneededOnlineOfflineRead-WriteandRead-OnlyTablespacesRead-WriteCreate,alter,anddropdatabaseobjectsDefaultRead-OnlyCannotcreateoralterdatabaseobjectsCandropcertainobjectsCanresideonWormdeviceorCDROMEliminateredundantdatabackupsIdealforDataWarehouseapplicationsandLookuptablesRead-WriteRead-OnlyTipsforRead-OnlyTablespacesOrganizebymodificationrequirementsFrequencyofdatachangeBackuprequirementSecurityrequirementUserrequirementAssociateddatafilecannotbe:ResizedorhaveautoextendsettoonRenamedtoanewdatafilenameordirectoryOfflineTransportableTablespacesSystemTablespaceTransportableTablespaceSystemTablespaceExportImportCopyUtilityTransportableTablespaceSourceDatabaseTargetDatabaseUsesofTransportableTablespacesArchiveOLTPanddatawarehousesystemsTransferdatafromOLTPtodatawarehousestagingsystemsLoadingdatamartsfromcentraldatawarehousesPublishdataMoveindexdataandavoidrebuildsTablesStoredinTablespacesOrganizedinrowsandcolumnsContaindataTablespaceTables,indexes,etc.DatafilesSegmentExtentBlocksTableDatatypesCHARVARCHAR2NUMBERDATALOBCustomersNameAddressPhone…..George..123Cherry555-5555LargeObjectDatatypeStoreUnstructuredInformationTextdocumentsStaticimagesVideoAudioCustomersNameAddress...PhotoGreetingGeorge..123CherryLOBTypesCLOBstorescharacterdataNCLOBstoresdouble-bytecharacterdataBLOBstoresbinarydata:graphics,video,soundBFILEstorespointerstoexternalfilesExternalFileSystemCustomersName…HistoryPhotoGreetingGeorgeExternalFileSystemLOBLocatorsLOBdatastoredseparatefromnon-LOBdataBFILEdatastoredseparatefromdatabaseTablespaceATableNameAdd…*(LocatorspointtoLOBandBFILELocations)TablespaceBTemporaryTablesStoresession/transactionspecificdataShowdataonlytocreatingsessionDiscarddataoncommitorpreservedatauntilsessionendsTablespaceATemp01Temp02CustomersIndex#Name1AcmeInc.2Zachary’s3Marie’s4ChimneyIndexesOptimizequeriesROWID(Block#.Row#.File#)StoredindependentoftablesConsumeminimalspaceReduceI/OIndexonWHEREclausecolumnsCustomersTable#NameAddre..3Marie’s4456Si1AcmeInc.1236Ch4Chimney9024F2Zachary’’s1234BrIndexTypesUniqueDuplicatesnotallowedNon-uniqueDuplicatesallowedXIndexTypesSingleColumnIndex RowidOrderNo Block#.Seq#.File#007000 0000A.0004.0003007000 0000A.0005.0003OrderNoItemNoCust.006989 001 ABC006989 002 XYZ006989 003 USA007000 001 USA007000 002 GBSI007000 003 TEST007000 004 XYZ007234 005 WESTORDERTABLEIndexTypesMulti-ColumnConcatenated,compositeIndexRowidOrderNoItemNoBlock#.Seq#.File#006989 001 0000A .0005 .0003006989 002 0000A .0006 .0003OrderNoItemNoCust.007000001USA007000002GBSI007000003TEST007234005WEST006989001ABC006989002XYZORDERTABLEBalancedIndexTree(B*Tree)ROOTBRANCHROWIDPOINTERDATASEGMENT0000020004000000020002001249930003001400149995000700090005000599970007200810090009999ROWID|1/4””RolledSheet|100Feet|5Days|C.O.D.LEAFBitmapIndexBitmapcreatedforeachcolumnvalueCommonUsageVeryLargeTablesLow-CardinalityColumnsFeaturesRequiresminimalstorageImprovesperformance‘F’BILLLISAANNTED10010110‘M’GENDERFunctionBasedIndexCreateonfunctionsorexpressionsUsergeneratedPL/SQLSQLfunctions(non-aggregate)Externalprogram(Ccallout)600500300250900660550330275990SALARY*1.10SALARYFunction-BasedSingleSQL>SELECTsalary,salary*1.10FROMemployees;PartitionDataDecomposetablesandindexesEachpartitioncanbestoredindifferenttablespaceCustomerCustNoCustNameState…1KodakNYOracleCAUSSteelPA6QuestWA7MotorolaTXCustomersAL……INCustomersKS……NYCustomersOH……WYAdvantagesofPartitionsReducetimefordataloadsandindexcreationIncreaseavailabilityofmission-criticaldatabasesCreatepartitionindependenceEnableconcurrentoperationsonseparatepartitionsExecutequeriesonpartitionsinsteadoffulltablescanAdvantagesofPartitionsControldatastorageacrossphysicaldevicesMaintainpartitiontransparencyProgramsdonotneedtoreferencepartitionsProgramscantakeadvantageofpartitionsEquipartitionsDatabaseobjectshavesamelogicalpartitionsExamplesMasteranddetailtableTableandindexOrderTableItemTableCustomerTableCustomerbyStateGlobalPartitionedIndexesCanimproveperformanceCancontainkeysfromallpartitionsofcorrespondingtableCustomerSalesIndexCustomersAL……INCustomersKS……NYCustomersOH……WYIndexOrganizedTablesDatastoredwithB*TreeindexTablemusthaveprimarykey,whichactsasindexAppropriateforcomplexorunstructureddataSpatialOLAPDataDataData90009999DataDataDataDataDataData3001400149990000020004000DataDataData5000700090005000799900002999TablespaceandDatafileRelationshipTablespaceTables,indexes,etc.DatafilesSegmentExtentAtablespaceiscreatedwithoneormoredatafilesDatafilesaremadeupofblocksBlocksStorageObjectandSegmentRelationshipCreateastorageobjectintablespaceTables,indexes,etc.arepreallocatedtosegmentsSegmentExtentSegmentMySegmentBlocksDatabaseSegmentsSetofextentscontainingspecifictypesofdataBootstrapSegmentTemporarySegmentRollbackSegmentIndexSegmentDataSegmentExtentExtentExtentDataSegmentsStoresrowsforTablesTablefeaturesContainsuserdataContainsDataDictionarydataRowFeaturesContainunlimitednumberofrowsSpanblocks(chaining)ColumnsstoredsidebysideBootstrapSegmentRollbackSegmentIndexSegmentDataSegmentIndexSegmentsCreatedforeveryindexExistasseparatephysicalsegmentsfromdatasegmentUsuallymuchsmallerthanassociateddatasegmentBootstrapSegmentRollbackSegmentIndexSegmentIndexSegmentRollbackSegmentsRecordbeforeimageofmodifieddataAllowchangestobeundonebeforecommitSupportreadconsistencySupportdataconcurrencySupportdatabaserecoveryASYSTEMrollbacksegmentisrequiredBootstrapSegmentRollbackSegmentIndexSegmentRollbackSegmentRollbackSegmentTypesPrivateMustincludesegmentnameinROLLBACK_SEGMENTSparameterPublicApoolofrollbacksegmentsthatmaybeacquiredbyanyinstancemountingdatabaseDeferredCreatedinSYSTEMtablespacewhentablespacetakenofflineandtransactionscannotberolledbackRollbackSegmentUsageUndodataiswrittentosegmentEachextentsupportsmultipletransactionsEachtransactionwritestoitsownblock(s)Ifextentfills,automaticallyextendsIfallextentsfill,segmentwrapsIfsegmentwraps,undodataoverwrittenIfundodatanotavailable,erroroccurs“Snapshottooold””“Cannotallocateextent”ReadConsistencyWhenqueryexecutes:CurrenttimedeterminedBlockswithsametimereadNewerblocksreconstructedwithrollbacksegmentsCustomers#NameAddress…1AcmeInc.123Ch2Zachary’s1234Br3Marie’s4456Si4Chimney9024FSELECT*FROMcustomerUPDATEcustomer...TemporarySegmentsProvidetemporaryworkspaceCreatedautomaticallyforjoins,indexes,sortsCreatedondiskwhenmemoryfullSizedbyDEFAULTSTORAGEclauseoftablespaceReclaimedbySMONBootstrapSegmentRollbackSegmentIndexSegmentTemporarySegmentBootstrapSegmentContainsdictionarydefinitionsloadedonopenIsnotuseraccessibleResidesinSYSTEMtablespaceRequiresnoDBAmaintenanceBootstrapSegmentRollbackSegmentIndexSegmentBootstrapSegmentDatabaseExtents
温馨提示
- 1. 本站所有资源如无特殊说明，都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等，如果需要附件，请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸，网页内容里面会有图纸预览，若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间，仅对用户上传内容的表现方式做保护处理，对用户上传分享的文档内容本身不做任何修改或编辑，并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容，请与我们联系，我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
评论
0/150
提交评论