0Oracle 对象定义-概念(45)++

Copyright©2001GlobalBusinessSolutions,Inc.DefineDatabaseObjectsCopyright©2001GlobalBusinessSolutions,Inc.DefineDatabaseObjectsOBJECTIVESDefineTablespacesDefineTablesandIndexesDefineSegments,Extents,andBlocksCopyright©2001GlobalBusinessSolutions,Inc.DefineDatabaseObjectsOverviewDatabaseTablespaceTables,indexes,etc.DatafilesSegmentExtentBlocksCopyright©2001GlobalBusinessSolutions,Inc.TablespacesLargestlogicalunitofspaceConsistofoneormoredatafilesSYSTEMtablespacemustexistTablespaceCopyright©2001GlobalBusinessSolutions,Inc.TypesofTablespacesSystem/NonsystemOnline/OfflineRead-Write/Read-OnlyTransportableCopyright©2001GlobalBusinessSolutions,Inc.SystemandNonsystemTablespacesSystemAtleastoneDataDictionaryRollbackNonsystemFacilitatesAdministrationUser/ApplicationData,IndexesRollbackandTemporarySegmentsCopyright©2001GlobalBusinessSolutions,Inc.OnlineandOfflineTablespacesOnlineAvailabletoapplicationsTypicallytablespacesonlineOfflineNotavailabletousers,evenifdatabaseisavailableUseformaintenance,troubleshooting,orhistoricaldatanolongerneededOnlineOfflineCopyright©2001GlobalBusinessSolutions,Inc.Read-WriteandRead-OnlyTablespacesRead-WriteCreate,alter,anddropdatabaseobjectsDefaultRead-OnlyCannotcreateoralterdatabaseobjectsCandropcertainobjectsCanresideonWormdeviceorCDROMEliminateredundantdatabackupsIdealforDataWarehouseapplicationsandLookuptablesRead-WriteRead-OnlyCopyright©2001GlobalBusinessSolutions,Inc.TipsforRead-OnlyTablespacesOrganizebymodificationrequirementsFrequencyofdatachangeBackuprequirementSecurityrequirementUserrequirementAssociateddatafilecannotbe:ResizedorhaveautoextendsettoonRenamedtoanewdatafilenameordirectoryOfflineCopyright©2001GlobalBusinessSolutions,Inc.TransportableTablespacesSystemTablespaceTransportableTablespaceSystemTablespaceExportImportCopyUtilityTransportableTablespaceSourceDatabaseTargetDatabaseCopyright©2001GlobalBusinessSolutions,Inc.UsesofTransportableTablespacesArchiveOLTPanddatawarehousesystemsTransferdatafromOLTPtodatawarehousestagingsystemsLoadingdatamartsfromcentraldatawarehousesPublishdataMoveindexdataandavoidrebuildsCopyright©2001GlobalBusinessSolutions,Inc.TablesStoredinTablespacesOrganizedinrowsandcolumnsContaindataTablespaceTables,indexes,etc.DatafilesSegmentExtentBlocksCopyright©2001GlobalBusinessSolutions,Inc.TableDatatypesCHARVARCHAR2NUMBERDATALOBCustomersNameAddressPhone…..George..123Cherry555-5555Copyright©2001GlobalBusinessSolutions,Inc.LargeObjectDatatypeStoreUnstructuredInformationTextdocumentsStaticimagesVideoAudioCustomersNameAddress...PhotoGreetingGeorge..123CherryCopyright©2001GlobalBusinessSolutions,Inc.LOBTypesCLOBstorescharacterdataNCLOBstoresdouble-bytecharacterdataBLOBstoresbinarydata:graphics,video,soundBFILEstorespointerstoexternalfilesExternalFileSystemCustomersName…HistoryPhotoGreetingGeorgeCopyright©2001GlobalBusinessSolutions,Inc.ExternalFileSystemLOBLocatorsLOBdatastoredseparatefromnon-LOBdataBFILEdatastoredseparatefromdatabaseTablespaceATableNameAdd…*(LocatorspointtoLOBandBFILELocations)TablespaceBCopyright©2001GlobalBusinessSolutions,Inc.TemporaryTablesStoresession/transactionspecificdataShowdataonlytocreatingsessionDiscarddataoncommitorpreservedatauntilsessionendsTablespaceATemp01Temp02Copyright©2001GlobalBusinessSolutions,Inc.CustomersIndex#Name1AcmeInc.2Zachary’s3Marie’s4ChimneyIndexesOptimizequeriesROWID(Block#.Row#.File#)StoredindependentoftablesConsumeminimalspaceReduceI/OIndexonWHEREclausecolumnsCustomersTable#NameAddre..3Marie’s4456Si1AcmeInc.1236Ch4Chimney9024F2Zachary’s1234BrCopyright©2001GlobalBusinessSolutions,Inc.IndexTypesUniqueDuplicatesnotallowedNon-uniqueDuplicatesallowedXCopyright©2001GlobalBusinessSolutions,Inc.IndexTypesSingleColumnIndexRowidOrderNoBlock#.Seq#.File#0070000000A.0004.00030070000000A.0005.0003OrderNoItemNoCust.006989001ABC006989002XYZ006989003USA007000001USA007000002GBSI007000003TEST007000004XYZ007234005WESTORDERTABLECopyright©2001GlobalBusinessSolutions,Inc.IndexTypesMulti-ColumnConcatenated,compositeIndexRowidOrderNoItemNoBlock#.Seq#.File#0069890010000A.0005.00030069890020000A.0006.0003OrderNoItemNoCust.007000001USA007000002GBSI007000003TEST007234005WEST006989001ABC006989002XYZORDERTABLECopyright©2001GlobalBusinessSolutions,Inc.BalancedIndexTree(B*Tree)ROOTBRANCHROWIDPOINTERDATASEGMENT0000020004000000020002001249930003001400149995000700090005000599970007200810090009999ROWID|1/4”RolledSheet|100Feet|5Days|C.O.D.LEAFCopyright©2001GlobalBusinessSolutions,Inc.BitmapIndexBitmapcreatedforeachcolumnvalueCommonUsageVeryLargeTablesLow-CardinalityColumnsFeaturesRequiresminimalstorageImprovesperformance‘F’BILLLISAANNTED10010110‘M’GENDERCopyright©2001GlobalBusinessSolutions,Inc.FunctionBasedIndexCreateonfunctionsorexpressionsUsergeneratedPL/SQLSQLfunctions(non-aggregate)Externalprogram(Ccallout)600500300250900660550330275990SALARY*1.10SALARYFunction-BasedSingleSQLSELECTsalary,salary*1.10FROMemployees;Copyright©2001GlobalBusinessSolutions