Result cache



Download 80.78 Kb.
Date28.05.2018
Size80.78 Kb.
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, isdefault


2 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.

caching results manually

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)

71 rows processed

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 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

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.

INVISIBLE AND VISIBLE INDEXES

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 READ ONLY;

Once the table is made read only you cannot perform dml operations(like INSERT,UPDATE,DELETE), DDL(ALTER) .But the ddl commands like drop and truncate are possible.

SQL> desc user_tables

SQL>select table_name,read_only from user_tables;

Read_only column shows 2 values yes if it is set read only if not it shows the value as no.

SQL>ALTER TABLE

READ WRITE;

This statement makes sure that you perform any operation on the table.



DDL WAIT OPTION:

DDL commands require exclusive locks on internal structures. If these locks are not available the commands return with an "ORA-00054: resource busy" error message. This can be especially frustrating when trying to modify objects that are accessed frequently. To get round this Oracle 11g includes the DDL_LOCK_TIMEOUT parameter, which can be set at instance or session level using the ALTER SYSTEM and ALTER SESSION commands respectively.

The DDL_LOCK_TIMEOUT parameter indicates the number of seconds a DDL command should wait for the locks to become available before throwing the resource busy error message. The default value is zero. To see it in action, create a new table and insert a row, but don't commit the insert.

CREATE TABLE lock_tab (id NUMBER);


INSERT INTO lock_tab VALUES (1);

Leave this session alone and in a new session, set the DDL_LOCK_TIMEOUT at session level to a non-zero value and attempt to add a column to the table.

ALTER SESSION SET ddl_lock_timeout=30;
ALTER TABLE lock_tab ADD (description VARCHAR2(50));

The session will wait for 30 seconds before failing.

ALTER TABLE lock_tab ADD (

*

ERROR at line 1:



ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

If we repeat the ALTER TABLE command and commit the insert in the first session within 30 seconds, the ALTER TABLE will return a successful message.

ALTER TABLE lock_tab ADD (description VARCHAR2(50));
Table altered.

DDL LOCK_TIMEOUT



Property

Description

Parameter type

Integer

Default value

0

Modifiable

ALTER SESSION

Range of values

0 to 1,000,000 (in seconds)

Basic

No

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

release 1002000300 -
drop table tst_source purge ;

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
release 1101000600 –
drop table tst_source purge ;

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

VIRTUAL COLUMNS:

In Oracle 11g, a table can contain a virtual column which derives the values by evaluating a user-specified expressions.The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions.

A virtual column’s does not consume any space on disk as its value is calculated only when it is queried based on its column expression.

It can be defined at the time of table creation or modification.

We can index virtual columns, collect statistics on them, and create integrity constraints. Thus, they can be treated much as normal columns.

Off course, we cannot explicitly write to a virtual column.

As Oracle Documents rightly says:
“This makes application development easier and less error-prone, as well as enhances query optimization by providing additional statistics to the optimizer for these virtual columns”

Syntax is as follows:

CREATE STATMENT:

CREATE TABLE table_name (
column_name data_type
…..
column_name data_type GENERATED ALWAYS AS (column_expression) VIRTUAL — For Virtual Column
) ;

ALTER STATMENT:

ALTER TABLE table_name action (columns datatype GENERATED ALWAYS AS (column_expression) VIRTUAL);

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
Net_Amount := Gross_Amount - Discount Amount;

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:




The database is protected by copyright ©ininet.org 2020
send message

    Main page