1、Define Database ObjectsCopyright 2001 Global Business Solutions, Inc.DefineDatabaseObjectsOBJECTIVESDefineTablespacesDefineTables andIndexesDefineSegments, Extents, andBlocksCopyright2001 GlobalBusinessSolutions, Inc.DefineDatabaseObjectsOverviewDatabaseTablespaceTables, indexes, etc.DatafilesSegmen

2、tExtentBlocksCopyright2001 GlobalBusinessSolutions, Inc.TablespacesLargest logicalunitofspaceConsist of oneormore datafilesSYSTEMtablespacemust existTablespaceCopyright2001 GlobalBusinessSolutions, Inc.TypesofTablespacesSystem/NonsystemOnline/OfflineRead-Write/Read-OnlyTransportableCopyright2001 Glo

3、balBusinessSolutions, Inc.Systemand NonsystemTablespacesSystemAtleastoneData DictionaryRollbackNonsystemFacilitatesAdministrationUser/Application Data, IndexesRollbackandTemporary SegmentsCopyright2001 GlobalBusinessSolutions, Inc.Onlineand OfflineTablespacesOnlineAvailabletoapplicationsTypicallytab

4、lespacesonlineOfflineNotavailable to users,even if database is availableUsefor maintenance,troubleshooting, or historicaldatanolongerneededOnlineOfflineCopyright2001 GlobalBusinessSolutions, Inc.Read-Write andRead-OnlyTablespacesRead-WriteCreate, alter,anddropdatabaseobjectsDefaultRead-OnlyCannotcre

5、ate or alter database objectsCandropcertain objectsCanreside on Wormdevice or CDROMEliminateredundant databackupsIdealforDataWarehouseapplications andLookuptablesRead-WriteRead-OnlyCopyright2001 GlobalBusinessSolutions, Inc.Tips forRead-OnlyTablespacesOrganizebymodificationrequirementsFrequencyofdat

6、achangeBackuprequirementSecurityrequirementUser requirementAssociated datafile cannotbe:Resized or haveautoextendset to onRenamed to anew datafile nameordirectoryOfflineCopyright2001 GlobalBusinessSolutions, Inc.Transportable TablespacesSystemTablespaceTransportableTablespaceSystemTablespaceExportIm

7、portCopyUtilityTransportableTablespaceSourceDatabaseTargetDatabaseCopyright2001 GlobalBusinessSolutions, Inc.Uses of TransportableTablespacesArchive OLTPand datawarehouse systemsTransferdata fromOLTPtodata warehousestaging systemsLoading datamartsfromcentral datawarehousesPublish dataMove index data

8、and avoid rebuildsCopyright2001 GlobalBusinessSolutions, Inc.TablesStoredinTablespacesOrganizedinrowsandcolumnsContain dataTablespaceTables, indexes, etc.DatafilesSegmentExtentBlocksCopyright2001 GlobalBusinessSolutions, Inc.TableDatatypesCHARVARCHAR2NUMBERDATALOBCustomersNameAddressPhone.George.123

9、Cherry555- 5555Copyright2001 GlobalBusinessSolutions, Inc.LargeObjectDatatypeStoreUnstructuredInformationText documentsStaticimagesVideoAudioCustomersNameAddress.PhotoGreetingGeorge.123 CherryCopyright2001 GlobalBusinessSolutions, Inc.LOBTypesCLOB storescharacterdataNCLOBstoresdouble-bytecharacter d

10、ataBLOB storesbinarydata:graphics, video,soundBFILEstorespointerstoexternalfilesExternalFileSystemCustomersNameHistoryPhotoGreetingGeorgeCopyright2001 GlobalBusinessSolutions, Inc.ExternalFileSystemLOBLocatorsLOBdatastoredseparatefromnon-LOB dataBFILEdata storedseparatefrom databaseTablespace ATable

