Oracle对象定义(英文版)

DefineDatabaseObjectsCopyright©2001GlobalBusinessSolutions,Inc.DefineDatabaseObjectsOBJECTIVESDefineTablespacesDefineTablesandIndexesDefineSegments,Extents,andBlocksCopyright©2001GlobalBusinessSolutions,Inc.DefineDatabaseObjectsOverviewDatabaseTablespaceTables,indexes,etc.DatafilesSegmentExtentBlocksCopyright©2001GlobalBusinessSolutions,Inc.TablespacesLargestlogicalunitofspaceConsistofoneormoredatafilesSYSTEMtablespacemustexistTablespaceCopyright©2001GlobalBusinessSolutions,Inc.TypesofTablespacesSystem/NonsystemOnline/OfflineRead-Write/Read-OnlyTransportableCopyright©2001GlobalBusinessSolutions,Inc.SystemandNonsystemTablespacesSystemAtleastoneDataDictionaryRollbackNonsystemFacilitatesAdministrationUser/ApplicationData,IndexesRollbackandTemporarySegmentsCopyright©2001GlobalBusinessSolutions,Inc.OnlineandOfflineTablespacesOnlineAvailabletoapplicationsTypicallytablespacesonlineOfflineNotavailabletousers,evenifdatabaseisavailableUseformaintenance,troubleshooting,orhistoricaldatanolongerneededOnlineOfflineCopyright©2001GlobalBusinessSolutions,Inc.Read-WriteandRead-OnlyTablespacesRead-WriteCreate,alter,anddropdatabaseobjectsDefaultRead-OnlyCannotcreateoralterdatabaseobjectsCandropcertainobjectsCanresideonWormdeviceorCDROMEliminateredundantdatabackupsIdealforDataWarehouseapplicationsandLookuptablesRead-WriteRead-OnlyCopyright©2001GlobalBusinessSolutions,Inc.TipsforRead-OnlyTablespacesOrganizebymodificationrequirementsFrequencyofdatachangeBackuprequirementSecurityrequirementUserrequirementAssociateddatafilecannotbe:ResizedorhaveautoextendsettoonRenamedtoanewdatafilenameordirectoryOfflineCopyright©2001GlobalBusinessSolutions,Inc.TransportableTablespacesSystemTablespaceTransportableTablespaceSystemTablespaceExportImportCopyUtilityTransportableTablespaceSourceDatabaseTargetDatabaseCopyright©2001GlobalBusinessSolutions,Inc.UsesofTransportableTablespacesArchiveOLTPanddatawarehousesystemsTransferdatafromOLTPtodatawarehousestagingsystemsLoadingdatamartsfromcentraldatawarehousesPublishdataMoveindexdataandavoidrebuildsCopyright©2001GlobalBusinessSolutions,Inc.TablesStoredinTablespacesOrganizedinrowsandcolumnsContaindataTablespaceTables,indexes,etc.DatafilesSegmentExtentBlocksCopyright©2001GlobalBusinessSolutions,Inc.TableDatatypesCHARVARCHAR2NUMBERDATALOBCustomersName

Address

Phone…..George..123Cherry555-5555Copyright©2001GlobalBusinessSolutions,Inc.LargeObjectDatatypeStoreUnstructuredInformationTextdocumentsStaticimagesVideoAudioCustomersNameAddress...PhotoGreetingGeorge..123Cherry

Copyright©2001GlobalBusinessSolutions,Inc.LOBTypesCLOBstorescharacterdataNCLOBstoresdouble-bytecharacterdataBLOBstoresbinarydata:graphics,video,soundBFILEstorespointerstoexternalfilesExternalFileSystemCustomersName…History

Photo

GreetingGeorge

Copyright©2001GlobalBusinessSolutions,Inc.ExternalFileSystemLOBLocatorsLOBdatastoredseparatefromnon-LOBdataBFILEdatastoredseparatefromdatabaseTablespaceA

TableName

Add…

*(LocatorspointtoLOBandBFILELocations)TablespaceB

Copyright©2001GlobalBusinessSolutions,Inc.TemporaryTablesStoresession/transactionspecificdataShowdataonlytocreatingsessionDiscarddataoncommitorpreservedatauntilsessionendsTablespaceA

Temp01

