1、Define Database Objects.Define Database ObjectsOBJECTIVESDefine TablespacesDefine Tables and IndexesDefine Segments, Extents, and Blocks.Define Database Objects OverviewDatabaseTablespaceTables, indexes, etc.DatafilesSegmentExtentBlocks.TablespacesLargest logical unit of spaceConsist of one or more

2、datafilesSYSTEM tablespace must existTablespace.Types of TablespacesSystem/NonsystemOnline/OfflineRead-Write/Read-OnlyTransportable.System and Nonsystem TablespacesSystemAt least oneData DictionaryRollbackNonsystemFacilitates AdministrationUser/Application Data, IndexesRollback and Temporary Segment

3、s.Online and Offline TablespacesOnlineAvailable to applicationsTypically tablespaces onlineOfflineNot available to users, even if database is availableUse for maintenance, troubleshooting, or historical data no longer neededOnlineOffline.Read-Write and Read-Only TablespacesRead-WriteCreate, alter, a

4、nd drop database objectsDefaultRead-OnlyCannot create or alter database objectsCan drop certain objectsCan reside on Worm device or CDROM Eliminate redundant data backupsIdeal for Data Warehouse applications and Lookup tablesRead-WriteRead-Only.Tips for Read-Only TablespacesOrganize by modification

5、requirementsFrequency of data changeBackup requirementSecurity requirementUser requirementAssociated datafile cannot be:Resized or have auto extend set to onRenamed to a new datafile name or directoryOffline.Transportable TablespacesSystem TablespaceTransportable TablespaceSystem TablespaceExportImp

6、ortCopyUtilityTransportable TablespaceSource DatabaseTarget Database.Uses of Transportable TablespacesArchive OLTP and data warehouse systemsTransfer data from OLTP to data warehouse staging systemsLoading data marts from central data warehousesPublish dataMove index data and avoid rebuilds.TablesSt

7、ored in TablespacesOrganized in rows and columnsContain dataTablespaceTables, indexes, etc.DatafilesSegmentExtentBlocks.Table DatatypesCHARVARCHAR2NUMBERDATALOBCustomersName Address Phone.George.123 Cherry 555- 5555 .Large Object DatatypeStore Unstructured InformationText documentsStatic imagesVideo

8、AudioCustomersName Address. Photo Greeting George. 123 Cherry .LOB TypesCLOB stores character dataNCLOB stores double-byte character dataBLOB stores binary data: graphics, video, soundBFILE stores pointers to external filesExternal File SystemCustomersName History Photo GreetingGeorge .External File

9、 SystemLOB LocatorsLOB data stored separate from non-LOB data BFILE data stored separate from databaseTablespace A TableName Add * (Locators point to LOB and BFILE Locations)Tablespace B .Temporary TablesStore session/ transaction specific dataShow data only to creating sessionDiscard data on commit

10、 or preserve data until session endsTablespace A Temp01 Temp02.Customers Index# Name 1 Acme Inc. 2 Zacharys 3 Maries 4 Chimney IndexesOptimize queriesROWID (Block#.Row#.File#)Stored independent of tablesConsume minimal spaceReduce I/OIndex on WHERE clause columnsCustomers Table# Name Addre.3 Maries4

11、456 Si 1 Acme Inc.1236 Ch4 Chimney9024 F 2 Zacharys1234 Br.Index TypesUniqueDuplicates not allowedNon-uniqueDuplicates allowedX.Index TypesSingle ColumnIndexRowidOrderNoBlock#.Seq#.File#0070000000A .0004 .00030070000000A .0005 .0003OrderNo ItemNo Cust.006989 001 ABC 006989 002 XYZ006989 003 USA00700

12、0 001 USA007000 002 GBSI007000 003 TEST007000 004 XYZ007234 005 WESTORDER TABLE.Index TypesMulti-ColumnConcatenated, compositeIndex RowidOrderNo ItemNo Block#.Seq#.File#006989 001 0000A.0005.0003006989 002 0000A.0006.0003OrderNo ItemNo Cust.007000 001 USA007000 002 GBSI007000 003 TEST007234 005 WEST

13、 006989 001 ABC 006989 002 XYZORDER TABLE.Balanced Index Tree (B*Tree)ROOTBRANCHROWIDPOINTERDATASEGMENT0000020004000000020002001249930003001400149995000700090005000599970007200810090009999ROWID|1/4 Rolled Sheet|100 Feet|5 Days|C.O.D.LEAF.Bitmap IndexBitmap created for each column valueCommon UsageVe