11、NameAdd* (LocatorspointtoLOBand BFILE Locations)Tablespace BCopyright2001 GlobalBusinessSolutions, Inc.TemporaryTablesStoresession/transactionspecificdataShow dataonlytocreatingsessionDiscard dataoncommit or preserve datauntilsessionendsTablespace ATemp01Temp02Copyright2001 GlobalBusinessSolutions,

12、Inc.CustomersIndex# Name1 AcmeInc.2 Zacharys3 Maries4 ChimneyIndexesOptimizequeriesROWID(Block#.Row#.File#)StoredindependentoftablesConsume minimalspaceReduceI/OIndexonWHEREclausecolumnsCustomersTable# NameAddre.3 Maries4456Si1 AcmeInc.1236 Ch4 Chimney9024F2 Zacharys1234 BrCopyright2001 GlobalBusine

13、ssSolutions, Inc.IndexTypesUniqueDuplicates notallowedNon-uniqueDuplicates allowedXCopyright2001 GlobalBusinessSolutions, Inc.IndexTypesSingleColumnIndexRowidOrderNoBlock#.Seq#.File#0070000000A .0004 .00030070000000A .0005 .0003OrderNo ItemNo Cust.006989 001 ABC 006989 002 XYZ006989 003 USA007000 00

14、1 USA007000 002 GBSI007000 003 TEST007000 004 XYZ007234 005 WESTORDER TABLECopyright2001 GlobalBusinessSolutions, Inc.IndexTypesMulti-ColumnConcatenated, compositeIndexRowidOrderNo ItemNo Block#.Seq#.File#006989 001 0000A.0005.0003006989 002 0000A.0006.0003OrderNo ItemNoCust.007000001USA007000002GBS

15、I007000003TEST007234005WEST006989001ABC006989002XYZORDERTABLECopyright2001 GlobalBusinessSolutions, Inc.BalancedIndexTree (B*Tree)ROOTBRANCHROWIDPOINTERDATASEGMENT0000020004000000020002001249930003001400149995000700090005000599970007200810090009999ROWID|1/4” RolledSheet|100Feet|5 Days|C.O.D.LEAFCopy

16、right2001 GlobalBusinessSolutions, Inc.BitmapIndexBitmapcreatedforeachcolumnvalueCommonUsageVery Large TablesLow-CardinalityColumnsFeaturesRequiresminimal storageImprovesperformanceFBILLLISAANNTED10010110MGENDERCopyright2001 GlobalBusinessSolutions, Inc.FunctionBasedIndexCreateonfunctions or express

17、ionsUser generatedPL/SQLSQLfunctions (non-aggregate)Externalprogram (C callout)600500300250900660550330275990SALARY*1.10SALARYFunction-BasedSingleSQL SELECTsalary, salary*1.10 FROMemployees;Copyright2001 GlobalBusinessSolutions, Inc.PartitionDataDecomposetables andindexesEach partitioncanbestored in

18、 differenttablespaceCustomerCustNoCustNameState1KodakNYOracleCAUSSteelPA6QuestWA7MotorolaTXCustomersALINCustomersKSNYCustomersOHWYCopyright2001 GlobalBusinessSolutions, Inc.Advantages of PartitionsReducetimefordataloadsandindexcreationIncreaseavailabilityofmission-criticaldatabasesCreatepartition in

19、dependenceEnableconcurrentoperations on separate partitionsExecute queriesonpartitionsinstead of fulltablescanCopyright2001 GlobalBusinessSolutions, Inc.Advantages of PartitionsControl datastorageacrossphysicaldevicesMaintainpartitiontransparencyProgramsdonotneedtoreferencepartitionsProgramscantakea

20、dvantageofpartitionsCopyright2001 GlobalBusinessSolutions, Inc.EquipartitionsDatabaseobjects havesamelogicalpartitionsExamplesMasterand detailtableTableandindexOrderTableItem TableCustomerTableCustomerbyStateCopyright2001 GlobalBusinessSolutions, Inc.GlobalPartitionedIndexesCanimproveperformanceCanc

