0Oracle 對象定義-概念(45)++

1、Define Database ObjectsCopyright 2001 Global Business Solutions, Inc.Define Database ObjectsOBJECTIVESnDefine TablespacesnDefine Tables and IndexesnDefine Segments, Extents, and BlocksCopyright 2001 Global Business Solutions, Inc.Define Database Objects OverviewDatabaseTablespaceTables, indexes, etc

2、.DatafilesSegmentExtentBlocksCopyright 2001 Global Business Solutions, Inc.TablespacesnLargest logical unit of spacenConsist of one or more datafilesnSYSTEM tablespace must existTablespaceCopyright 2001 Global Business Solutions, Inc.Types of TablespacesnSystem/NonsystemnOnline/OfflinenRead-Write/Re

3、ad-OnlynTransportableCopyright 2001 Global Business Solutions, Inc.System and Nonsystem TablespacesnSystemlAt least onelData DictionarylRollbacknNonsystemlFacilitates AdministrationlUser/Application Data, IndexeslRollback and Temporary SegmentsCopyright 2001 Global Business Solutions, Inc.Online and

4、 Offline TablespacesnOnlinelAvailable to applicationslTypically tablespaces onlinenOfflinelNot available to users, even if database is availablelUse for maintenance, troubleshooting, or historical data no longer neededOnlineOfflineCopyright 2001 Global Business Solutions, Inc.Read-Write and Read-Onl

5、y TablespacesnRead-WritelCreate, alter, and drop database objectslDefaultnRead-OnlylCannot create or alter database objectslCan drop certain objectslCan reside on Worm device or CDROM lEliminate redundant data backupslIdeal for Data Warehouse applications and Lookup tablesRead-WriteRead-OnlyCopyrigh

6、t 2001 Global Business Solutions, Inc.Tips for Read-Only TablespacesnOrganize by modification requirementslFrequency of data changelBackup requirementlSecurity requirementlUser requirementnAssociated datafile cannot be:lResized or have auto extend set to onlRenamed to a new datafile name or director

7、ylOfflineCopyright 2001 Global Business Solutions, Inc.Transportable TablespacesSystem TablespaceTransportable TablespaceSystem TablespaceExportImportCopyUtilityTransportable TablespaceSource DatabaseTarget DatabaseCopyright 2001 Global Business Solutions, Inc.Uses of Transportable TablespacesnArchi

8、ve OLTP and data warehouse systemsnTransfer data from OLTP to data warehouse staging systemsnLoading data marts from central data warehousesnPublish datanMove index data and avoid rebuildsCopyright 2001 Global Business Solutions, Inc.TablesnStored in TablespacesnOrganized in rows and columnsnContain

9、 dataTablespaceTables, indexes, etc.DatafilesSegmentExtentBlocksCopyright 2001 Global Business Solutions, Inc.Table DatatypesnCHARnVARCHAR2nNUMBERnDATAnLOBCustomersName Address Phone.George.123 Cherry 555- 5555 Copyright 2001 Global Business Solutions, Inc.Large Object DatatypenStore Unstructured In

10、formationlText documentslStatic imageslVideolAudioCustomersName Address. Photo Greeting George. 123 Cherry Copyright 2001 Global Business Solutions, Inc.LOB TypesnCLOB stores character datanNCLOB stores double-byte character datanBLOB stores binary data: graphics, video, soundnBFILE stores pointers

11、to external filesExternal File SystemCustomersName History Photo GreetingGeorge Copyright 2001 Global Business Solutions, Inc.External File SystemLOB LocatorsnLOB data stored separate from non-LOB data nBFILE data stored separate from databaseTablespace A TableName Add * (Locators point to LOB and B

12、FILE Locations)Tablespace B Copyright 2001 Global Business Solutions, Inc.Temporary TablesnStore session/ transaction specific datanShow data only to creating sessionnDiscard data on commit or preserve data until session endsTablespace A Temp01 Temp02Copyright 2001 Global Business Solutions, Inc.Cus

13、tomers Index# Name 1 Acme Inc. 2 Zacharys 3 Maries 4 Chimney IndexesnOptimize querieslROWID (Block#.Row#.File#)nStored independent of tablesnConsume minimal spacenReduce I/OnIndex on WHERE clause columnsCustomers Table# Name Addre.3 Maries4456 Si 1 Acme Inc. 1236 Ch4 Chimney9024 F 2 Zacharys 1234 Br

14、Copyright 2001 Global Business Solutions, Inc.Index TypesnUniquelDuplicates not allowednNon-uniquelDuplicates allowedXCopyright 2001 Global Business Solutions, Inc.Index TypesnSingle ColumnIndexRowidOrderNo Block#.Seq#.File#0070000000A .0004 .00030070000000A .0005 .0003OrderNo ItemNo Cust.006989 001

15、 ABC 006989 002 XYZ006989 003 USA007000 001 USA007000 002 GBSI007000 003 TEST007000 004 XYZ007234 005 WESTORDER TABLECopyright 2001 Global Business Solutions, Inc.Index TypesnMulti-ColumnlConcatenated, compositeIndex RowidOrderNo ItemNo Block#.Seq#.File#006989 001 0000A .0005.0003006989 002 0000A .0

16、006.0003OrderNo ItemNo Cust.007000 001 USA007000 002 GBSI007000 003 TEST007234 005 WEST 006989 001 ABC 006989 002 XYZORDER TABLECopyright 2001 Global Business Solutions, Inc.Balanced Index Tree (B*Tree)ROOTBRANCHROWIDPOINTERDATASEGMENT00000200040000000200020012499300030014001499950007000900050005999