Temp02Copyright©2001GlobalBusinessSolutions,Inc.CustomersIndex#Name

1AcmeInc.2Zachary’s3Marie’s4ChimneyIndexesOptimizequeriesROWID(Block#.Row#.File#)StoredindependentoftablesConsumeminimalspaceReduceI/OIndexonWHEREclausecolumnsCustomersTable#NameAddre..3Marie’s 4456Si1AcmeInc. 1236Ch4Chimney 9024F2Zachary’s 1234BrCopyright©2001GlobalBusinessSolutions,Inc.IndexTypesUniqueDuplicatesnotallowedNon-uniqueDuplicatesallowedXCopyright©2001GlobalBusinessSolutions,Inc.IndexTypesSingleColumnIndex 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 WESTORDERTABLECopyright©2001GlobalBusinessSolutions,Inc.IndexTypesMulti-ColumnConcatenated,compositeIndexRowidOrderNoItemNoBlock#.Seq#.File#006989 001 0000A .0005 .0003006989 002 0000A .0006 .0003OrderNoItemNoCust.007000001USA007000002GBSI007000003TEST007234005WEST006989001ABC006989002XYZORDERTABLECopyright©2001GlobalBusinessSolutions,Inc.BalancedIndexTree(B*Tree)ROOTBRANCHROWIDPOINTERDATASEGMENT0000020004000000020002001249930003001400149995000700090005000599970007200810090009999ROWID|1/4”RolledSheet|100Feet|5Days|C.O.D.LEAFCopyright©2001GlobalBusinessSolutions,Inc.BitmapIndexBitmapcreatedforeachcolumnvalueCommonUsageVeryLargeTablesLow-CardinalityColumnsFeaturesRequiresminimalstorageImprovesperformance‘F’BILLLISAANNTED10010110‘M’GENDERCopyright©2001GlobalBusinessSolutions,Inc.FunctionBasedIndexCreateonfunctionsorexpressionsUsergeneratedPL/SQLSQLfunctions(non-aggregate)Externalprogram(Ccallout)600500300250900660550330275990SALARY*1.10SALARYFunction-BasedSingleSQL>SELECTsalary,salary*1.10FROMemployees;Copyright©2001GlobalBusinessSolutions,Inc.PartitionDataDecomposetablesandindexesEachpartitioncanbestoredindifferenttablespaceCustomerCustNo

CustName

State…1KodakNYOracleCAUSSteelPA6QuestWA7MotorolaTX

CustomersAL…INCustomersKS…NYCustomersOH…WYCopyright©2001GlobalBusinessSolutions,Inc.AdvantagesofPartitionsReducetimefordataloadsandindexcreationIncreaseavailabilityofmission-criticaldatabasesCreatepartitionindependenceEnableconcurrentoperationsonseparatepartitionsExecutequeriesonpartitionsinsteadoffulltablescanCopyright©2001GlobalBusinessSolutions,Inc.AdvantagesofPartitionsControldatastorageacrossphysicaldevicesMaintainpartitiontransparencyProgramsdonotneedtoreferencepartitionsProgramscantakeadvantageofpartitionsCopyright©2001GlobalBusinessSolutions,Inc.EquipartitionsDatabaseobjectshavesamelogicalpartitionsExamplesMasteranddetailtableTableandindexOrderTableItemTableCustomerTableCustomerbyStateCopyright©2001GlobalBusinessSolutions,Inc.GlobalPartitionedIndexesCanimproveperformanceCancontainkeysfromallpartitionsofcorrespondingtableCustomerSalesIndexCustomersAL…INCustomersKS…NYCustomersOH…WYCopyright©2001GlobalBusinessSolutions,Inc.IndexOrganizedTablesDatastoredwithB*TreeindexTablemusthaveprimarykey,whichactsasindexAppropriateforcomplexorunstructureddataSpatialOLAPDataDataData90009999DataDataDataDataDataData3001400149990000020004000DataDataData5000700090005000799900002999Copyright©2001GlobalBusinessSolutions,Inc.TablespaceandDatafileRelationshipTablespaceTables,indexes,etc.DatafilesSegmentExtentAtablespaceiscreatedwithoneormore datafilesDatafilesaremadeupofblocksBlocksCopyright©2001GlobalBusinessSolutions,Inc.StorageObjectandSegmentRelationshipCreateastorageobjectintablespaceTables,indexes,etc.arepreallocatedtosegmentsSegmentExtentSegmentMySegmentBlocksCopyright©2001GlobalBusinessSolutions,Inc.DatabaseSegmentsSetofextentscontainingspecifictypesofdataBootstrapSegmentTemporarySegmentRollbackSegmentIndexSegmentDataSegmentExtentExtentExtentCopyright©2001GlobalBusinessSolutions,Inc.DataSegmentsStoresrowsforTablesTablefeaturesContainsuserdataContainsDataDictionarydataRowFeaturesContainunlimitednumberofrowsSpanblocks(chaining)ColumnsstoredsidebysideBootstrapSegmentRollbackSegmentIndexSegmentDataSegmentCopyright©2001GlobalBusinessSolutions,Inc.IndexSegmentsCreatedforeveryindexExistasseparatephysicalsegmentsfromdatasegmentUsuallymuchsmallerthanassociateddatasegmentBootstrapSegmentRollbackSegmentIndexSegmentIndexSegmentCopyright©2001GlobalBusinessSolutions,Inc.RollbackSegmentsRecordbeforeimageofmodifieddataAllowchangestobeundonebeforecommitSupportreadconsistencySupportdataconcurrencySupportdatabaserecoveryASYSTEMrollbacksegmentisrequiredBootstrapSegmentRollbackSegmentIndexSegmentRollbackSegmentCopyright©2001GlobalBusinessSolutions,Inc.RollbackSegmentTypesPrivateMustincludesegmentnameinROLLBACK_SEGMENTSparameterPublicApoolofrollbacksegmentsthatmaybeacquiredbyanyinstancemountingdatabaseDeferredCreatedinSYSTEMtablespacewhentablespacetakenofflineandtransactionscannotberolledbackCopyright©2001GlobalBusinessSolutions,Inc.RollbackSegmentUsageUndodataiswrittentosegmentEachextentsupportsmultipletransactionsEachtransactionwritestoitsownblock(s)Ifextentfills,automaticallyextendsIfallextentsfill,segmentwrapsIfsegmentwraps,undodataoverwrittenIfundodatanotavailable,erroroccurs“Snapshottooold”“Cannotallocateextent”Copyright©2001GlobalBusinessSolutions,Inc.ReadConsistencyWhenqueryexecutes:CurrenttimedeterminedBlockswithsametimereadNewerblocksreconstructedwithrollbacksegmentsCustomers#NameAddress…1AcmeInc.123Ch2Zachary’s1234Br3Marie’s4456Si4Chimney9024FSELECT*FROMcustomerUPDATEcustomer...Copyright©2001GlobalBusinessSolutions,Inc.TemporarySegmentsProvidetemporaryworkspaceCreatedautomaticallyforjoins,indexes,sortsCreatedondiskwhenmemoryfullSizedbyDEFAULTSTORAGEclauseoftablespaceReclaimedbySMONBootstrapSegmentRollbackSegmentIndexSegment

TemporarySegmentCopyright©2001GlobalBusinessSolutions,Inc.BootstrapSegmentContainsdictionarydefinitionsloadedonopenIsnotuseraccessibleResidesinSYSTEMtablespaceRequiresnoDBAmaintenanceBootstrapSegmentRollbackSegmentIndexSegmentBootstrapSegmentCopyright©2001GlobalBusinessSolutions,Inc.DatabaseExtentsConsistofasetofcontiguousblocksServeasspaceallocationforsegmentDe-allocatedwhensegmentisremovedFirstextentcreatedasInitialExtentGrowbyNextExtentSegmentExtentExtentExtentCopyright©2001GlobalBusinessSolutions,Inc.DatabaseBlocksAre:smallestunitofstoragesizedbyDB_BLOCK_SIZEsameasbufferindatabasebuffercacheCorrespondtoO/SblockTypicallysizedas2k,4k,8k,or16kSegmentExtentBlocksCopyright©2001GlobalBusinessSolutions,Inc.DefineDatabaseObjectsSummaryTablespacesarelogicalcollectionsofdatabaseobjectswithoneormoredatafilesUponcreation,spaceispreallocatedfortablespacesTablespaceTables,indexes,etc.DatafilesSegmentExtentBlocksCopyright©2001G