Result cache
Download 80.78 Kb.
|
- Navigate this page:
- INVISIBLE AND VISIBLE INDEXES
- Property Description
- ADDING COLUMN TO A TABLE WITH DEFAULT VALUE
- As Oracle Documents rightly says
- GENERATED ALWAYS AS
- Then I added a virtual column as follows to emp table
- GENERATED ALWAYS AS
| RESULT CACHE
Caching has been a feature of Oracle for as long as most of us can remember. Over the many years and versions, Oracle has continually expanded its caching mechanisms. We are all familiar with the buffer cache, keep cache, library cache, shared pool, PGA/UGA and so on. In addition to the many data and cursor caches included in its architecture, Oracle has also enhanced caching support for common programming techniques; for example, scalar subquery caching, global temporary tables and associative arrays. In 11g, Oracle has extended this further by adding result caching to both its server and client architecture. Following are result caching features in 11g: •query result cache; •PL/SQL function result cache QUERY RESULT CACHE As its name suggests, the query result cache is used to store the results of SQL queries for re-use in subsequent executions. By caching the results of queries, Oracle can avoid having to repeat the potentially time-consuming and intensive operations that generated the resultset in the first place (for example, sorting/aggregation, physical I/O, joins etc). The cache results themselves are available across the instance (i.e. for use by sessions other than the one that first executed the query) and are maintained by Oracle in a dedicated area of memory. Unlike our homegrown solutions using associative arrays or global temporary tables, the query result cache is completely transparent to our applications. It is also maintained for consistency automatically, unlike our own caching programs. We will examine the features of the query result cache in more detail throughout this article. database configuration We will begin by looking at some of the database configuration required to use the query result cache. The initialisation parameters are as follows. SQL> SELECT name, value, isdefault2 FROM v$parameter 3 WHERE name LIKE 'result_cache%'; NAME VALUE ISDEFAULT ---------------------------------- ------------------ --------- result_cache_mode MANUAL TRUE result_cache_max_size 1081344 TRUE result_cache_max_result 5 TRUE result_cache_remote_expiration 0 TRUE 4 rows selected. A brief explanation of each of these parameters is as follows. •result_cache_mode: the result cache can be enabled in three ways: via hint, alter session or alter system. Default is MANUAL which means that we need to explicitly request caching via the RESULT_CACHE hint; •result_cache_max_size: this is the size of the result cache in bytes. The cache is allocated directly from the shared pool but is maintained separately (for example, flushing the shared pool will not flush the result cache); •result_cache_max_result: this specifies the highest percentage of the cache that is able to be used by a single resultset (default 5%); and •result_cache_remote_expiration: this specifies the number of minutes for which a resultset based on a remote object can remain valid. The default is 0 which means that resultsets dependant on remote objects will not be cached. The cache size is dynamic and can be changed either permanently or until the instance is restarted. We will roughly double the size of the cache for this article and verify that we have a larger result cache as follows (note this was run as SYSDBA). SQL> ALTER SYSTEM SET result_cache_max_size = 2M SCOPE = MEMORY; System altered. SQL> SELECT name, value FROM v$parameter WHERE name = 'result_cache_max_size'; NAME VALUE ---------------------------------------- ------------------------- result_cache_max_size 2097152 1 row selected. The setup for the result cache is simple and should be a one-time DBA operation. We will now see some examples of caching and using results below.
As we saw earlier, the default caching mode for this instance is MANUAL. This means that query resultsets will not be cached unless we instruct Oracle to do so by using the RESULT_CACHE hint. In our first example below, we will manually cache the results of a simple aggregate query. Note that the examples in this article are all based on the SH sample schema. First, we verify our cache mode as follows. SQL> SELECT value FROM v$parameter WHERE name = 'result_cache_mode'; VALUE
---------------- MANUAL
1 row selected. We will now run a query and cache its results. We will run this through Autotrace because we are interested in both the workload statistics and the execution plan (Autotrace will also conveniently suppress the query output). SQL> set autotrace traceonly SQL> set timing on SQL> SELECT /*+ RESULT_CACHE */ p.prod_name,SUM(s.amount_sold) AS total_revenue SUM(s.quantity_sold) AS total_sales FROM sales s, products p WHERE s.prod_id = p.prod_id GROUP BY p.prod_name; 71 rows selected. Elapsed: 00:00:05.00 Using the RESULT_CACHE hint, we have instructed Oracle to cache the results of this aggregate query. We can see that it returned 71 rows and took 5 seconds to execute. We will see the amount of work that Oracle did to generate these results further below, but first we will see the execution plan (note that this is a theoretical explain plan and not the real execution plan, but is a good approximation in this system). Execution Plan ---------------------------------------------------------- Plan hash value: 504757596 ----------------------------------------------------------------------- ... ----------------- | Id | Operation | Name | Rows | ... | Pstart| Pstop | ----------------------------------------------------------------------- ... ----------------- | 0 | SELECT STATEMENT | | 71 | ... | | | | 1 | RESULT CACHE | 091zc7mvn8ums36mbd2gqac4h0 | | ... | | | | 2 | HASH GROUP BY | | 71 | ... | | | |* 3 | HASH JOIN | | 72 | ... | | | | 4 | VIEW | VW_GBC_5 | 72 | ... | | | | 5 | HASH GROUP BY | | 72 | ... | | | | 6 | PARTITION RANGE ALL| | 918K| ... | 1 | 28 | | 7 | TABLE ACCESS FULL | SALES | 918K| ... | 1 | 28 | | 8 | TABLE ACCESS FULL | PRODUCTS | 72 | ... | | | ----------------------------------------------------------------------- ... ----------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ITEM_1"="P"."PROD_ID") Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=3; dependencies=(SH.SALES, SH.PRODUCTS); parameters=(nls); name="SELECT /*+ RESULT_CACHE */ p.prod_name , SUM(s.amount_sold) AS total_revenue , SUM(s.quantity_sold) AS total_" Note the highlighted sections of the execution plan. It contains some new information, which we can summarise as follows: •first, we can see a new operation, "RESULT CACHE" at operation ID=1. This is the last step in this particular example and it is telling us that Oracle will cache the results of the preceding operations; •second, we see a system-generated name beside the RESULT CACHE operation. This is used internally as a key for looking up and matching SQL statements to their cached results; •third, we see a new section in the plan report on the result cache metadata for this query. This section includes information such as the objects that the results are dependant on (i.e. to maintain cache coherency) and the leading part of the SQL text that generated the results. Finally, the Autotrace report displays the work that Oracle performed to generate these results. Statistics ---------------------------------------------------------- 14871 recursive calls 0 db block gets 4890 consistent gets 1745 physical reads 0 redo size 3526 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 136 sorts (memory) 0 sorts (disk) We can see a range of I/O and CPU activity in these figures, as expected. We will now test the new query result cache by running the same query a second time and comparing the Autotrace report, as follows. SQL> SELECT /*+ RESULT_CACHE */ 2 p.prod_name 3 , SUM(s.amount_sold) AS total_revenue 4 , SUM(s.quantity_sold) AS total_sales 5 FROM sales s 6 , products p 7 WHERE s.prod_id = p.prod_id 8 GROUP BY 9 p.prod_name; 71 rows selected. Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 504757596 ----------------------------------------------------------------------- ... ----------------- | Id | Operation | Name | Rows | ... | Pstart| Pstop | ----------------------------------------------------------------------- ... ----------------- | 0 | SELECT STATEMENT | | 71 | ... | | | | 1 | RESULT CACHE | 091zc7mvn8ums36mbd2gqac4h0 | | ... | | | | 2 | HASH GROUP BY | | 71 | ... | | | |* 3 | HASH JOIN | | 72 | ... | | | | 4 | VIEW | VW_GBC_5 | 72 | ... | | | | 5 | HASH GROUP BY | | 72 | ... | | | | 6 | PARTITION RANGE ALL| | 918K| ... | 1 | 28 | | 7 | TABLE ACCESS FULL | SALES | 918K| ... | 1 | 28 | | 8 | TABLE ACCESS FULL | PRODUCTS | 72 | ... | | | ----------------------------------------------------------------------- ... ----------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ITEM_1"="P"."PROD_ID") Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=3; dependencies=(SH.SALES, SH.PRODUCTS); parameters=(nls); name="SELECT /*+ RESULT_CACHE */ p.prod_name , SUM(s.amount_sold) AS total_revenue , SUM(s.quantity_sold) AS total_" Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 3526 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 71 rows processed Starting with the statistics report, we can see that this time Oracle has done very little work. In fact it has performed none of the I/O, sorting or recursive SQL that was required to answer our query the first time. Oracle has recognised that the query can be satisfied from the result cache and simply returned the pre-computed answer to us instead, in approximately 0.1 seconds. Interestingly, the execution plan remains the same (this is to be expected because the SQL is not optimised a second time) but is now slightly misleading. None of the plan operations actually take place once we have a reusable resultset, but the presence of the RESULT CACHE operation should alert us to the fact that we might already have a cached set of results. In fact, we can use the information supplied in this plan to verify the existence of a cached resultset for ourselves, which we will examine later in this article. We have now seen a simple example of query result caching. Minimising the amount of work that Oracle has to do to answer our query will reduce the time it takes. It also follows that the more work Oracle can avoid, the better the gains from caching. automatic result caching The alternative result_cache_mode to MANUAL is FORCE. This can be session or system specific and in this mode Oracle will attempt to set or use cached query results when it can, unless we use the NO_RESULT_CACHE hint. We will see an example of this mode below. We will set the mode to FORCE at a session level, then repeat our previous SQL example minus the RESULT_CACHE hint. First we set the result_cache_mode as follows. SQL> ALTER SESSION SET result_cache_mode = FORCE; Session altered. With Autotrace set, we will now run the same aggregate statement from our manual example above. Note that the only modification to this SQL statement is to remove the RESULT_CACHE hint. SQL> set autotrace traceonly SQL> set timing on SQL> SELECT p.prod_name 2 , SUM(s.amount_sold) AS total_revenue 3 , SUM(s.quantity_sold) AS total_sales 4 FROM sales s 5 , products p 6 WHERE s.prod_id = p.prod_id 7 GROUP BY 8 p.prod_name; 71 rows selected. Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 504757596 ----------------------------------------------------------------------- ... ----------------- | Id | Operation | Name | Rows | ... | Pstart| Pstop | ----------------------------------------------------------------------- ... ----------------- | 0 | SELECT STATEMENT | | 71 | ... | | | | 1 | RESULT CACHE | 091zc7mvn8ums36mbd2gqac4h0 | | ... | | | | 2 | HASH GROUP BY | | 71 | ... | | | |* 3 | HASH JOIN | | 72 | ... | | | | 4 | VIEW | VW_GBC_5 | 72 | ... | | | | 5 | HASH GROUP BY | | 72 | ... | | | | 6 | PARTITION RANGE ALL| | 918K| ... | 1 | 28 | | 7 | TABLE ACCESS FULL | SALES | 918K| ... | 1 | 28 | | 8 | TABLE ACCESS FULL | PRODUCTS | 72 | ... | | | ----------------------------------------------------------------------- ... ----------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ITEM_1"="P"."PROD_ID") Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=3; dependencies=(SH.SALES, SH.PRODUCTS); parameters=(nls); name="SELECT p.prod_name , SUM(s.amount_sold) AS total_revenue , SUM(s.quantity_sold) AS total_sales FROM sales s , " This is really interesting! We can see that in FORCE mode without a RESULT_CACHE hint, Oracle has used the same plan as the "equivalent" statement in MANUAL mode with the RESULT_CACHE hint (though in reality they are different cursors). Note that the result cache hashes are the same, although the name parameter is different. Given this, we would expect Oracle to use our previously cached results, which we can verify from the Statistics report, as follows. Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 3526 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 71 rows processed We can see quite clearly that Oracle has returned the results that it cached from the manual-mode, hinted SQL. It recognises their equivalence despite the difference in the SQL text. Continuing in FORCE result_cache_mode, we will now execute a new aggregate statement, as follows. SQL> SELECT p.prod_name 2 , MAX(s.quantity_sold) AS max_sales 3 FROM sales s 4 , products p 5 WHERE s.prod_id = p.prod_id 6 GROUP BY 7 p.prod_name; 71 rows selected. Elapsed: 00:00:00.59 Execution Plan ---------------------------------------------------------- Plan hash value: 504757596 ----------------------------------------------------------------------- ... ----------------- | Id | Operation | Name | Rows | ... | Pstart| Pstop | ----------------------------------------------------------------------- ... ----------------- | 0 | SELECT STATEMENT | | 71 | ... | | | | 1 | RESULT CACHE | 12scakxrxks3p73w5nxr69wn3j | | ... | | | | 2 | HASH GROUP BY | | 71 | ... | | | |* 3 | HASH JOIN | | 72 | ... | | | | 4 | VIEW | VW_GBC_5 | 72 | ... | | | | 5 | HASH GROUP BY | | 72 | ... | | | | 6 | PARTITION RANGE ALL| | 918K| ... | 1 | 28 | | 7 | TABLE ACCESS FULL | SALES | 918K| ... | 1 | 28 | | 8 | TABLE ACCESS FULL | PRODUCTS | 72 | ... | | | ----------------------------------------------------------------------- ... ----------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ITEM_1"="P"."PROD_ID") Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=2; dependencies=(SH.SALES, SH.PRODUCTS); parameters=(nls); name="SELECT p.prod_name , MAX(s.quantity_sold) AS max_sales FROM sales s , products p WHERE s.prod_id = p.prod_id GROUP " Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1731 consistent gets 0 redo size 2687 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 71 rows processed In FORCE mode, Oracle has cached the results of our query without us requesting it. We can see this in the execution plan. We will run the query a second time, as follows. SQL> SELECT p.prod_name 2 , MAX(s.quantity_sold) AS max_sales 3 FROM sales s 4 , products p 5 WHERE s.prod_id = p.prod_id 6 GROUP BY 7 p.prod_name; 71 rows selected. Elapsed: 00:00:00.00 << ...plan removed... >> Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 2687 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 71 rows processed The wall-clock timing and Statistics report shows clearly that Oracle has retrieved the cached results. dynamic result cache views So far we have seen the effects of caching with the two modes of the query result cache. We will now look a little deeper into what happens with the query cache and what information Oracle exposes about it. We can search the data dictionary for the result cache dynamic views, as follows. SQL> SELECT view_name 2 FROM dba_views 3 WHERE view_name LIKE 'V_$RESULT_CACHE%'; VIEW_NAME ------------------------------ V_$RESULT_CACHE_DEPENDENCY V_$RESULT_CACHE_MEMORY V_$RESULT_CACHE_OBJECTS V_$RESULT_CACHE_STATISTICS 4 rows selected. Oracle provides four dynamic views. We will have a brief look at these below (refer to the online documentation for more details: a link is provided at the end of this article). We will start with V$RESULT_CACHE_OBJECTS, which exposes the most information about our cached query results. SQL> DESC v$result_cache_objects; As we can see, there is a great deal of information potentially available. We will query a few important columns below. SQL> SELECT name, type , cache_id, row_count FROM v$result_cache_objects ORDER BY creation_timestamp; NAME TYPE CACHE_ID ROW_COUNT ------------------------------ ---------- -------------------------- ---------- SH.PRODUCTS Dependency SH.PRODUCTS 0 SH.SALES Dependency SH.SALES 0 SELECT /*+ RESULT_CACHE */ Result 091zc7mvn8ums36mbd2gqac4h0 71 SELECT p.prod_name Result 12scakxrxks3p73w5nxr69wn3j 71 SELECT DECODE('A','A','1','2' Result 0y8dgk314f9f8bz05qsrrny8u8 1 5 rows selected. We can see two types of information in this view: dependencies and results. We will discuss dependencies later, but the results' names clearly align with the queries we have run so far (the SUM and MAX aggregate sales queries). The last query in the output is executed by SQL*Plus. Remember from earlier that we executed two SQL statements (equivalent except for the RESULT_CACHE hint) and note the CACHE_ID values. There is only one entry for the two statements due to the fact that they shared a result set and hashed to the same CACHE_ID. We can also look at the result cache statistics for a high-level overview of how it is being used, as follows. SQL> SELECT * 2 FROM v$result_cache_statistics; ID NAME VALUE ---------- --------------------------------- --------------- 1 Block Size (Bytes) 1024 2 Block Count Maximum 2048 3 Block Count Current 32 4 Result Size Maximum (Blocks) 102 5 Create Count Success 3 6 Create Count Failure 0 7 Find Count 4 8 Invalidation Count 0 9 Delete Count Invalid 0 10 Delete Count Valid 0 10 rows selected.
Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level. Indexes can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command. CREATE INDEX index_name ON table_name(column_name) INVISIBLE; ALTER INDEX index_name INVISIBLE; ALTER INDEX index_name VISIBLE;The following script creates and populates a table, then creates an invisible index on it. CREATE TABLE ii_tab (id NUMBER); BEGIN FOR i IN 1 .. 10000 LOOP INSERT INTO ii_tab VALUES (i); END LOOP; COMMIT; END;
/ CREATE INDEX ii_tab_id ON ii_tab(id) INVISIBLE; EXEC DBMS_STATS.gather_table_stats(USER, 'ii_tab', cascade=> TRUE);A query using the indexed column in the WHERE clause ignores the index and does a full table scan. SET AUTOTRACE ON SELECT * FROM ii_tab WHERE id = 9999; ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 7 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| II_TAB | 1 | 3 | 7 (0)| 00:00:01 | ----------------------------------------------------------------------------Setting the OPTIMIZER_USE_INVISIBLE_INDEXES parameter makes the index available to the optimizer. ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE; SELECT * FROM ii_tab WHERE id = 9999; ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| II_TAB_ID | 1 | 3 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------Making the index visible means it is still available to the optimizer when the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is reset. ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE; ALTER INDEX ii_tab_id VISIBLE; ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| II_TAB_ID | 1 | 3 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------Invisible indexes can be useful for processes with specific indexing needs, where the presence of the indexes may adversely affect other functional areas. They are also useful for testing the impact of dropping an index. The current visibility status of an index is indicated by the VISIBILITY column of the [DBA|ALL|USER]_INDEXES views. READ ONLY TABLES Different Ways through which we can make a table Read-only prior to 11g Security priviliges (schema + grant SELECT) Triggers Create a check constraint with disable validate Read-only tablespace Create view, and instead-of triggers that do nothing (similar to 2) You can also us e DBMS_RLS package too for making the table read only From oracle 11g you make the table read only by using the below command SQL>ALTER TABLE
DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock. ADDING COLUMN TO A TABLE WITH DEFAULT VALUE: Before Oracle 11g adding new columns with DEFAULT values and NOT NULL constraint required both an exclusive lock on the table and the default value to be stored in all existing records. Now in Oracle 11g the database can optimize the resource usage and storage requirements for this operation, the default value is stored in the data dictionary instead of updating the table column as a result especially for large tables the execution time is reduced and space is saved
create table tst_source nologging as select rownum id, text from dba_source; set timing on ALTER TABLE tst_source ADD (name VARCHAR2(16) DEFAULT 'N' NOT NULL); Elapsed: 00:00:30.43 set timing off exec dbms_stats.gather_table_stats(user, 'tst_source'); select count(*) from tst_source where name is NULL ; COUNT(*) ---------- 0
create table tst_source nologging as select rownum id, text from dba_source; set timing on ALTER TABLE tst_source ADD (name VARCHAR2(16) DEFAULT 'N' NOT NULL); Elapsed: 00:00:00.10 set timing off exec dbms_stats.gather_table_stats(user, 'tst_source'); select count(*) from tst_source where name is NULL ; COUNT(*) ---------- 0
Example : Now lets suppose that we are working in scott schema. First I created the following function: CREATE FUNCTION get_grade(p_sal emp.sal%TYPE) RETURN VARCHAR2 DETERMINISTIC IS l_grade VARCHAR2(6); BEGIN l_grade := CASE WHEN p_sal <= 1000 THEN ‘3′ WHEN p_sal <= 5000 THEN ‘4′ WHEN p_sal <= 6000 THEN ‘5′ WHEN p_sal <= 7000 THEN ‘6′ WHEN p_sal <= 8000 THEN ‘7′ WHEN p_sal <= 9000 THEN ‘8′ WHEN p_sal <= 10000 THEN ‘9′ ELSE ‘10′ END ; RETURN l_grade; END; Then I added a virtual column as follows to emp table: ALTER TABLE emp ADD( sal_grade VARCHAR2(6) GENERATED ALWAYS AS (SUBSTR(get_grade(sal),1,6)) VIRTUAL ); Just in case you are wondering what will be create statement, it will be as follows: CREATE TABLE emp ( empno NUMBER (4) NOT NULL, ename VARCHAR2 (10), job VARCHAR2 (9), mgr NUMBER (4), hiredate DATE, sal NUMBER (7,2), comm NUMBER (7,2), deptno NUMBER (2), sal_grade VARCHAR2(6) GENERATED ALWAYS AS (SUBSTR(get_grade(sal),1,6)) VIRTUAL, CONSTRAINT pk_emp PRIMARY KEY ( empno ) ) ; For example lets go through one situation, we have a sales table with Gross_Amount and Discount_Amount. Now
We can tell our guys to use this formula everywhere. Now if formula changes due to some reason, then we will need to change the formula everywhere. Other way to tackle such situation is to create the separate view (formula embedded in it) and everybody use that view everywhere. So if formula changes then we just need to alter the view. Another way is to add a new column called Gross_Amount and calculate it at trigger level, but this will result in wastage of hard disk space. Now with Oracle 11g, there is a new feature called Virtual Column to handle this situation. With help of this new feature we can just add one virtual column based on the formula in the existing table and if formula changes then just need to alter this column expression or formula. Directory: groups groups -> What is ascaa? groups -> Tiger Leaps Progress Update from Tenafly High School March 28, 2012 English groups -> Personal Data: Name : Prasanna. S educational Qualification groups -> Traffic alert and Collision Avoidance System or tcas groups -> 1980s Type Card Master List from Standard Catalog 1980 Burger King Phillies groups -> Lima Site 85 Personnel List groups -> General knowledge groups -> Kenneth pomeranz the great himalayan watershed groups -> Christina Maria Bergqvist Download 80.78 Kb. Share with your friends:
|