Oracle對(duì)象定義(英文版)

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