ORACLE8i- P8

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

0
33
lượt xem
3
download

ORACLE8i- P8

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

ORACLE8i- P8: 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- P8

  1. 270 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT Indexed Clusters An indexed cluster (or just cluster) contains two or more tables. The cluster key is a col- umn or multiple columns that can be used to join the data in these tables. When the tables of the cluster are populated with data, the data in the tables is stored in the same block, and the rows for both tables are related based on the cluster key columns. This has the basic effect of denormalizing the table. Thus, if you are going to cluster tables, they should be tightly related based on the keys. The cluster key of the cluster is defined when you create the cluster. It can consist of up to 16 columns, and the cluster key size is limited to something like half the database block size. Also, the cluster key cannot consist of a LONG, LONG RAW, or LOB datatype (CLOB, BLOB, and so on). One benefit of clustering tables is that the cluster key is stored only once. This results in a slight improvement in performance, because the overall size of the clus- tered tables is smaller than the size of two individual tables storing the same data, so less I/O is required. Another benefit of clusters is that, unlike indexes, they do not brown-out. Thus, performance of SELECT statements should not be negatively impacted by ongoing DML activity, as can happen with indexes. Clusters can improve join performance, but this can be at the cost of slower perfor- mance on scans of the individual tables in the cluster and any DML activity on the cluster. To avoid problems, you should cluster only tables that are commonly joined together and that have little DML activity. Hash Clusters A hash cluster is an alternative to storing data in a table and then creating an index on that table. A hash cluster is the default type of cluster that will be created by the CRE- ATE CLUSTER command. In a hash cluster, the cluster key values are converted into a hash value. This hash value is then stored along with the data associated with that key. The hash value is calculated by using a hashing algorithm, which is simply a mathematical way of gen- erating a unique identifier. (The same keys would generate the same hash value, of course.) You control the number of hash keys through the HASHKEYS parameter of the CREATE CLUSTER command. Thus, the total number of possible cluster key val- ues is defined when the cluster is created. Be careful to correctly choose this number, or you will find that keys may end up being stored together. There are two different kinds of hash clusters: • With a normal hash cluster, Oracle will convert the values of the WHERE clause, if they contain the cluster key, to a hash value. It will then use the hash value as an offset value in the hash cluster, allowing Oracle to quickly go to the row being requested. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  2. MANAGING CLUSTERS 271 • With a single-row hash cluster, the hash keys relate directly to the key in the table stored in hash cluster. This eliminates the scan that would be required on a nor- mal hash cluster, since Oracle would scan the entire cluster for a single table lookup by default. When choosing the cluster key, you should consider only columns with a high degree of cardinality. You should also be careful about using hash clusters when a table will be subject to a great deal of range searches, such as searches on date ranges. Hash clusters generally are used when there is a constant need to look up specific and unique values, such as those you might find in a primary key or a unique index. For the hashing algorithm, you have three options. The first is to use Oracle’s inter- nal algorithm to convert the cluster key values into the correct hash value. This works well in most cases. You can also choose to use the cluster key if your cluster key is some uniform value, such as a series of numbers generated by a sequence. The other PA R T option is a user-defined algorithm in the form of a PL/SQL function. II Creating Clusters You use the CREATE CLUSTER command to create both indexed clusters and hash clusters. In order to create a cluster in your schema, you need the CREATE CLUSTER Oracle Database Administration privilege. To create a cluster in another schema, you must have the CREATE ANY CLUSTER privilege. You also need the appropriate QUOTA set for the tablespace in which you wish to create the clusters. Creating an Indexed Cluster You use the CREATE CLUSTER command to create indexed clusters in your database. Listing 6.17 shows an example of creating an indexed cluster. Listing 6.17: Creating an Indexed Cluster CREATE CLUSTER parent_child (parent_id NUMBER) INDEX SIZE 512 STORAGE (INITIAL 100k NEXT 100k); It is the keyword INDEX in the CREATE CLUSTER command that makes this an indexed cluster. Omit this keyword, and Oracle will default to creating a hash cluster. When you create the cluster, you define how many rows will be identified with each cluster key by using the SIZE parameter. The value associated with the SIZE key- word tells Oracle how much space to reserve for all rows with the same hash value, and this value should be a multiple of the database block size. If SIZE is not a multiple Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  3. 272 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT of the database block size, it will be rounded up to the next multiple of the database block size. For example, if you have 1600 bytes left in the block, and SIZE is set at 512, then you will be able to store three cluster key sets within that block. WARN I NG It is important to set the SIZE parameter correctly for both indexed clus- ters and hash clusters. If you set SIZE too small, you can cause rows to be chained, which can cause additional I/O. After you have created the cluster, you can add tables to the cluster. Then you need to create a cluster index before you can use the cluster or the tables in the cluster. Adding Tables to an Indexed Cluster Listing 6.18 shows an example of creating two tables and adding them to the cluster created in Listing 6.17. Listing 6.18: Adding Tables to a Cluster 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) ) CLUSTER parent_child (parent_id); CREATE TABLE children (child_id NUMBER CONSTRAINT pk_children PRIMARY KEY USING INDEX TABLESPACE indexes STORAGE (INITIAL 200k NEXT 200k), parent_id NUMBER, 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) ) ) CLUSTER parent_child(parent_id); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  4. MANAGING CLUSTERS 273 Notice that you can still define primary keys on the tables, use the USING INDEX command to define where the primary key index is to be stored, and include the STORAGE clause. You can create indexes on tables in clusters, just as you would for any other table. Creating a Cluster Index Once you have set up the cluster, you need to create a cluster index so that you can add rows to our cluster. Until this step is done, no data can be added to any of the tables that exist in the cluster. To create the cluster index, you use the CREATE INDEX command using the ON CLUSTER keyword, as shown in the following example: CREATE INDEX ic_parent_children ON CLUSTER parent_child; After creating the cluster index, you can work with the tables in the cluster. PA R T NOTE If you accidentally drop the cluster index, you will not lose the data in the cluster. II However, you will not be able to use the tables in the cluster until the cluster index is re-created. Oracle Database Administration Creating a Hash Cluster Creating a hash cluster is similar to creating an indexed cluster. The differences are that you omit the INDEX keyword and add the HASHKEYS keyword. Listing 6.19 shows an example of using a CREATE CLUSTER statement to create a hash cluster. Listing 6.19: Creating a Hash Cluster CREATE CLUSTER parent_child (parent_id NUMBER) SIZE 512 HASHKEYS 1000 STORAGE (INITIAL 100k NEXT 100k); The SIZE and HASHKEYS keywords are used to calculate how much space to allo- cate to the cluster. The SIZE keyword defines how much of each block will be used to store a single set of cluster key rows. This value determines the number of cluster key values that will be stored in each block of the cluster. SIZE is defined in bytes, and you can also append a k or m to indicate that the number is in kilobytes or megabytes. The HASHKEYS clause (which is not used when creating an indexed cluster) defines the number of cluster key values that are expected in the cluster. Oracle will round up the number chosen to the next nearest prime number. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  5. 274 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT It is important to try to calculate the HASHKEYS and SIZE values as exactly as pos- sible, but it is also sometimes a difficult task. If you already have the data stored in another database, or perhaps in a table that you were thinking of moving to a hash cluster, it might be easier to determine the settings for these values. You could simply set HASHKEYS to the number of unique rows in the table, based on a select set of columns that make up either a primary or unique key, or on a pseudo unique key if one does not exist. The SIZE value is a bit more complicated. SIZE could be calculated by first taking the overall size of the data in the object that you are thinking about moving to a hash cluster, and dividing that size by the number of unique key values. This will give you some idea of where to start making the SIZE parameter. (I would add on a bit for over- head and a fudge factor.) By default, Oracle will allocate one block for every cluster key value (which is potentially very expensive). Also, the value given by SIZE cannot be greater than the block size of the database. If it is, Oracle will use the database block size instead. TI P Like many other things in the database world, there is no exact science to calculat- ing cluster sizes. It’s important to get as close as you can to an accurate figure, but this may not be possible until you have seen how the data is actually going to come in and load. In practice, sizing may require one or two reorganizations of the object. So, sizing typically involves doing your best to calculate the right numbers, and then adjusting those numbers based on ongoing operations. Both SIZE and HASHKEYS can have a significant impact on performance. If you allocate too much space (by making either SIZE or HASHKEYS too large), you will be wasting space. Since fewer data rows will exist per block, full scans (not using the hash key) or range scans of the hash cluster will be degraded. Also, Oracle uses the values of SIZE and HASHKEYS, along with the values of INITIAL and NEXT, to deter- mine the initial extent allocations for the hash cluster. The total amount of space that will be allocated to a hash cluster when it is created is the greater of SIZE * HASHKEYS or INITIAL. Thus, when the hash cluster is created, all of the initial space for the expected number of hash keys is mapped out in a struc- ture called a hash table. Subsequent extents are created as overflow space and will be generated based on the NEXT parameter of the STORAGE clause. Any rows added to the cluster will first be added to the initial block in the hash table, based on the hash value of the cluster key column(s) in the row. If the block that is assigned to that clus- ter key is full, then the row will be stored in an overflow block, and a pointer will be Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  6. MANAGING CLUSTERS 275 stored in the block where the row should have gone. This can cause additional I/O operations, and it is why it is important to size your clusters correctly. WARN I NG Incorrectly setting the SIZE value can cause chaining to occur. This is because Oracle does not guarantee that any hashed data will remain in a given block (and often, this may not even be possible). Carefully set the SIZE parameter to avoid chaining. Once you have created the hash cluster, you add tables to the cluster, just as you would with an index cluster (see Listing 6.18). You do not need to create a cluster index, as you do for an indexed cluster. After you add the tables to the hash cluster, the cluster is ready for use. PA R T Altering Clusters II You can use the ALTER CLUSTER command to allocate an additional extent to the cluster in an index cluster (you cannot allocate an additional extent to a hash cluster) or to de-allocate an unused extent. You can also modify the STORAGE clause or the Oracle Database PCTFREE and PCTUSED settings for the cluster, as well as the other settings of the Administration physical attributes clause. Here is an example of allocating an additional extent and modifying the STORAGE parameters of a cluster: ALTER CLUSTER parent_child STORAGE (NEXT 200k); Dropping Clusters To drop an indexed cluster, you must first drop the underlying cluster index, and then you must drop the underlying tables of the cluster. There is no way to decluster a table. Thus, you may need to use the CREATE TABLE AS SELECT command to move the data from tables in the cluster to tables outside the cluster. After you remove the cluster index and the tables of the index, you can use the DROP CLUSTER command. If you don’t care about the data in the tables of the clus- ter, you may use the optional parameter INCLUDING TABLES in the DROP CLUSTER command, and Oracle will remove the underlying tables for you. You may also need to include the CASCADE CONSTRAINTS clause if the tables have referential integrity constraints that will need to be dropped. Here is an example of dropping the PAR- ENT_CHILD cluster created in Listing 6.19, including the tables: DROP CLUSTER parent_child INCLUDING TABLES CASCADE CONSTRAINTS; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  7. 276 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT Viewing Cluster Information The DBA_CLUSTERS view provides information about the clusters in your database. The following is a description of the view. SQL> DESC dba_clusters Name Null? Type ----------------------------------------- -------- ------------ OWNER NOT NULL VARCHAR2(30) CLUSTER_NAME NOT NULL VARCHAR2(30) TABLESPACE_NAME NOT NULL VARCHAR2(30) PCT_FREE NUMBER PCT_USED NOT NULL NUMBER KEY_SIZE NUMBER INI_TRANS NOT NULL NUMBER MAX_TRANS NOT NULL NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NOT NULL NUMBER MAX_EXTENTS NOT NULL NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER AVG_BLOCKS_PER_KEY NUMBER CLUSTER_TYPE VARCHAR2(5) FUNCTION VARCHAR2(15) HASHKEYS NUMBER DEGREE VARCHAR2(10) INSTANCES VARCHAR2(10) CACHE VARCHAR2(5) BUFFER_POOL VARCHAR2(7) SINGLE_TABLE VARCHAR2(5) Here is an example of a query against the DBA_CLUSTERS view and its results: SELECT owner, cluster_name, tablespace_name FROM dba_clusters WHERE owner NOT LIKE ’SYS’; OWNER CLUSTER_NAME TABLESPACE ------ ------------------------------ ---------- SCOTT TEST USERS SCOTT TEST2 SYSTEM Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  8. MANAGING ROLLBACK SEGMENTS 277 Managing Rollback Segments When creating a database, the DBA needs to carefully consider the number, size, and extents to be allocated to rollback segments in the database. Rollback segments are used to provide rollback of incomplete transactions, read consistency, and database recovery. Planning Rollback Segments There are as many differing opinions about how to initially size a rollback segment as there are models of cars on the road. The answer to the question of how many roll- back segments and what size to make them is the same as the answer to many such DBA questions: It depends. PA R T There are a few considerations for planning rollback segments: • The average size of a transaction II • The total number of concurrent transactions • The type and frequency of transactions Oracle Database The main concerns with rollback segments in terms of performance and opera- Administration tional success are the appropriate sizing of the rollback segment and contention for a given rollback segment. If you do not have enough rollback segments allocated, con- tention for rollback segments can occur. You also need to have the appropriate sizing of the rollback segment tablespace. A single large job may cause a rollback segment to extend and take up the entire table- space. If the rollback segment has OPTIMAL set (which should always be the case!), it will eventually shrink after the transaction using that rollback segment has completed or failed. In the meantime, however, any attempt to extend other rollback segments will lead to transaction failure. Some DBAs create a separate large rollback segment in its own tablespace for large transactions. I personally don’t like this approach, because these large rollback seg- ments are rarely used, so they are a waste of space. I prefer to lump that extra space into one rollback segment tablespace that will allow the rollback segments to grow with the large transaction. Then I set the OPTIMAL parameter so the rollback segment will shrink back to its correct size later on. NOTE Keep in mind the concept of I/O distribution with regard to rollback segments. I’ve seen more than one DBA just plug the rollback segment tablespace datafiles on one disk, and then wonder why the system had such poor response time. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  9. 278 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT I generally will create rollback segments according to the following formula: total extents = 2 × (expected number of concurrent transactions / number of overall rollback segments) For example, if I expect 20 concurrent transactions and create 5 rollback segments, this formula leads to 8 extents each. I generally round this up to 10 extents, for a total of 5 rollback segments of 10 extents each. You should probably not have any rollback segment with more than about 30 extents initially. If you do, you need to either resize the extents or add another rollback segment. I normally will make sure that the total size of each rollback segment (depending on how much space is available) is about 1.3 times the size of the largest table in the database. This allows me to modify all of the rows of any table without any problems. (Of course, if you have some particularly large tables, this might not be possible.) Finally, I always throw as much space as I can to the rollback segment tablespace (par- ticularly in a production database). I am not fond of transaction failures due to lack of rollback segment space. For test or development systems where less disk space is avail- able, I use smaller rollback segments. Again, it all depends on the environment. TI P If you find that you have users that are constantly blowing your tablespaces up with ad-hoc queries that are poorly written, you should look into metering your users’ resource use with Oracle’s resource control facilities, rather then just allowing them to extend a tablespace to eternity. The truth is that most DBAs guess at what they think is the right number and size of rollback segments, and then monitor the system for contention. Usually, when you first create a database, you have little idea of how many users will really be on it or how big the average transaction or largest table will be. After you create your initial set of rollback segments, you need to monitor their usage. Chapter 15 provides details on monitoring rollback segment use. If you find that rollback segments have a significant number of extends, shrinks, or wraps, as determined from the V$ROLLSTAT performance view, you will need to rework the extent size and perhaps the number of extents in the rollback segment. You may also want to review the V$WAITSTAT performance view for classes that include the word UNDO in them. If you see waits for these segment types, you probably need to add rollback segments to your database. Finally, you should use a uniform extent management policy with rollback seg- ments. This implies that the INITIAL and NEXT storage parameters for a rollback seg- ment will always be the same size. Also, INITIAL and NEXT should be the same for all Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  10. MANAGING ROLLBACK SEGMENTS 279 rollback segments in any given tablespace. This helps eliminate any fragmentation issues in the rollback segment tablespaces. Creating Rollback Segments To create a rollback segment, use the CREATE ROLLBACK SEGMENT command. Cre- ating a rollback segment is like creating any other segment in most respects. You define a STORAGE clause for the object, and you can define to which tablespace the rollback segment is assigned. Listing 6.20 shows an example. Listing 6.20: Creating a Rollback Segment CREATE ROLLBACK SEGMENT rbs01 TABLESPACE rbs STORAGE (INITIAL 1m NEXT 1m OPTIMAL 10m MINEXTENTS 10); PA R T The OPTIMAL option within the STORAGE clause allows you to define a size that you want Oracle to shrink the rollback segment back to. Thus, the rollback segment II will expand as required, but Oracle will shrink it back down to the correct size later. As noted earlier, you should always use the OPTIMAL cause to prevent problems with a rollback segment taking too much tablespace. Oracle Database The OPTIMAL and MINEXTENTS clauses cross-check each other. Thus, you cannot Administration have an OPTIMAL parameter that will cause the rollback segment to drop below the value defined by MINEXTENTS. As noted in the previous section, it is strongly encouraged that you make all of your extents uniform in size (thus, make sure that INITIAL and NEXT are set to the same value). If you choose to make INITIAL or NEXT larger, make sure it is a multiple of the smaller value. When you create a rollback segment it is not initially available for use. You will need to use the ALTER ROLLBACK SEGMENT command (discussed next) to bring the rollback segment online. Also, you will want to add the rollback segment to the data- base parameter file so that it will be brought online immediately when the database is started. You use the ROLLBACK_SEGMENT parameter in the init.ora file to accom- plish this. Altering Rollback Segments You can use the ALTER ROLLBACK SEGMENT command to alter the storage charac- teristics of a rollback segment, bring it online after creation, or take it offline before dropping it. You can also use the ALTER ROLLBACK segment command to force the rollback segment to shrink back to the size defined by OPTIMAL or, optionally, to a Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  11. 280 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT size you define in the ALTER ROLLBACK SEGMENT SHRINK command. Here are some examples of using the ALTER ROLLBACK SEGMENT command: ALTER ROLLBACK SEGMENT rbs01 ONLINE; ALTER ROLLBACK SEGMENT rbs01 OFFLINE; ALTER ROLLBACK SEGMENT rbs01 SHRINK; ALTER ROLLBACK SEGMENT rbs01 STORAGE (OPTIMAL 9m MINEXTENTS 9); If you attempt to take a rollback segment offline while it is in use, it will go into an OFFLINE PENDING state (as you can see in the STATUS column of V$ROLLSTAT). The rollback segment will be taken offline immediately after the user transaction has com- pleted. Since the ALTER ROLLBACK SEGMENT command is DDL, it will do an implied commit. Thus, if the user whose transaction was holding up the rollback seg- ment before it was taken offline performed some sort of DML operation and did not commit it, the ALTER ROLLBACK SEGMENT command would commit it for that user. Dropping Rollback Segments Dropping a rollback segment is done with the DROP ROLLBACK SEGMENT command: DROP ROLLBACK SEGMENT rbs01; Oracle will generate an error message (ORA-1545) if you attempt to drop a rollback segment if it is in an ONLINE or OFFLINE PENDING state. Viewing Rollback Segment Information Three data dictionary views provide the primary information about rollback seg- ments: DBA_ROLLBACK_SEGS, V$ROLLSTAT, and V$ROLLNAME. The DBA_ROLLBACK_SEGS View The DBA_ROLLBACK_SEGS view provides a list of all rollback segments that exist on the system, both online and offline. This view also includes information such as storage information (INITIAL, NEXT, and OPTIMAL) and the tablespace the rollback segment is occupying. Here is a description of the view: SQL> DESC dba_rollback_segs Name Null? Type ----------------------------------------- -------- ------------ SEGMENT_NAME NOT NULL VARCHAR2(30) OWNER VARCHAR2(6) TABLESPACE_NAME NOT NULL VARCHAR2(30) SEGMENT_ID NOT NULL NUMBER FILE_ID NOT NULL NUMBER Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  12. MANAGING ROLLBACK SEGMENTS 281 BLOCK_ID NOT NULL NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NOT NULL NUMBER MAX_EXTENTS NOT NULL NUMBER PCT_INCREASE NUMBER STATUS VARCHAR2(16) INSTANCE_NUM VARCHAR2(40) RELATIVE_FNO NOT NULL NUMBER Here is an example of a query against the DBA_ROLLBACK_SEGS view and its results: SELECT segment_name, owner, tablespace_name FROM dba_rollback_segs PA R T ORDER BY segment_name; II SEGMENT_NAME OWNER TABLESPACE_NAME ------------------------------ ------ --------------- RB01 SYS RBS Oracle Database RB02 SYS RBS Administration RB03 SYS RBS RBS01 SYS RBS SYSTEM SYS SYSTEM TEST_ME SYS LOCAL_UNIFORM The V$ROLLSTAT and V$ROLLNAME Views The V$ROLLSTAT and V$ROLLNAME views are typically used together. V$ROLL- STAT provides performance information about the individual rollback segments, as shown here. SQL> DESC v$rollstat Name Null? Type ----------------------------------------- -------- ------------ USN NUMBER EXTENTS NUMBER RSSIZE NUMBER WRITES NUMBER XACTS NUMBER GETS NUMBER WAITS NUMBER OPTSIZE NUMBER Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  13. 282 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT HWMSIZE NUMBER SHRINKS NUMBER WRAPS NUMBER EXTENDS NUMBER AVESHRINK NUMBER AVEACTIVE NUMBER STATUS VARCHAR2(15) CUREXT NUMBER CURBLK NUMBER Unfortunately, as you can see, this view does not contain the name of the rollback segment, but rather the Undo Segment Number (USN) of the segment. The V$ROLLNAME view resolves the USN to the rollback segment name, as you can see in its description: SQL> DESC v$rollname Name Null? Type ----------------------------------------- -------- ------------ USN NUMBER NAME NOT NULL VARCHAR2(30) Here is a sample query that uses both these views and its results: SELECT a.name, b.extents, b.rssize, b.waits, b.extends, b.shrinks FROM v$rollname a, v$rollstat b WHERE a.usn=b.usn; NAME EXTENTS RSSIZE WAITS EXTENDS SHRINKS ---------- ---------- ---------- ---------- ---------- ---------- SYSTEM 8 407552 0 0 0 RB02 16 1636352 0 0 0 Notice that not all of the rollback segments appear. Only those rollback segments that are online or have an OFFLINE PENDING status will appear in the V$ROLLSTAT and V$ROLLNAME views. Managing Views A view is a logical representation of a SQL statement that is stored in the database. Views are useful when certain SQL statements are commonly used on a given database. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  14. MANAGING VIEWS 283 View definitions are stored in the data dictionary, and they do not physically store data. As a result, views do not require any storage. Prior to Oracle8i and the virtual private database (or fine-grained access control), views were frequently used to provide secured access to data within Oracle. Even now, fine-grained access control tends to be complicated to implement (and it makes per- formance tuning more difficult), so views are still sometimes uses to provide security. Also, views can be used to tune otherwise untunable code. You can rename the table that the code is attempting to access, and then create a view with the name of the table. In this view, you can include hints that make accessing the table much more efficient. This solution doesn’t always work, but it does in some cases. You can use views to INSERT, UPDATE, and DELETE records from the database, with a few provisions in regards to key-preserved tables. You can even create triggers on views. Another use of views is to provide access to certain features in Oracle8i SQL that PA R T are not yet available in Oracle8i PL/SQL, such as the CUBE and ROLLUP functions. II You simply create the view providing the appropriate query, and then have your PL/SQL code call that view. Sometimes, a view is the only way around such limitations. Oracle Database Administration NOTE Sometimes, certain third-party tools are not “up to snuff” with Oracle8i. They have problems dealing with some of Oracle8i’s more advanced features, such as partitions. You may need to create views to allow your tool to perform some of these operations. Creating Views You use the CREATE VIEW statement to create a view. You might also want to use the CREATE OR REPLACE VIEW command, should the view already exist. Here is an example of creating a view: CREATE OR REPLACE VIEW v_emp AS SELECT empno, ename FROM emp WHERE ename BETWEEN ‘A%’ AND ‘D%’; This example creates a simple view over the EMP table that displays only the employee number and name for all employees whose names begin with the letters between A and D. The CREATE VIEW command also has a FORCE option that will cause Oracle to create the view even if the underlying tables of the view are not yet created, as in this example: CREATE OR REPLACE FORCE VIEW v_emp Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  15. 284 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT AS SELECT empno, ename FROM emp WHERE ename BETWEEN ‘A%’ AND ‘D%’; From time to time, views may become invalid. You can recompile a view with the ALTER VIEW command using the COMPILE keyword: ALTER VIEW v_emp COMPILE; Finally, when you are just sick and tired of your view, you can drop it with the DROP VIEW command: DROP VIEW v_emp; Using Your Views The way you create views can have an impact on performance, just as the way you create SQL statements can have an impact on performance on those SQL statements. If you have two tables (or more) in the view joined together, the keys involved in the join cannot be updated. Basically, if your UPDATE operation will cause changes to rows in both tables, it will fail. Also, if in the SELECT portion of the view, you choose to include a column from a table that cannot be updated, you will find that your abil- ity to remove records from that view will be compromised. To understand what can happen, let’s look at some examples: DROP VIEW giview; DROP TABLE gijoe; DROP TABLE rank; CREATE TABLE gijoe ( part_id NUMBER PRIMARY KEY, Toy_name VARCHAR2(30), Toy_rank NUMBER); CREATE TABLE rank (toy_rank NUMBER PRIMARY KEY, rank_name VARCHAR2(30) ); INSERT INTO rank VALUES (1, ’Private’); INSERT INTO rank VALUES (2, ’Sgt’); INSERT INTO rank VALUES (3, ’Captain’); INSERT INTO gijoe VALUES (10, ’Joe’,1); INSERT INTO gijoe VALUES (20, ’Sarge’,2); INSERT INTO gijoe VALUES (30, ’Cappie’,3); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  16. MANAGING VIEWS 285 -- Now, create a view. CREATE VIEW giview AS SELECT a.part_id, a.toy_name, b. rank_name FROM gijoe a, rank b WHERE a.toy_rank=b.toy_rank; -- Update a record… this works. UPDATE giview SET toy_name=’Joseph’ WHERE part_id=10; -- Insert a new record… this will fail! INSERT INTO giview VALUES (4,’Barbie’,’Sarge’); Note that the INSERT fails. This is because one of the columns that the example is trying to INSERT INTO is not key preserved. To be key preserved, each column being updated must relate to the primary key in the base table that the column is in. In this PA R T example, we are trying to update a column in the RANK table (RANK_NAME). How- ever, the primary key of the RANK table (TOY_RANK) is not part of the UPDATE state- II ment (or the view). Since we are not able to reference the primary key columns of the RANK table in the INSERT statement (again, because these columns are not part of the view we are updating), the TOY_NAME column cannot be updated. The bottom line Oracle Database is that with the way this view is constructed, we cannot insert records into the under- Administration lying base tables through this view. We might rebuild the view in a slightly different way to try to get the UPDATE operation to succeed: DROP VIEW giview; -- Now, create a view. CREATE VIEW giview AS SELECT a.part_id, a.toy_name, b.toy_rank, b. rank_name FROM gijoe a, rank b WHERE a.toy_rank=b.toy_rank; -- Insert a new record… this will fail! INSERT INTO giview (part_id, toy_name, toy_rank) VALUES (4,’Barbie’,2); Still, it fails with this error: ERROR at line 2: ORA-01776: cannot modify more than one base table through a join view Why is this? Another rule with views is that only one base table can be modified at a time. Look closely at the view definition. You will see that the example uses the Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  17. 286 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT RANK.TOY_RANK column instead of the GIJOE.TOY_RANK column. Thus, it is trying to update two tables instead of one. Let’s rewrite the view again: DROP VIEW giview; -- Now, create a view. CREATE VIEW giview AS SELECT a.part_id, a.toy_name, a.toy_rank, b. rank_name FROM gijoe a, rank b WHERE a.toy_rank=b.toy_rank; -- Insert a new record… this will fail! INSERT INTO giview (part_id, toy_name, toy_rank) VALUES (4,’Barbie’,2); Once we use the GIJOE.TOY_RANK column rather than the RANK.TOY_RANK col- umn, we have met the requirement that only one table can be updated at a time. Unfortunately, since this view accesses A.TOY_RANK, we will have a problem if we want to use the view to insert a record into the RANK table, as shown in this example: INSERT INTO giview (toy_rank, rank_name) VALUES (10, ‘General’); Same old song—we get an ORA-1776 error (the patriotic Oracle message!) again. So, how do we work around this problem? We might try to include both columns in the view: DROP VIEW giview; -- Now, create a view. CREATE OR REPLACE VIEW giview AS SELECT a.part_id, a.toy_name, a.toy_rank as girank, b.toy_rank as update_rank, b. rank_name FROM gijoe a, rank b WHERE a.toy_rank=b.toy_rank; INSERT INTO giview (update_rank, rank_name) VALUES (10, ‘General’); But this doesn’t work either. Basically, given this view, you will not be able to do INSERT operations on columns that are in the RANK table. This is because Oracle will not allow you to change (or add) a column that is part of the join criteria of the view. For example, if you try to update the RANK_NAME column in GIVIEW, this impacts Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  18. MANAGING VIEWS 287 the join of the view, because the TOY_RANK column is the join column in the view. Because of this, you cannot update the RANK_NAME in the GIVIEW table. However, if you want to change a TOY_NAME column value, this is acceptable because it is not part of the join between the two tables, and thus will not impact the change. TI P INSTEAD OF triggers can be used as an effective solution to the problem of non-key preserved DML. These triggers can be written to override DML actions against a view and implement those changes in the base tables of the view instead. Refer to the Oracle docu- mentation for more information about INSTEAD OF triggers. PA R T II Views as Performance Problems Views can be performance problems, as well as performance god-sends. Be cautious when you create views that they only bring back the rows you really need. Also be care- Oracle Database ful of situations where you have views, stacked on views, stacked on views. The per- Administration formance of such designs can be terrible. I knew a developer who wrote code to use such a stacked-view design. All the devel- oper really needed was 2 columns of information, yet he was going through a view that was returning some 25 columns of information, via a stack of about four different lev- els of views. Each level contained joins that you would not believe (including outer joins, which views seem to have particularly hard times with). The developer came to me asking why his code performed so poorly. Once I had him rewrite his query to not use the view, but directly query the two-table join that he needed, his code’s performance was increased dramatically. This just serves to demon- strate that one of the follies of views is that they can make developers lazy in design- ing efficient code. Using Inline Views Oracle also offers an inline view that is very handy. An inline view is part of a SQL statement. It allows you, in the body of the SQL statement, to define the SQL for a Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  19. 288 CHAPTER 6 • ORACLE SCHEMA OBJECT MANAGEMENT view that the SQL statement will use to resolve its query. Here is an example of using an inline view: SELECT * FROM /* This starts the inline view */ (SELECT owner, table_name, num_rows FROM dba_tables WHERE num_rows IS NOT NULL ORDER BY num_rows desc) /* This is the end of the inline view */ WHERE ROWNUM < 10; This example gives the top-ten largest tables in the database, in terms of row count. Note that this particular bit of code will work only with Oracle8i. This is because the ORDER BY operation that occurs in the body of the inline view was not supported before Oracle8i. The ORDER BY clause sorts the result set of the data, before it is passed to the main query. Viewing View Information The DBA_VIEWS view is used to assist in the management of views in the database. It provides information about the view, such as its owner, its name, and the SQL used to create the view. Here is a description of DBA_VIEW: SQL> DESC dba_views Name Null? Type ----------------------------------------- -------- -------------- OWNER NOT NULL VARCHAR2(30) VIEW_NAME NOT NULL VARCHAR2(30) TEXT_LENGTH NUMBER TEXT LONG TYPE_TEXT_LENGTH NUMBER TYPE_TEXT VARCHAR2(4000) OID_TEXT_LENGTH NUMBER OID_TEXT VARCHAR2(4000) VIEW_TYPE_OWNER VARCHAR2(30) VIEW_TYPE VARCHAR2(30) The following is an example of a query that uses the DBA_VIEW and its result: SELECT owner, view_name FROM dba_views WHERE owner != ’SYS’; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  20. MANAGING VIEWS 289 OWNER VIEW_NAME ------ -------------------- SYSTEM AQ$DEF$_AQCALL SYSTEM AQ$DEF$_AQERROR SCOTT GIVIEW In this example, you see that the SCOTT user owns a view called GIVIEW. If you wanted to see the SQL statement that as associated with that view, you could query the TEXT column of the DBA_VIEWS view. Note that TEXT is a LONG, therefore you will probably need to use the SET LONG and SET ARRAY commands in SQL*Plus. PA R T II Oracle Database Administration 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