14、ry Large TablesLow-Cardinality ColumnsFeaturesRequires minimal storageImproves performanceFBILLLISAANNTED1 00 10 11 0MGENDER.Function Based IndexCreate on functions or expressionsUser generated PL/SQLSQL functions (non-aggregate)External program (C callout)600500300250900660550330275990SALARY * 1.10

15、SALARYFunction-BasedSingleSQL SELECT salary, salary*1.10 FROM employees;.Partition DataDecompose tables and indexesEach partition can be stored in different tablespaceCustomerCustNo CustName State1 Kodak NY Oracle CA US Steel PA 6 Quest WA 7 Motorola TX Customers ALINCustomers KSNY Customers OHWY.Ad

16、vantages of PartitionsReduce time for data loads and index creationIncrease availability of mission-critical databasesCreate partition independenceEnable concurrent operations on separate partitionsExecute queries on partitions instead of full table scan.Advantages of PartitionsControl data storage

17、across physical devicesMaintain partition transparencyPrograms do not need to reference partitionsPrograms can take advantage of partitions.EquipartitionsDatabase objects have same logical partitionsExamplesMaster and detail table Table and indexOrder TableItem TableCustomer TableCustomer by State .

18、Global Partitioned IndexesCan improve performanceCan contain keys from all partitions of corresponding tableCustomer Sales IndexCustomers ALINCustomers KSNY Customers OHWY.Index Organized TablesData stored with B*Tree indexTable must have primary key, which acts as indexAppropriate for complex or un

19、structured dataSpatialOLAPDataDataData90009999DataDataDataDataDataData3001400149990000020004000DataDataData5000700090005000799900002999.Tablespace and Datafile RelationshipTablespaceTables, indexes, etc.DatafilesSegmentExtentA tablespace is created with one or more datafilesDatafiles are made up of

20、blocksBlocks.Storage Object and Segment Relationship Create a storage object in tablespaceTables, indexes, etc. are preallocated to segmentsSegmentExtentSegmentMySegmentBlocks.Database SegmentsSet of extents containing specific types of dataBootstrapSegmentTemporarySegmentRollbackSegmentIndexSegment

21、Data SegmentExtentExtentExtent.Data SegmentsStores rows for TablesTable featuresContains user dataContains Data Dictionary dataRow FeaturesContain unlimited number of rowsSpan blocks (chaining)Columns stored side by sideBootstrapSegmentRollbackSegmentIndexSegmentData Segment.Index SegmentsCreated fo

22、r every indexExist as separate physical segments from data segmentUsually much smaller than associated data segmentBootstrapSegmentRollbackSegmentIndexSegmentIndex Segment.Rollback SegmentsRecord before image of modified dataAllow changes to be undone before commitSupport read consistencySupport dat

23、a concurrencySupport database recoveryA SYSTEM rollback segment is requiredBootstrapSegmentRollbackSegmentIndexSegmentRollback Segment.Rollback Segment TypesPrivateMust include segment name in ROLLBACK_SEGMENTS parameterPublicA pool of rollback segments that may be acquired by any instance mounting

24、databaseDeferredCreated in SYSTEM tablespace when tablespace taken offline and transactions cannot be rolled back.Rollback Segment UsageUndo data is written to segmentEach extent supports multiple transactionsEach transaction writes to its own block(s)If extent fills, automatically extendsIf all ext

25、ents fill, segment wrapsIf segment wraps, undo data overwrittenIf undo data not available, error occurs“Snapshot too old“Cannot allocate extent.Read ConsistencyWhen query executes:Current time determinedBlocks with same time readNewer blocks reconstructed with rollback segmentsCustomers# Name Addres

26、s1 Acme Inc. 123 Ch2 Zacharys 1234 Br3 Maries 4456 Si4 Chimney 9024 FSELECT * FROM customerUPDATE customer . . .Temporary SegmentsProvide temporary workspaceCreated automatically for joins, indexes, sortsCreated on disk when memory fullSized by DEFAULT STORAGE clause of tablespaceReclaimed by SMONBootstrapSegmentRollbackSegmentIndexSegment Temporary Segment.Bootstrap SegmentContains dictionary definitions loaded on openIs not user accessibleResides in SYSTEM tablespaceRequires no DBA maintenanceBootstrapSegmentRollbackSegmentIndexSegmentBootstrap Segment.Database ExtentsConsist of a s