21、ontainkeys fromall partitionsofcorrespondingtableCustomerSalesIndexCustomersALINCustomersKSNYCustomersOHWYCopyright2001 GlobalBusinessSolutions, Inc.IndexOrganizedTablesData storedwith B*TreeindexTablemust haveprimarykey, which actsasindexAppropriateforcomplexorunstructureddataSpatialOLAPDataDataDat

22、a90009999DataDataDataDataDataData3001400149990000020004000DataDataData5000700090005000799900002999Copyright2001 GlobalBusinessSolutions, Inc.Tablespace andDatafileRelationshipTablespaceTables, indexes, etc.DatafilesSegmentExtentA tablespaceiscreatedwith oneormoredatafilesDatafilesare madeupofblocksB

23、locksCopyright2001 GlobalBusinessSolutions, Inc.Storage ObjectandSegmentRelationshipCreateastorage objectintablespaceTables, indexes, etc.are preallocatedtosegmentsSegmentExtentSegmentMySegmentBlocksCopyright2001 GlobalBusinessSolutions, Inc.DatabaseSegmentsSetofextentscontaining specific types of d

24、ataBootstrapSegmentTemporarySegmentRollbackSegmentIndexSegmentDataSegmentExtentExtentExtentCopyright2001 GlobalBusinessSolutions, Inc.Data SegmentsStoresrowsforTablesTablefeaturesContainsuser dataContainsData DictionarydataRowFeaturesContain unlimitednumberofrowsSpan blocks(chaining)Columns storedsi

25、de by sideBootstrapSegmentRollbackSegmentIndexSegmentDataSegmentCopyright2001 GlobalBusinessSolutions, Inc.IndexSegmentsCreated foreveryindexExistasseparatephysicalsegmentsfrom datasegmentUsually muchsmallerthan associateddatasegmentBootstrapSegmentRollbackSegmentIndexSegmentIndexSegmentCopyright200

26、1 GlobalBusinessSolutions, Inc.RollbackSegmentsRecordbefore image of modified dataAllowchanges to be undonebeforecommitSupport readconsistencySupport dataconcurrencySupport database recoveryA SYSTEMrollbacksegment is requiredBootstrapSegmentRollbackSegmentIndexSegmentRollbackSegmentCopyright2001 Glo

27、balBusinessSolutions, Inc.RollbackSegment TypesPrivateMust includesegmentname in ROLLBACK_SEGMENTS parameterPublicA poolofrollbacksegmentsthatmaybeacquiredbyany instance mounting databaseDeferredCreated in SYSTEMtablespace whentablespacetakenoffline andtransactionscannot be rolledbackCopyright2001 G

28、lobalBusinessSolutions, Inc.RollbackSegment UsageUndo dataiswrittentosegmentEach extentsupportsmultipletransactionsEach transaction writestoitsown block(s)Ifextentfills, automaticallyextendsIfallextentsfill,segment wrapsIfsegment wraps,undo dataoverwrittenIfundo datanot available,erroroccurs“Snapsho

29、ttooold”“Cannot allocate extent”Copyright2001 GlobalBusinessSolutions, Inc.Read ConsistencyWhen query executes:Current timedeterminedBlockswithsame timereadNewerblocksreconstructedwith rollback segmentsCustomers# NameAddress1 AcmeInc.123Ch2 Zacharys1234Br3 Maries4456Si4 Chimney9024FSELECT*FROM custo

30、merUPDATEcustomer. .Copyright2001 GlobalBusinessSolutions, Inc.TemporarySegmentsProvide temporaryworkspaceCreated automaticallyfor joins,indexes,sortsCreated on diskwhenmemoryfullSizedbyDEFAULT STORAGEclause of tablespaceReclaimedbySMONBootstrapSegmentRollbackSegmentIndexSegmentTemporarySegmentCopyright2001 GlobalBusinessSolutions, Inc.BootstrapSegmentContainsdictionary definitions loadedonopenIsnotuseraccessibleResides in SYSTEMtablespaceRequiresnoDBAmaintenanceBootstrapSegmentRollbackSegmentIndexSegmentBootstrapSegmentCopyright2001 GlobalBusinessSolutions, I