ORACLE8i- P7

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:40

0
42
lượt xem
3
download

ORACLE8i- P7

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

ORACLE8i- P7: We want information… information...” Possibly you recognize these words as the primary interest of a somewhat clandestine group, and as told by a character called Number 2 to Patrick McGoohan’s character Number 6 (in the old TV show The Prisoner). Indeed, in this day, information is king, and the speedy, accurate, and reliable retrieval of this information is paramount.

Chủ đề:
Lưu

Nội dung Text: ORACLE8i- P7

  1. 230 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT addition of Oracle objects and partitioning, the format of the native database ROWID needed to be expanded, and the extended ROWID was born. Let’s look at each of these ROWID types in a bit more detail. Extended ROWIDs The extended ROWID is the native form of a ROWID in Oracle8i and was first intro- duced with Oracle8. Extended ROWIDs are relative only to a given tablespace. This is different from the relative ROWID that was present in Oracle7, which made every row in the database unique throughout the entire database. The extended ROWID is an 18-character value that represents four different values: Data object number This is a six-character representation of a 32-bit data object number. This number uniquely identifies the database segment. Each time an object changes, the data object number associated with this object changes as well. Thus, the data object number also serves as a version number for the object. Relative datafile number This is a three-character representation of the relative datafile number. The relative datafile number is relative to the tablespace to which that tablespace belongs. Thus, objects in different tablespaces will likely have different relative datafile numbers. Data block This is a six-character representation of the data block. This block number is relative to the tablespace to which it belongs. Row number in the block This is a three-character representation of the row in the block. An example of an extended ROWID is shown in the query below. SQL> SELECT rowid FROM test; ROWID ------------------ AAAAx/AABAAADhBAAA AAAAx/AABAAADhBAAB AAAAx/AABAAADhBAAC AAAAx/AABAAADhBAAD As you can see, the ROWID is a rather odd-looking thing. All of the different letters do not translate into anything usable by the DBA, except that they can be used directly in a SQL statement’s WHERE clause in certain cases (for example, if you store ROWIDs in a table). To effectively use an extended ROWID, you will probably need to employ the Oracle package DBMS_ROWID. This package has several functions that can be used to translate Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  2. MANAGING HEAP TABLES 231 the extended ROWID into its component parts, which can then be used to determine such things as which datafile or block the row is in. The DBMS_ROWID package also provides procedures and functions that can convert extended ROWIDs into restricted ROWIDs, and vice versa. Restricted ROWIDs The restricted ROWID can be determined from the extended ROWID, and an extended ROWID can be created from a restricted ROWID. The restricted ROWID has three components: • The first number in the restricted ROWID is an eight-digit number that repre- sents the block number that the row belongs in. This number relates to the V$LOCK column in the DBA_EXTENTS dictionary view. • The second number is a four-digit number that represents the row number in PA R T the block. The first row in each block is always a 0. II • The last number is a four-digit number that represents the absolute datafile to which the row belongs. This number relates to the FILE_ID column in the DBA_EXTENTS data dictionary view. Oracle Database An example of a restricted ROWID looks something like this: Administration 00000011.0000.0001 Each component of the restricted ROWID is separated by a dot, and unlike the extended ROWID, you do not need to use any package to determine the values being provided for each component. NOTE It is a common misconception that the restricted ROWID has been totally done away with. In fact, Oracle indexes still store restricted ROWIDs for each table row that they point to. Also, there are those who mistakenly suppose that there is some sort of ROWID conversion that occurs during a migration from Oracle 7.3 to Oracle8i. This is not the case, since the ROWIDs for tables are not stored in the database, but are generated on the fly when the ROWID pseudocolumn is used in a query. Creating Heap Tables To create a table, use the CREATE TABLE DDL statement. In its typical form, the CRE- ATE TABLE statement includes the following: • The table name Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  3. 232 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT • A list of column names, the datatype associated with the column, and certain constraints associated with that column (such as NOT NULL) • The tablespace that the table is assigned to • Table attributes such as PCTFREE and PCTUSED • The STORAGE clause associated with the table • Table attribute settings, such as CACHE or NOLOGGING To create a table in your own schema, you must have the CREATE TABLE privilege. If you want to create a table in another user’s schema, you need to have the CREATE ANY TABLE privilege. Also, you will need to have a sufficient QUOTA assigned to the tablespace(s) in which you will be creating tables (and any associated primary key indexes). If you create a primary key on the table, you also need to have the privileges to create an index, since the primary key is enforced through a unique index. NOTE A QUOTA is a limitation on how much space a user can use in a given tablespace. For example, if your quota is 0 (the default for new users), you will not be able to add any- thing to the tablespace. Users are assigned a QUOTA as a part of their configuration and setup. See Chapter 21 for more details on setting up user accounts. If you are using an Oracle TYPE in the table (we will discuss TYPEs in Chapter 8), you will need EXECUTE privileges on the TYPE you will be using, if your schema doesn’t own the TYPE. If you wish to grant access to the table to other users, you will need to have EXECUTE privileges on the TYPE with the ADMIN option. There are several miscellaneous types of settings that you may wish to consider when creating a table in Oracle. Table 6.1 lists these settings. TABLE 6.1: MISCELLANEOUS TABLE OPTIONS Option Default Purpose CACHE No Causes full table scans to be put on the head of the LRU list. This clause has been deprecated in favor of the use of the new KEEP BUFFER_POOL option. NOCACHE Yes Causes Oracle to follow the default full table scan behavior, by putting the blocks read for a full table scan on the tail of the LRU list. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  4. MANAGING HEAP TABLES 233 TABLE 6.1: MISCELLANEOUS TABLE OPTIONS (CONTINUED) Option Default Purpose NOLOGGING No Reduces redo generated by DML activity on the table. Table data inserted after backup will not be recover- able. This can reduce the time required to create the table. LOGGING Yes Sets the table in normal mode. MONITORING No Causes Oracle to record DML activity on the table. You can then use the DBMS_STATS.GATHER STALE procedure to update the statistics of just the tables that have changed. NOMONITORING Yes Sets the default for this parameter. PA R T II NOTE CACHE remains in Oracle only for backward compatibility. You should configure and use a keep buffer pool (described in Chapter 5) and assign an object to that buffer Oracle Database Administration pool rather than use the CACHE option. Creating a Table Listing 6.1 shows a simple example of using the CREATE TABLE statement. Listing 6.1: Creating a Heap Table CREATE TABLE PARENT (parent_id NUMBER PRIMARY KEY, last_name VARCHAR2(30) NOT NULL, first_name VARCHAR2(30) NOT NULL, middle_int CHAR(1), sex CHAR(1), married_status CHAR(1) ) TABLESPACE users PCTFREE 10 PCTUSED 60 STORAGE(INITIAL 10m NEXT 10m PCTINCREASE 0); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  5. 234 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT This creates a table named PARENT with six columns. The first column, PARENT_ID, is defined as a NUMBER datatype and the primary key of this table. Because the keyword PRIMARY KEY is included in the definition of the PARENT_ID column, an index will be created on this primary key column. Since we didn’t give the index a name in this CREATE TABLE statement, Oracle will assign a name to it by default. The system-generated name will always start with a SYS and then be followed by a series of numbers. Also, the primary key index will be created in the default tablespace of the user creating the table, and the index associated with the primary key will also have a system-generated name. The query and results below show the primary key created as a result of the creation of this table. SQL> SELECT table_name, index_name, tablespace_name 2 FROM user_indexes 3 WHERE table_name like ‘PARENT’; TABLE_NAME INDEX_NAME TABLESPACE_NAME ---------- -------------------- ---------------- PARENT SYS_C00780 USERS This shows that the system-generated index name is SYS_C00780. Also note that the primary key index has been assigned to the USERS tablespace, which is the default tablespace of the user who created the index. In the next section, you will see an example of how to give the primary key index a meaningful name and control where the primary key index is created. The LAST_NAME and FIRST_NAME columns in the table are VARCHAR2 datatypes that are sized to hold up to 30 characters each. The MIDDLE_INT, SEX, and MAR- RIED_STATUS columns are fixed-length CHAR datatypes, each holding 1 byte. Notice the NOT NULL keyword in the LAST_NAME and FIRST_NAME column defi- nitions. The NOT NULL keyword establishes a constraint on the table. A constraint is a rule. In this case, the NOT NULL constraint prohibits this column from having a NULL value in it at any time. Thus, you will need to provide a value for the column in each INSERT statement, by including it in the INSERT statement, through the use of a trigger (see Chapter 1 and Appendix F for more on triggers) or by defining a default value for the column. When you create a primary key, the columns of the primary key will be set to the NOT NULL status automatically. The other types of constraints you can create in Oracle are check, foreign key, NOT NULL, primary, and unique. When creating a table, you may define the various con- straints in the CREATE TABLE statement either for individual rows (check constraints) or a group of rows (primary keys). (See Chapter 7 for a detailed discussion of con- straints.) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  6. MANAGING HEAP TABLES 235 Viewing the Table After you’ve created the table, you can query the DBA_TABLES view to see your table: SELECT owner, table_name, tablespace_name, initial_extent, next_extent FROM dba_tables WHERE owner = ‘SCOTT’ AND table_name LIKE ‘PARENT’; OWNER TABLE_NAME TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT ----------- ------------ --------------- -------------- ----------- SCOTT PARENT USERS 10485760 10485760 Also, you can query the DBA_TAB_COLS data dictionary view and discover that you have rows in it for each of the columns in your table: SELECT owner, table_name, column_name, data_type, nullable PA R T 2 FROM dba_tab_columns 3 WHERE owner=’SCOTT’ II 4 AND table_name=’PARENT’ OWNER TABLE_NAME COLUMN_NAME DATA_TYPE N Oracle Database Administration --------- --------------- ------------------------------ ---------- - SCOTT PARENT PARENT_ID NUMBER N SCOTT PARENT LAST_NAME VARCHAR2 N SCOTT PARENT FIRST_NAME VARCHAR2 N SCOTT PARENT MIDDLE_INT CHAR Y SCOTT PARENT SEX CHAR Y SCOTT PARENT MARRIED_STATUS CHAR y The DBA_TABLES and DBA_TAB_COLS views are discussed in more detail in the “Viewing Table Information” section a bit later in this chapter. Specifying the Primary Key and Index Location Listing 6.2 shows another example of a CREATE TABLE statement. Listing 6.2: Creating a Table with Primary Key and Index Location Specifications CREATE TABLE children (child_id NUMBER CONSTRAINT pk_children PRIMARY KEY USING INDEX TABLESPACE indexes STORAGE (INITIAL 200k NEXT 200k), parent_id NUMBER, Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  7. 236 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT last_name VARCHAR2(30), first_name VARCHAR2(30), middle_int CHAR(1), medical_code VARCHAR2(30) CONSTRAINT children_check_upper CHECK (medical_code = UPPER(medical_code) ) ) TABLESPACE users PCTFREE 10 PCTUSED 60 STORAGE(INITIAL 10m NEXT 10m PCTINCREASE 0); This one looks a bit different from the CREATE TABLE statement in Listing 6.1. First, the second line, where the CHILD_ID column is being defined, includes a CONSTRAINT clause that names the primary key constraint. In this example, the primary key con- straint is named PK_CHILDREN. The associated index enforcing that primary key will also be called PK_CHILDREN. Next, the USING INDEX clause is associated with the first column. It is through this clause that you can control in which tablespace the primary key index is created, as well as define storage characteristics for that index with a separate STORAGE clause. Finally, note the check constraint in the definition of the MEDICAL_CODE col- umn. The definition of the constraint begins with the word CONSTRAINT, followed by the name of the constraint, then the keyword CHECK. Following the CHECK key- word is the actual constraint. This example uses the UPPER function to ensure that any values for MEDICAL_CODE are entered in uppercase. If someone tried to enter a lowercase medical code in this column, the transaction would fail and the following error would be returned: ORA-02290: check constraint (SCOTT.CHECK_UPPER) violated Creating a Two-Column Primary Key and Foreign Key Constraint Let’s look at another example of a CREATE TABLE statement. The example shown in Listing 6.3 re-creates the CHILD table with a two-column primary key and a foreign key constraint to the PARENT key. Listing 6.3: Creating a Table with a Two-Column Primary Key and For- eign Key Constraint CREATE TABLE children (child_id NUMBER, parent_id NUMBER, last_name VARCHAR2(30), Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  8. MANAGING HEAP TABLES 237 first_name VARCHAR2(30), middle_int CHAR(1), medical_code VARCHAR2(30) CONSTRAINT children_check_upper CHECK (medical_code = UPPER(medical_code) ), CONSTRAINT pk_children PRIMARY KEY (child_id, parent_id) USING INDEX TABLESPACE indexes STORAGE (INITIAL 10k NEXT 10k), CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent (parent_id) ) TABLESPACE users PCTFREE 10 PCTUSED 60 STORAGE(INITIAL 10m NEXT 10m PCTINCREASE 0) PA R T Both the primary key constraint and the foreign key constraint are created in the II same clause where the table columns are created. Since the example creates a two- column primary key, it adds a separate CONSTRAINT clause, rather than just the PRIMARY KEY clause at the end of each column. In other words, you cannot create a Oracle Database two-column primary key like this: Administration CREATE TABLE children (child_id NUMBER PRIMARY KEY, parent_id NUMBER PRIMARY KEY, last_name VARCHAR2(30), first_name VARCHAR2(30), middle_int CHAR(1) ); This code would result in an error because you are incorrectly trying to define a two- column primary key. Creating a Table from an Existing Table Definition The CREATE TABLE AS SELECT (CTAS) command allows you to create a table from the definition of an existing table, or create a new table based on a join of one or more tables. This is handy to have for a variety of reasons. First, this is one method you can use to defragment a table. Also, you can use CTAS to create a separate table with a sample set of data. Listing 6.4 shows an example of using CTAS to create a table from two existing tables. Listing 6.4: Using the CREATE TABLE AS SELECT (CTAS) Command -- Using CTAS from an existing table (or tables in this case). -- We are also limiting the number of rows returned to 9. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  9. 238 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT CREATE TABLE create_view_table_one TABLESPACE users STORAGE (INITIAL 100k NEXT 100k BUFFER_POOL RECYCLE) AS SELECT a.parent_id AS parent_id, a.last_name AS parent_last_name, a.first_name AS paremt_first_name, b.last_name AS child_last_name, b.first_name AS child_first_name FROM parent a, children b WHERE a.parent_id=b.parent_id AND rownum < 10; You can also use CTAS to create a table using a view, as shown in Listing 6.5. This example executes this statement using a view as the source for the new table. Listing 6.5: Creating a Table Using a View with CTAS -- Create the view first CREATE VIEW v_parent_child AS SELECT a.parent_id AS parent_id, a.last_name AS parent_last_name, a.first_name AS parent_first_name, b.last_name AS child_last_name, b.first_name AS child_first_name FROM parent a, children b WHERE a.parent_id=b.parent_id; -- Now, create a table via CTAS using the view CREATE TABLE ctas_from_view_pc TABLESPACE data STORAGE (INITIAL 100k NEXT 100k ) AS SELECT * FROM v_parent_child; NOTE CTAS is particularly handy for use with LogMiner, which is discussed in Chapter 14. This is because some of the critical views that LogMiner populates with information are persistent only for the duration of the user session that created those views. Using CTAS to create a table allows you to retain the LogMiner results. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  10. MANAGING HEAP TABLES 239 Creating a Temporary Table A temporary table is like a regular table in many ways. It is created by the use of the GLOBAL TEMPORARY keywords in the CREATE TABLE command. The data in a tem- porary table is visible to only the session that created that data, yet the temporary table is available for use by all transactions. The temporary table will persist until it is dropped; however, the data in the tem- porary table is not persistent. Using the ON COMMIT keywords, you can choose from two different options that control when the data in the temporary table is removed. If you create the temporary table using the ON COMMIT DELETE clause (the default), the session data in the table is removed as soon as the transaction completes. Using the ON COMMIT PRESERVE clause will cause that session’s data to be removed only after the session is disconnected. A temporary table uses temporary segments to store session data, and along with PA R T the data in the segments, the segments themselves are de-allocated. Since temporary tables use temporary segments, they use the temporary tablespace defined for the user II who is using the temporary table. Therefore, no TABLESPACE clause, STORAGE clause, or physical attributes clauses are allowed in the CREATE TABLE statement. When an index is created on a temporary table, its data segments are temporary as well. There- Oracle Database fore, a USING INDEX clause is not permitted when creating a temporary table. The Administration CONSTRAINT clause is allowed, but some constraints are not permitted. Here is a summary of the restrictions on temporary tables: • They can be only hash tables (so index-organized tables and clusters are not allowed as temporary tables). • You cannot partition a temporary table. • Most constraints are not supported, but you can specify check constraints and NOT NULL constraints. You can create temporary tables with primary keys, but foreign key relationships are not supported. • Temporary tables do not support nested tables or VARRAYs, which are part of Oracle’s object-oriented features. • Parallel DML and parallel queries are not supported on temporary tables. • You cannot use a temporary table as a part of a distributed transaction. Listing 6.6 shows an example of creating a temporary table. Listing 6.6: Creating a Temporary Table CREATE GLOBAL TEMPORARY TABLE temp_children (child_id NUMBER CONSTRAINT pk_temp_children PRIMARY KEY, parent_id NUMBER, Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  11. 240 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT last_name VARCHAR2(30), first_name VARCHAR2(30), middle_int CHAR(1) ); Altering Tables You can alter many of the settings of a table through the ALTER TABLE DDL command. The following are some of the things you can do with the ALTER TABLE command: • Alter or drop column definitions, sizes, and constraints • Alter table storage parameters, including NEXT and FREELISTS • Enable constraints (without validating them) and disable constraints • Move or rename a table • Manually allocate an extent to a table • Modify table parameters • Add, drop, move, or alter partitions • Modify several table attributes, such as CACHE and NOLOGGING There are certain parameters that cannot be changed once an object is created. In particular, you cannot change the INITIAL parameter in the STORAGE clause. The following are several examples of using the ALTER TABLE command: • To alter the storage clause of a table: ALTER TABLE dodo STORAGE (NEXT 100k); • To enable a constraint on the table: ALTER TABLE my_table ENABLE CONSTRAINT fk_my_table_01; • To add a unique constraint to a table: ALTER TABLE phone_list ADD user_code CONSTRAINT line_code UNIQUE; • To add a primary key to the table: ALTER TABLE student ADD CONSTRAINT pk_student_id PRIMARY KEY (student_id) USING INDEX TABLESPACE pk_index STORAGE (INITIAL 100k NEXT 100k); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  12. MANAGING HEAP TABLES 241 • To add a column: ALTER TABLE student ADD COLUMN (maiden_name VARCHAR2(20)); • To drop a column: ALTER TABLE student DROP COLUMN maiden_name; De-allocating Unused Table Space You may discover that a table has had excessive space allocated to its structure. It might be that the unused space would be better used in another object. When this is the case, you can de-allocate space from the table using the DEALLOCATE UNUSED clause in an ALTER TABLE statement. When this command is issued, Oracle will de-allocate space from the table. Two settings influence how much space you can de-allocate with this command: PA R T The high-water mark You can only de-allocate space in the table from the high-water mark up. Recall that the high-water mark is the highest position in the II table that has ever been used to store data. Oracle will de-allocate space from the end of the table being de-allocated (this same command can be used for indexes, table partitions, and clusters as well) to the high-water mark. The optional KEEP Oracle Database clause allows you to reserve some space in the table, rather than de-allocate all of Administration the space above the high-water mark. The MAXEXTENTS clause Oracle will not allow you to de-allocate space such that the number of extents allocated to the object will fall below the MAX- EXTENTS clause of the object from which you are de-allocating space. The de- allocation process can actually cause Oracle to modify the INITIAL STORAGE clause value as well as the MINEXTENTS value, if the DEALLOCATE command will cause space to be de-allocated below these points. Here are examples of using the DEALLOCATE UNUSED clause. ALTER TABLE student DEALLOCATE UNUSED; ALTER TABLE parent DEALLOCATE UNUSED KEEP 200m; Dropping Tables Removing a table (hash, temporary, or index-organized) is done via the DROP TABLE statement. In most cases, this is a straightforward process. The DROP TABLE com- mand includes an optional CASCADE CONSTRAINTS clause. The CASCADE CON- STRAINTS clause will cause all associated referential integrity constraints to be removed when you drop the table. If there is a foreign key associated with the table to be Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  13. 242 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT dropped, you will need to use the CASCADE CONSTRAINTS clause in order to drop the table. Here are examples of using the DROP TABLE statement: DROP TABLE dodo; DROP TABLE my_table CASCADE CONSTRAINTS; Viewing Table Information Several data dictionary views can be used to manage tables in Oracle. These views are used to locate the table and identify its columns and the other information in the table. Each of the views discussed here comes not only in the DBA_ variety, but also in USER_ and ALL_ versions. The DBA_TABLES View The DBA_TABLES view provides basic table information, including the owner of the table, the name of the table, the tablespace the table resides in, and many of the sta- tistics collected by the Oracle statistics collection process. If you need to find out who owns a table or what its storage characteristics are, this is the place to go. The following is a listing of the description of the view. SQL> DESC dba_tables Name Null? Type ----------------------------------------- -------- ------------ OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) TABLESPACE_NAME VARCHAR2(30) CLUSTER_NAME VARCHAR2(30) IOT_NAME VARCHAR2(30) PCT_FREE NUMBER PCT_USED NUMBER INI_TRANS NUMBER MAX_TRANS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER LOGGING VARCHAR2(3) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  14. MANAGING HEAP TABLES 243 BACKED_UP VARCHAR2(1) NUM_ROWS NUMBER BLOCKS NUMBER EMPTY_BLOCKS NUMBER AVG_SPACE NUMBER CHAIN_CNT NUMBER AVG_ROW_LEN NUMBER AVG_SPACE_FREELIST_BLOCKS NUMBER NUM_FREELIST_BLOCKS NUMBER DEGREE VARCHAR2(10) INSTANCES VARCHAR2(10) CACHE VARCHAR2(5) TABLE_LOCK VARCHAR2(8) SAMPLE_SIZE NUMBER PA R T LAST_ANALYZED DATE II PARTITIONED VARCHAR2(3) IOT_TYPE VARCHAR2(12) TEMPORARY VARCHAR2(1) SECONDARY VARCHAR2(1) Oracle Database Administration NESTED VARCHAR2(3) BUFFER_POOL VARCHAR2(7) ROW_MOVEMENT VARCHAR2(8) GLOBAL_STATS VARCHAR2(3) USER_STATS VARCHAR2(3) DURATION VARCHAR2(15) SKIP_CORRUPT VARCHAR2(8) MONITORING VARCHAR2(3) Here is an example of a query against the view and its results: SELECT owner, table_name, initial_extent, next_extent, pct_free, pct_used FROM dba_tables WHERE owner=’SCOTT’; OWNER TABLE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_FREE PCT_USED ------ --------------- -------------- ----------- ---------- ---------- SCOTT BONUS 10240 10240 10 40 SCOTT CHILD 10240 10240 10 40 SCOTT DEPT 10240 10240 10 40 SCOTT EMPLOYEE 102400 102400 10 40 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  15. 244 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT SCOTT GIJOE 10240 10240 10 40 SCOTT PARENT 10240 10240 10 40 SCOTT PLAN_TABLE 10240 10240 10 40 SCOTT RANK 10240 10240 10 40 SCOTT SALGRADE 10240 10240 10 40 SCOTT STUDENTS 10240 10240 10 40 SCOTT ZOCALO 10240 10240 10 40 The DBA_TAB_COLUMNS View The DBA_TAB_COLUMNS view is used to locate specific information about columns in a table, including the name of the column, the datatype of the column, the preci- sion of the column, and statistical information gathered by the Oracle statistics col- lection process. The following is a listing of the description of this view. SQL> DESC dba_tab_columns Name Null? Type ----------------------------------------- -------- ------------ OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) DATA_TYPE VARCHAR2(106) DATA_TYPE_MOD VARCHAR2(3) DATA_TYPE_OWNER VARCHAR2(30) DATA_LENGTH NOT NULL NUMBER DATA_PRECISION NUMBER DATA_SCALE NUMBER NULLABLE VARCHAR2(1) COLUMN_ID NOT NULL NUMBER DEFAULT_LENGTH NUMBER DATA_DEFAULT LONG NUM_DISTINCT NUMBER LOW_VALUE RAW(32) HIGH_VALUE RAW(32) DENSITY NUMBER NUM_NULLS NUMBER NUM_BUCKETS NUMBER LAST_ANALYZED DATE SAMPLE_SIZE NUMBER CHARACTER_SET_NAME VARCHAR2(44) CHAR_COL_DECL_LENGTH NUMBER Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  16. MANAGING HEAP TABLES 245 GLOBAL_STATS VARCHAR2(3) USER_STATS VARCHAR2(3) AVG_COL_LEN NUMBER Here is an example of a query against the view and its results: SELECT owner, table_name, column_name, data_type, data_length, column_id FROM dba_tab_columns WHERE owner=’SCOTT’ AND table_name=’GIJOE’ ORDER BY column_id; OWNER TABLE_NAME COLUMN_NAM DATA_TYPE DATA_LENGTH COLUMN_ID ------ --------------- ---------- ---------- ----------- ---------- PA R T SCOTT GIJOE PART_ID NUMBER 22 1 SCOTT GIJOE TOY_NAME VARCHAR2 30 2 II SCOTT GIJOE TOY_RANK NUMBER 22 3 The DBA_EXTENTS View Oracle Database The DBA_EXTENTS view provides information about extent allocations for a given Administration object in Oracle, including tables and indexes. Here is a description of the view: SQL> DESC dba_extents Name Null? Type ----------------------------------------- -------- ------------ OWNER VARCHAR2(30) SEGMENT_NAME VARCHAR2(81) PARTITION_NAME VARCHAR2(30) SEGMENT_TYPE VARCHAR2(18) TABLESPACE_NAME VARCHAR2(30) EXTENT_ID NUMBER FILE_ID NUMBER BLOCK_ID NUMBER BYTES NUMBER BLOCKS NUMBER RELATIVE_FNO NUMBER The following is an example of using this view to determine how many extents an object has been allocated. SELECT owner, segment_name, segment_type, count(*) “Total_extents” FROM dba_extents Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  17. 246 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT WHERE owner=’SCOTT’ GROUP BY owner, segment_name, segment_type ORDER BY 1,3,2; OWNER SEGMENT_NAME SEGMENT_TYPE Total_extents ------ --------------- ------------------ ------------- SCOTT TEST CLUSTER 2 SCOTT TEST2 CLUSTER 1 SCOTT IX_TEST_RGF INDEX 1 SCOTT IX_TEST_RGF_01 INDEX 1 SCOTT PK_CHILD INDEX 1 SCOTT PK_CHILDREN INDEX 1 SCOTT PK_PARENT INDEX 1 SCOTT AUDIT_TABLE TABLE 1 SCOTT BONUS TABLE 1 SCOTT CHILD TABLE 1 SCOTT DEPT TABLE 1 SCOTT DUMMY TABLE 1 SCOTT EMPLOYEE TABLE 1 SCOTT GIJOE TABLE 1 SCOTT HOLD_EVENTS TABLE 4 The DBA_SEGMENTS View The DBA_SEGMENTS view provides a look at the individual segments in the database, including tables, indexes, and clusters. The following is a description of the view. SQL> DESC dba_segments Name Null? Type ----------------------------------------- -------- ------------ OWNER VARCHAR2(30) SEGMENT_NAME VARCHAR2(81) PARTITION_NAME VARCHAR2(30) SEGMENT_TYPE VARCHAR2(18) TABLESPACE_NAME VARCHAR2(30) HEADER_FILE NUMBER HEADER_BLOCK NUMBER BYTES NUMBER BLOCKS NUMBER EXTENTS NUMBER INITIAL_EXTENT NUMBER Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  18. MANAGING INDEXES 247 NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER RELATIVE_FNO NUMBER BUFFER_POOL VARCHAR2(7) Here is an example of using this view to get information about segments: SELECT owner, segment_name, segment_type, tablespace_name, sum(bytes) bytes FROM dba_segments WHERE owner=’SCOTT’ PA R T GROUP BY owner, segment_name, segment_type, tablespace_name II ORDER BY 5; OWNER SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME BYTES Oracle Database ------ --------------- ---------- --------------- ---------- Administration SCOTT AUDIT_TABLE TABLE USERS 10,240 SCOTT BONUS TABLE USERS 10,240 SCOTT CHILD TABLE USERS 10,240 SCOTT DUMMY TABLE USERS 10,240 SCOTT GIJOE TABLE USERS 10,240 SCOTT IX_TEST_RGF INDEX INDEXES 10,240 SCOTT HOLD_EVENTS TABLE USERS 71,680 SCOTT EMPLOYEE TABLE USERS 102,400 SCOTT TEST2 CLUSTER SYSTEM 102,400 SCOTT NEWTABLE TABLE USERS 4,382,720 Managing Indexes Indexes are important structures in Oracle. They can be an incredible boon to perfor- mance, and I don’t think there is any database that should not have at least one index on just about every table in the tablespace. Indexes give the Optimizer many more access paths to consider when choosing how to execute a given SQL statement. Even small lookup tables that have indexes associated with them will perform better when they are being joined to other tables. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  19. 248 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT Index Fundamentals Oracle uses different kinds of indexes. The primary index types are B*Tree indexes and bitmap indexes. Oracle8i introduces function-based indexes. Let’s look at how these indexes are handled internally and when using an index is helpful. N OTE Indexes do not store columns with values that are NULL. Thus, if you have a query with a limiting condition that is NULL, Oracle will not be able to use any index to sat- isfy that particular condition. However, queries with NOT NULL limitation criteria can use indexes. B*Tree Indexes The standard type of index that Oracle uses is called a B*Tree index. Think of a B*Tree index as something like a tree (hence the name). At the top of the tree is a single node called the root. Extending up (or down if you are in the south- ern hemisphere) from this node is a series of leaf blocks. There may be one or more leaf blocks associated with the root node. In the root, there are pointers to the differ- ent data stored in each leaf node. For example, in a phone book, in the head node, all last names from A to L might have a pointer to the first leaf block, and all last names from M to Z might have a pointer to the second leaf block as shown in Figure 6.2. This progression will continue through a third (and perhaps more) level of nodes. Now suppose that you are looking for the name Adams. You would go from the root node to the first node below it, because the first node below the head node contains all last names beginning with A through L. In the second-level node, you find pointers for the values A through L again. This time, they are pointing to another level of leaf blocks below this level. Say, for example, that there are 13 leaf nodes below the middle leaf node. The middle leaf node, for the name beginning with A, would point to the node below it that contains the A records. Finally, in the bottom leaf node, you would search for Adams, which you would find quickly. B*Tree indexes can be associated with one or more columns in a given table. Along with the values of the specific index columns, the B*Tree index also contains the ROWID of each row associated with an index entry. Oracle will return the ROWID to another process during the execution of the statement. Typically, that process will then access the table via ROWID, which is the fastest method of getting to distinct data in a table. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  20. MANAGING INDEXES 249 FIGURE 6.2 Branch Node The structure of a =M B*Tree index Leaf Nodes A–E F–L M–R S–Z Adams (ROWID) Freeman (ROWID) Leaf Nodes Edwards (ROWID) Arnold (ROWID) Lewis (ROWID) In most cases, an index search is fast. Because the indexed data is distributed among several nodes, Oracle can eliminate which leaf nodes do not need to be scanned, thus reducing the I/O needed to find the desired record. Also, the data in PA R T these nodes is stored in ordered fashion. However, there are times when indexes can II be inefficient. You should build B*Tree indexes on columns with high cardinality, or a high degree of distinct values. If your column were populated with only Ys and Ns, it would not make a good candidate for a B*Tree index in most cases. This is because there are about two to four times the I/O operations required to get one row from a Oracle Database Administration table using an index than are required to do a full table scan. For example, suppose that you have a table with 10,000 rows. It has a column called YES_NO, which is populated with 6000 values of Y and 4000 values of N. If you want to count all records with a Y, how many I/O operations would that take? For a full table scan, you are looking at 10,000 logical I/O operations (one for each row). With an indexed lookup, there will be 6000 logical I/O operations to the index, plus 6000 lookups in the table, plus 1 or 2 I/O operations for the root and leaf block lookups needed to get to the proper leaf nodes. That makes 12,002 logical I/O opera- tions for an indexed lookup, compared with 10,000 for a full table scan. In this case, the index is not the best way to get the information. Now suppose that the YES_NO column in the sample table has 9999 Ys in it and just 1 N. In this case, a full table scan requires a 10,000-row lookup. With the index lookup, only three or four I/O operations are involved (two to three for the index lookup, and one for the table lookup). So, in this case, the index is the best choice. Bitmap Indexes Bitmap indexes were introduced in Oracle 7.3, and they can be very powerful if used properly. Bitmap indexes are used to provide performance improvements for queries against nonselective columns in a table, if these nonselective columns are part of the selectivity criteria (the WHERE clause). For example, performance might improve Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
Đồng bộ tài khoản