3.模式对象管理概述(自学).doc

SRC: Oracle8i Administrators Guide(12)Guidelines for Managing Schema Objects Managing Space in Data Blocks Transaction Entry Settings (INITRANS and MAXTRANS) Setting Storage Parameters Deallocating SpaceManaging Space in Data BlocksThe PCTFREE and PCTUSED parameters are used to:1. Increase the performance of writing and retrieving data2. Decrease the amount of unused space in data blocks3. Decrease the amount of row chaining between data blocksThe PCTFREE Parameterl The PCTFREE parameter is used to set the percentage of a block to be reserved for possible updates to rows that already are contained in that block.l Notice that before the block reaches PCTFREE, the free space of the data block is filled by both the insertion of new rows and by the growth of the data block header.Specifying PCTFREEl The default for PCTFREE is 10 percent. You can use any integer between 0 and 99, inclusive, as long as the sum of PCTFREE and PCTUSED does not exceed 100.l A smaller PCTFREE has the following effects:1. Reserves less room for updates to expand existing table rows2. Allows inserts to fill the block more completely3. May save space, because the total data for a table or index is stored in fewer blocks (more rows or entries per block)l A larger PCTFREE has the following effects:1. Reserves more room for future updates to existing table rows2. May require more blocks for the same amount of inserted data (inserting fewer rows per block)3. May improve update performance, because Oracle does not need to chain row pieces as frequently, if everl PCTFREE for Nonclustered Tables: If the data in the rows of a nonclustered table is likely to increase in size over time, reserve some space for these updates. Otherwise, updated rows are likely to be chained among blocks.l PCTFREE for Clustered Tables: The discussion for nonclustered tables also applies to clustered tables. However, if PCTFREE is reached, new rows from any table contained in the same cluster key go into a new data block that is chained to the existing cluster key.l PCTFREE for Indexes: You can specify PCTFREE only when initially creating an index.The PCTUSED Parameterl After a data block becomes full as determined by PCTFREE, Oracle does not consider the block for the insertion of new rows until the percentage of the block being used falls below the parameter PCTUSED.Specifying PCTUSEDl The default value for PCTUSED is 40 percent. The percent value is for the block space available for data after overhead is subtracted from total space.l You can specify any integer between 0 and 99 (inclusive) for PCTUSED, as long as the sum of PCTUSED and PCTFREE does not exceed 100.l A smaller PCTUSED has the following effects:1. Reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when it has fallen below that percentage of usage2. Increases the unused space in a databasel A larger PCTUSED has the following effects:1. Improves space efficiency2. Increases processing cost during INSERTs and UPDATEsSelecting Associated PCTUSED and PCTFREE Values1. The sum of PCTFREE and PCTUSED must be equal to or less than 100.2. If the sum equals 100, then Oracle attempts to keep no more than PCTFREE free space, and processing costs are highest.3. Block overhead is not included in the computation of PCTUSED or PCTFREE.4. The smaller the difference between 100 and the sum of PCTFREE and PCTUSED (as in PCTUSED of 75, PCTFREE of 20), the more efficient space usage is, at some performance cost.Examples of Choosing PCTFREE and PCTUSED Values1ScenarioCommon activity includes UPDATE statements that increase the size of the rows.SettingsPCTFREE = 20；PCTUSED = 402ScenarioMost activity includes INSERT and DELETE statements, and UPDATE statements that do not increase the size of affected rows.SettingsPCTFREE = 5；PCTUSED = 60ExplanationPCTFREE is set to 5 because most UPDATE statements do not increase row sizes. PCTUSED is set to 60 so that space freed by DELETE statements is used soon, yet processing is minimized.3ScenarioThe table is very large; therefore, storage is a primary concern. Most activity includes read-only transactions.SettingsPCTFREE = 5；PCTUSED = 40ExplanationPCTFREE is set to 5 because this is a large table and you want to completely fill each block.Transaction Entry Settings (INITRANS and MAXTRANS)l The INITRANS and MAXTRANS transaction entry settings for the data blocks allocated for a table, cluster, or index should be set individually for each object based on the following criteria:1. The space you would like to reserve for transaction entries compared to the space you would reserve for database data2. The number of concurrent transactions that are likely to touch the same data blocks at any given timel If a table is very large and only a small number of users simultaneously access the table, INITRANS can be set low, especially if space is at a premium（非常需要） in the database.l If a table is usually accessed by many users at the same time,a high INITRANS and a higher MAXTRANS can be set so that no user has to wait to access necessary data blocks.INITRANSl Specifies the number of DML transaction entries for which space should be initially reserved in the data block header.l Space is reserved in the headers of all data blocks in the associated data or index segment. The default value is 1 for tables and 2 for clusters and indexes.MAXTRANSl As multiple transactions concurrently access the rows of the same data block, space is allocated for each DML transactions entry in the block. Once allocated, this space effectively becomes a permanent part of the block header.l The MAXTRANS parameter limits the number of transaction entries that can concurrently use data in a data block.l The default value is an operating system-specific function of block size, not exceeding 255.Setting Storage Parametersl These storage parameters apply to the following types of structures and schema objects:1. Tablespaces (used as storage parameter defaults for all segments)2. Tables, partitions, clusters, snapshots, and snapshot logs (data segments)3. Indexes (index segments)4. Rollback segmentsIdentifying the Storage Parametersl Every database has default values for storage parameters.l You can specify new defaults for a tablespace. These default storage values are specified in the DEFAULT STORAGE clause of a CREATE or ALTER TABLESPACE statement.l You can specify storage settings for each individual schema object. To do so, you use the STORAGE clause of the CREATE or ALTER statement for the individual object.INITIALl The size, in bytes, of the first extent allocated when a segment is created. This parameter can not be specified on an ALTER statement.Default5 data blocksMinimum2 data blocks (nonbitmapped segments), 3 data blocks (bitmapped segments)MaximumOperating system specificNEXTl The size, in bytes, of the next incremental extent to be allocated for a segment. The second extent is equal to the original setting for NEXT. From there forward, NEXT is set to the previous size of NEXT multiplied by (1 + PCTINCREASE/100).Default5 data blocksMinimum1 data blockMaximumOperating system specificPCTINCREASEl The percentage by which each incremental extent grows over the last incremental extent allocated for a segment.l The new NEXT equals 1 + PCTINCREASE/100, multiplied by the size of the last incremental extent (the old NEXT) and rounded up to the next multiple of a block size.Default50 (%)Minimum0 (%)MaximumOperating system specificMINEXTENTSl The total number of extents to be allocated when the segment is created. This allows for a large allocation of space at creation time, even if contiguous space is not available.Default1 (extent); 2(extents) for rollback segmentsMinimum1 (extent); 2 (extents) for rollback segmentsMaximumOperating system specificMAXEXTENTSl The total number of extents, including the first, that can ever be allocated for the segment.DefaultDepends on the data block size and operating systemMinimum1 (extent); 2(extents) for rollback segmentsMaximumUnlimitedFREELIST GROUPSl The number of groups of free lists for the database object you are creating. Oracle uses the instance number of Oracle Parallel Server instances to map each instance to one free list group.Default1Minimum1MaximumDepends on number of Oracle Parallel Server instancesFREELISTSl Specifies the number of free lists for each of the free list groups for the schema object. Not valid for tablespaces.Default1Minimum1MaximumDepends on data block sizeOPTIMALl Relevant only to rollback segments.BUFFER_POOLl Defines a default buffer pool (cache) for a schema object. Not valid for tablespaces or rollback segments.Setting Default Storage Parameters for Segments in a Tablespacel When specifying MINEXTENTS at the tablespace level, any extent allocated in the tablespace is rounded to a multiple of the number of minimum extents. Basically, the number of extents is a multiple of the number of blocks.Setting Storage Parameters for Data Segmentsl You set the storage parameters for the data segment of a nonclustered table, snapshot, or snapshot log using the STORAGE clause of the CREATE or ALTER statement for tables, snapshots, or snapshot logs.l You set the storage parameters for the data segments of a cluster using the STORAGE clause of the CREATE CLUSTER or ALTER CLUSTER statement.l The storage parameters set for the cluster override the tables storage parameters.l With partitioned tables, you can set default storage parameters at the table level.Setting Storage Parameters for Index Segmentsl Storage parameters for an index segment created for a table index can be set using the STORAGE clause of the CREATE INDEX or ALTER INDEX statement. Setting Storage Parameters for LOBs, Varrays, and Nested Tablesl A table or snapshot may contain LOB, varray, or nested table column types. These entities can be stored in their own segments.l LOBs and varrays are stored in LOB segments, while a nested table is stored in a storage table. Changing Values for Storage Parametersl You can alter default storage parameters for tablespaces and specific storage parameters for individual segments if you so choose.l The INITIAL and MINEXTENTS storage parameters cannot be altered for an existing table, cluster, index, or rollback segment.l If only NEXT is altered for a segment, the next incremental extent is the size of the new NEXT, and subsequent extents can grow by PCTINCREASE as usual.l If both NEXT and PCTINCREASE are altered for a segment, the next extent is the new value of NEXT, and from that point forward, NEXT is calculated using PCTINCREASE as usual.Understanding Precedence in Storage Parametersl The storage parameters in effect at a given time are determined by the following types of SQL statements, listed in order of precedence:1. ALTER TABLE/CLUSTER/SNAPSHOT/SNAPSHOT LOG/INDEX/ROLLBACK SEGMENT statement2. CREATE TABLE/CLUSTER/SNAPSHOT/SNAPSHOT LOG/CREATE INDEX/ROLLBACK SEGMENT statement3. ALTER TABLESPACE statement4. CREATE TABLESPACE statement5. Oracle default valuesl Any storage parameter specified at the object level overrides the corresponding option set at the tablespace level.l The storage parameters for temporary segments always use the default storage parameters set for the associated tablespace.Deallocating SpaceViewing the High Water Markl Prior to deallocation, you can use the DBMS_SPACE package, which contains a procedure (UNUSED_SPACE) that returns information about the position of the high water mark（水印：占用和空闲） and the amount of unused space in a segment.l Within a segment, the high water mark indicates the amount of used space, or space that had been formatted to receive data.l If the segment is completely empty, you can release space using the TRUNCATE.DROP STORAGE statement.Issuing Space Deallocation Statementsl The following statements deallocate unused space in a segment (table, index or cluster). The KEEP clause is optional.ALTER TABLE table DEALLOCATE UNUSED KEEP integer;ALTER INDEX index DEALLOCATE UNUSED KEEP integer;ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;l When you explicitly identify an amount of unused space to KEEP, this space is retained while the remaining unused space is deallocated. If the remaining number of extents becomes smaller than MINEXTENTS, the MINEXTENTS value changes to reflect the new number. If the initial extent becomes smaller, the INITIAL value changes to reflect the new size of the initial extent.l If you do not specify the KEEP clause, all unused space (everything above the high water mark) is deallocated, as long as the size of the initial extent and MINEXTENTS are preserved. Thus, even if the high water mark occurs within the MINEXTENTS boundary, MINEXTENTS remains and the initial extent size is not reduced.Deallocating Space ExamplesA table consists of three extents. The first extent is 10K, the second is 20K, and the third is 30K. The high water mark is in the middle of the second extent, and there is 40K of unused space.ALTER TABLE dquon DEALLOCATE UNUSEDAll unused space is deallocated, leaving table DQUON with two remaining extents. The third extent disappears, and the second extent size is 10K.But, if you had issued the following statement specifying the KEEP keyword, then 10K above the high water mark would be kept, and the rest of the unused space would be deallocated from DQUON.ALTER TABLE dquon DEALLOCATE UNUSED KEEP 10K;In effect, the third extent is deallocated and the second extent remains intact.Further, if you deallocate all unused space from DQUON and keep 20K, as specified in the following statement, the third extent is cut to 10K, and the size of the second extent remains the same.ALTER TABLE dquon DEALLOCATE UNUSED KEEP 20K;Understanding Space Use of Datatypesl When creating tables and other data structures, you need to know how much space they will require.l Each datatype has different space requirements. The PL/SQL Users Guide and Reference and Oracle8i SQL Reference contain extensive descriptions of datatypes and their space requirements.SRC: Oracle8i Administrators Guide(19)General Management of Schema Objects Creating Multiple Tables and Views in a Single Operation Renaming Schema Objects Analyzing Tables, Indexes, and Clusters Truncating Tables and Clusters Managing Integrity Constraints Managing Object Dependencies Managing Object Name Resolution Displaying Information About Schema ObjectsCreating Multiple Tables and Views in a Single Operationl The CREATE SCHEMA statement can include CREATE TABLE, CREATE VIEW, and GRANT statements. You must have the privileges necessary to issue the included statements.CREATE SCHEMA AUTHORIZATION scott CREATE TABLE dept ( deptno NUMBER(3,0) PRIMARY KEY, dname VARCHAR2(15), loc VARCHAR2(25) CREATE TABLE emp ( empno NUMBER(5,0) PRIMARY KEY, ename VARCHAR2(15) NOT NULL, job VARCHAR2(10), mgr NUMBER(5,0), hiredate DATE DEFAULT (sysdate), sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(3,0) NOT NULL CONSTRAINT dept_fkey REFERENCES dept) CREATE VIEW sales_staff AS SELECT empno, ename, sal, comm FROM emp WHERE deptno = 30 WITH CHECK OPTION CONSTRAINT sales_staff_cnst GRANT SELECT ON sales_staff TO human_resources;Renaming Schema Objectsl To rename an object, you must own it. You can rename schema objects in either of the following ways:1. Drop and re-create the object2. Rename the object using the RENAME statementl If you drop and re-create an object, all privileges granted for that object are lost. Privileges must be re-granted when the object is re-created.l If you rename an object, integrity constraints, indexes, and grants made for the object are carried forward for the new name.l You cannot rename a stored PL/SQL program unit, public synonym, index, or cluster. To rename such an object, you must drop and re-create it.l Before renaming a schema object, consider the following effects:1. All views and PL/SQL program units dependent on a renamed object become invalid, and must be recompiled before next use.2. All synonyms for a renamed object return an error when used.Analyzing Tables, Indexes, and Clustersl You can analyze a table, index, or cluster to gather data about it, or to verify the validity of its storage format.l These schema objects can also be analyzed to collect or update statistics about specific objects.l A table, index, or cluster can be analyzed to validate the structure of the object.l A table or cluster can be analyzed to collect information about chained rows of the table or cluster.l To analyze a table, cluster, or index, you must own the table, cluster, or index or have the ANALYZE ANY system privilege.Using Statistics for Tables, Indexes, and Clustersl You can choose either of the following clauses of the ANALYZE statement for gathering statistics:1. COMPUTE STATISTICS: Oracle scans an entire object to gather data about the object.2. ESTIMATE STATISTICS: Oracle gathers representative information from portions of an object.Computing Statistics Using the ANALYZE StatementANALYZE TABLE emp COMPUTE STATISTICS;ANALYZE TABLE emp ESTIMATE STATISTICS;ANALYZE TABLE emp ESTIMATE STATISTICS SAMPLE 2000 ROWS;ANALYZE TABLE emp ESTIMATE STATISTICS SAMPLE 33 PERCENT;l In either case, if you specify a percentage greater than 50, or a number of row