17、70007200810090009999ROWID|1/4” Rolled Sheet|100 Feet|5 Days|C.O.D.LEAFCopyright 2001 Global Business Solutions, Inc.Bitmap IndexnBitmap created for each column valuenCommon UsagelVery Large TableslLow-Cardinality ColumnsnFeatureslRequires minimal storagelImproves performanceFBILLLISAANNTED1 00 10 11

18、 0MGENDERCopyright 2001 Global Business Solutions, Inc.Function Based IndexnCreate on functions or expressionslUser generated PL/SQLlSQL functions (non-aggregate)lExternal program (C callout)600500300250900660550330275990SALARY * 1.10SALARYFunction-BasedSingleSQL SELECT salary, salary*1.10 FROM empl

19、oyees;Copyright 2001 Global Business Solutions, Inc.Partition DatanDecompose tables and indexesnEach partition can be stored in different tablespaceCustomerCustNo CustName State1 Kodak NY 4 Oracle CA 5 US Steel PA 6 Quest WA 7 Motorola TX Customers ALINCustomers KSNY Customers OHWYCopyright 2001 Glo

20、bal Business Solutions, Inc.Advantages of PartitionsnReduce time for data loads and index creationnIncrease availability of mission-critical databasesnCreate partition independencelEnable concurrent operations on separate partitionslExecute queries on partitions instead of full table scanCopyright 2

21、001 Global Business Solutions, Inc.Advantages of PartitionsnControl data storage across physical devicesnMaintain partition transparencylPrograms do not need to reference partitionslPrograms can take advantage of partitionsCopyright 2001 Global Business Solutions, Inc.EquipartitionsnDatabase objects

22、 have same logical partitionsnExampleslMaster and detail table lTable and indexOrder TableItem TableCustomer TableCustomer by State Copyright 2001 Global Business Solutions, Inc.Global Partitioned IndexesnCan improve performancenCan contain keys from all partitions of corresponding tableCustomer Sal

23、es IndexCustomers ALINCustomers KSNY Customers OHWYCopyright 2001 Global Business Solutions, Inc.Index Organized TablesnData stored with B*Tree indexnTable must have primary key, which acts as indexnAppropriate for complex or unstructured datalSpatiallOLAPDataDataData90009999DataDataDataDataDataData

24、3001400149990000020004000DataDataData5000700090005000799900002999Copyright 2001 Global Business Solutions, Inc.Tablespace and Datafile RelationshipTablespaceTables, indexes, etc.DatafilesSegmentExtentnA tablespace is created with one or more datafilesnDatafiles are made up of blocksBlocksCopyright 2

25、001 Global Business Solutions, Inc.Storage Object and Segment Relationship nCreate a storage object in tablespacenTables, indexes, etc. are preallocated to segmentsSegmentExtentSegmentMySegmentBlocksCopyright 2001 Global Business Solutions, Inc.Database SegmentsnSet of extents containing specific ty

26、pes of dataBootstrapSegmentTemporarySegmentRollbackSegmentIndexSegmentData SegmentExtentExtentExtentCopyright 2001 Global Business Solutions, Inc.Data SegmentsnStores rows for TablesnTable featureslContains user datalContains Data Dictionary datanRow FeatureslContain unlimited number of rowslSpan bl

27、ocks (chaining)lColumns stored side by sideBootstrapSegmentRollbackSegmentIndexSegmentData SegmentCopyright 2001 Global Business Solutions, Inc.Index SegmentsnCreated for every indexnExist as separate physical segments from data segmentnUsually much smaller than associated data segmentBootstrapSegme

28、ntRollbackSegmentIndexSegmentIndex SegmentCopyright 2001 Global Business Solutions, Inc.Rollback SegmentsnRecord before image of modified datanAllow changes to be undone before commitnSupport read consistencynSupport data concurrencynSupport database recoverynA SYSTEM rollback segment is requiredBoo

29、tstrapSegmentRollbackSegmentIndexSegmentRollback SegmentCopyright 2001 Global Business Solutions, Inc.Rollback Segment TypesnPrivatelMust include segment name in ROLLBACK_SEGMENTS parameternPubliclA pool of rollback segments that may be acquired by any instance mounting databasenDeferredlCreated in

30、SYSTEM tablespace when tablespace taken offline and transactions cannot be rolled backCopyright 2001 Global Business Solutions, Inc.Rollback Segment UsagenUndo data is written to segmentnEach extent supports multiple transactionsnEach transaction writes to its own block(s)nIf extent fills, automatic

31、ally extendsnIf all extents fill, segment wrapsnIf segment wraps, undo data overwrittennIf undo data not available, error occurs“Snapshot too old”“Cannot allocate extent”Copyright 2001 Global Business Solutions, Inc.Read ConsistencynWhen query executes:lCurrent time determinedlBlocks with same time

32、readlNewer blocks reconstructed with rollback segmentsCustomers# Name Address1 Acme Inc. 123 Ch2 Zacharys 1234 Br3 Maries 4456 Si4 Chimney 9024 FSELECT * FROM customerUPDATE customer . . .Copyright 2001 Global Business Solutions, Inc.Temporary SegmentsnProvide temporary workspacenCreated automatical

33、ly for joins, indexes, sortsnCreated on disk when memory fullnSized by DEFAULT STORAGE clause of tablespacenReclaimed by SMONBootstrapSegmentRollbackSegmentIndexSegment Temporary SegmentCopyright 2001 Global Business Solutions, Inc.Bootstrap SegmentnContains dictionary definitions loaded on opennIs not user accessiblenResides in SYSTEM tablespacenRequires no DBA maintenanceBootstrapSegmentRollbackSegmentIndexSegmentBootstrap SegmentCopyright 2001 Global Business Solutions, Inc.Databa