Oracle 11g Virtual Column

Virtual Column is one of the new features introduced in Oracle 11g. Virtual column values are not stored on disk. They are generated at runtime using their associated expression

The syntax for defining a virtual column is:

Column_Name [Datatype] [GENERATED ALWAYS] AS [Expression] [VIRTUAL]

Here, Data type: Column’s Data Type and Expression: SQL Expression

Though Virtual Columns appear to be like normal table columns they are defined by an expression and they derive their value from the result of the expression. The values of these virtual columns are not stored in database; instead they are calculated at run time dynamically.

How to create a virtual column in a table

Example 1:

Create Table Product

(

    Product_Id Number,

    Product_Name Varchar2 (100),

    Value Number,

    Increment Number,

    New_Value Number Generated Always As (Value + Increment) Virtual

);

Example 2:

Create Table Product_Sales

    (
       Sls_Key Number,

       Product_Id Number,

       Sls_Amt Number,

       Sls_Loc_Id Number,

       Sales_Percent

       Generated Always As

       (

         Case

            When Sls_Loc_Id = 1 Then Sls_Amt

            When Sls_Loc_Id =2 Then Sls_Amt * .01

            End

      ) Virtual

   );

Adding the virtual column using the ALTER command:

Alter Table Product Add (New_Value Generated Always As (Value + Increment));

You can also create indexes on the virtual columns:

­Create Index Index_New_Value On Product (New_Value);

Some important Points on Virtual Columns:

• All columns in the virtual column, should belong to the same table on which we are creating the virtual column
• We cannot do any update or DML's on Virtual column.
• Virtual column expression can’t reference any other virtual column.
• Indexes and constraints can be created on virtual columns; also they can be used as partition key
• Virtual columns reduce the use of views and derived columns.

virtual columns and storage

To correctly demonstrate that a virtual column uses no storage would require techniques such as block dumping, which is beyond the scope of this article. We can, however, show that adding more virtual columns to an existing table doesn't require any additional space. In the following example, we will create two tables of 10,000 records each and compare their relative sizes (one table will have several virtual columns). We will begin by creating the tables as follows.

CREATE TABLE NORML_COL_TBL

 (
 c1 VARCHAR2(4000),

 c2 VARCHAR2(4000),

 c3 VARCHAR2(4000)

);

 

CREATE TABLE succeeded.

CREATE TABLE VITTUAL_COL_TBL

 (
 c1 VARCHAR2(4000),

 c2 VARCHAR2(4000),

 c3 VARCHAR2(4000),

 c4 VARCHAR2(4000) GENERATED ALWAYS AS (UPPER(c1)) VIRTUAL,

 c5 VARCHAR2(4000) GENERATED ALWAYS AS (UPPER(c2)) VIRTUAL,

 c6 VARCHAR2(4000) GENERATED ALWAYS AS (UPPER(c3)) VIRTUAL

 );

 

 CREATE TABLE succeeded.

 

We will now load each table with 10,000 identical rows.

INSERT INTO NORML_COL_TBL

SELECT RPAD('x',4000),

        RPAD('x',4000),

        RPAD('x',4000)

FROM   dual

CONNECT BY ROWNUM <= 10000;

 10000 rows inserted

INSERT INTO VITTUAL_COL_TBL(c1,c2,c3)

SELECT RPAD('x',4000),

        RPAD('x',4000),

        RPAD('x',4000)

FROM   dual

CONNECT BY ROWNUM <= 10000;

 10000 rows inserted

Finally, we can compare the sizes of the two tables, as follows.

SELECT segment_name,

  bytes,

    blocks,

     extents

FROM   user_segments

WHERE  segment_name LIKE '%_COL_TBL%';

 

SEGMENT_NAME                                                                      BYTES                  BLOCKS                 EXTENTS               

--------------------------------------------------------------------------------- ---------------------- ---------------------- ----------------------

VITTUAL_COL_TBL                                                                 251658240            30720                 10                   

NORML_COL_TBL                                                                  251658240            30720                 10

As expected, the tables' physical storage requirements (excepting the dictionary metadata) are identical.

Assume I have a composite index on a table. While searching the data, will it use statistics of only first column or statistics of all the columns?

Lets understand this by an example(by the way I love to give such examples).

You have a travel book and it has description of all the beautiful places in the world. You do not want to read the entire book but straight away want to check out 2-3 destinations to make up your mind. Lets say you are looking for all the places to visit in the UK. So you will open the book and straightaway go to the country UK in the index and will quickly jump to that page. Right??

But then you have other criteria as well. You only want to visit the places in London, the zone should be between 1-3. 

Because you do not have any index on City i.e. London and zone i.e. between 1-3.You will reach the first page from where the details about UK starts(Index seek), but after that you will browse all the pages in that range(bookmark lookup). Painful isn't it! Imagine if in the same index you also have a sub index for cities and then within cities another sub index on zones. 

In a database when you create an index on 3 columns viz. Country, City and zone a collective statistics on the index will be created. In that you will see density of all the combinations i.e. (a)Country (b)Country and City (c) country, city and zone. But the histogram will be on the leftmost column i.e. Country because that is the first page in the book that you need to reach to.

That is the reason wise men say "Make sure that your left most column is as unique as possible"

How to load image file in Oracle database?

Photographs and pictures and Oracle BLOB data are easy to add to a Oracle table. Uses PL/SQL and the DBMS_LOB package and the BFILE datatype to transfer external LOB files into the database internal LOB structures.

Once the BFILE locators are set in the BFILE table we can use the DBMS_LOB package to read the external LOB (BFILE) into an internal LOB (BLOB, CLOB or NCLOB).

Below are the steps:

1. create a directory where you will find the image files:

CREATE OR REPLACE DIRECTORY ORCL_IMG_FILE as '/orcl/data/img_file';

GRANT READ, WRITE ON DIRECTORY test_dir TO abc;

If  it is windows the select your windows directory.

2.Table which hold the image

CREATE TABLE EXTRACT_FILE_IMG_TBL

(

  FILE_NAME       VARCHAR2(100 BYTE),

  FILE_SIZE       VARCHAR2(100 BYTE),

  EXTRACT_FILE    BLOB,

  LOAD_DATE_TIME  DATE

);

3.Processing the image file to the table,below is the procedure:

CREATE OR REPLACE PROCEDURE PR_IMAGE_UPLOAD(pfname VARCHAR2)--pfname is the image file name which we want to load into db table.

AS

  upload_file_name    VARCHAR2(100);

  src_file            BFILE;

  dst_file            BLOB;

  lgh_file                                     INTEGER;

  pdname              VARCHAR2(100);

  v_file_size_chk     BOOLEAN       :=TRUE;

  g_sqlcode           VARCHAR2(50)  := NULL;

  g_sqlerrm           VARCHAR2(250) := NULL;

  g_customized_msg    VARCHAR2(500) := NULL;

  v_proc_start_time   DATE;

  v_proc_end_time     DATE;

  v_elapsed_time      NUMBER;

  v_lineno                              NUMBER :=0;

BEGIN

v_lineno :=1;

v_elapsed_time := 0;

v_proc_start_time := SYSDATE + (1 / 86380);

  BEGIN

  v_lineno :=2;

    pdname   :='ORCL_IMG_FILE';--My directory where through source system images will store

    src_file := bfilename(pdname, pfname);

                lgh_file := NVL(dbms_lob.getlength(src_file),0);

-- +-----------------------------------------------------------------------------------------------------------+

 -- | ASSIGN AN EMPTY VALUE (WITH 0 LENGTH) TO A BLOB COLUMN. ALTHOUGH THE LENGTH IS 0, THE VALUE IS NOT NULL   |

-- +-----------------------------------------------------------------------------------------------------------+

  v_lineno :=3;

     INSERT

      /* + APPEND */

       INTO EXTRACT_FILE_IMG_TBL

      (

        FILE_NAME,

        FILE_SIZE,

        EXTRACT_FILE,

        load_date_time

      )

      VALUES

      (

        pfname  ,

        lgh_file,

        EMPTY_BLOB(),

        sysdate

      )

    RETURNING EXTRACT_FILE

       INTO dst_file;

    COMMIT;

               

    v_lineno :=4;

               

    /*lock record till update */

     SELECT EXTRACT_FILE

       INTO dst_file

       FROM EXTRACT_FILE_IMG_TBL

      WHERE FILE_NAME = pfname and file_size=lgh_file FOR UPDATE;

                 

    /* open the file */

    dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

               

    /* determine the length of file */

    /* Read the file and intiate dst_file varible with input file */

    dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

                   

    v_lineno :=5;

               

                /* update the blob field */

                UPDATE EXTRACT_FILE_IMG_TBL

                SET EXTRACT_FILE  = dst_file

                WHERE FILE_NAME = pfname

                and FILE_SIZE=lgh_file;

                COMMIT;

               

    /* close file */

    dbms_lob.fileclose(src_file);

    COMMIT;

v_proc_end_time := SYSDATE + (1 / 86380);

v_elapsed_time := v_proc_end_time-v_proc_start_time;

 dbms_output.put_line('Total Elapsed_time for image processing into database'||' = '||v_elapsed_time); 

   

  EXCEPTION

  WHEN OTHERS THEN

    g_customized_msg := 'Error while inserting data into extract_file_tprr_tbl';

    g_sqlcode        := SQLCODE;

    g_sqlerrm        := sqlerrm;

--   

                DELETE FROM EXTRACT_FILE_IMG_TBL WHERE FILE_NAME = pfname and FILE_SIZE=lgh_file;

                COMMIT;

    RAISE_APPLICATION_ERROR

    (

      -20420, 'ERROR WHILE EXECUTING PR_IMAGE_UPLOAD'||' line no: '|| v_lineno||' '|| g_sqlcode||' '|| g_sqlerrm

    )

    ;

  END;

END PR_IMAGE_UPLOAD;

How to move table,indexes,lobs from one tablespace to another?

Moving the user table to another tablespace

 BEGIN
  FOR i IN (
    SELECT * FROM ALL_tables where owner = :owner
      and (tablespace_name is null or tablespace_name != :tbs)
      and temporary != 'Y'
      and partitioned != 'YES'
    ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE '  || i.table_name || ' MOVE TABLESPACE ' || :tbs;
  END LOOP;
END;
BEGIN
  FOR i IN (
    SELECT * FROM ALL_tab_partitions
    WHERE table_owner = :owner and tablespace_name != :tbs
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE '
      || i.table_name || ' MOVE PARTITION '
      || i.partition_name ||' TABLESPACE '|| :tbs;
  END LOOP;
END;
BEGIN
  FOR i IN (
    SELECT * FROM ALL_tables where owner = :owner and partitioned = 'YES'
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE '
      || i.table_name || ' MODIFY DEFAULT ATTRIBUTES TABLESPACE ' || :tbs;
  END LOOP;
END;
BEGIN
  FOR i IN (
    SELECT * FROM ALL_Lobs WHERE owner = :owner
      and (tablespace_name is null or tablespace_name != :tbs)
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE '  || i.table_name || ' MOVE TABLESPACE ' || :tbs;
  END LOOP;
END;

 Moving indexes to another user tablespace:

 BEGIN
  FOR i IN (
    SELECT * FROM ALL_indexes
    where owner = :owner
      and tablespace_name != :index_tbs
      and index_type != 'IOT - TOP'
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX '  || i.index_name || ' REBUILD TABLESPACE ' || :index_tbs;
  END LOOP;
END;
BEGIN
  FOR i IN (
    SELECT * FROM ALL_indexes
    where owner = :owner
      and partitioned = 'YES'
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX '  || i.index_name
      || ' MODIFY DEFAULT ATTRIBUTES TABLESPACE ' || :index_tbs;
  END LOOP;
END;
BEGIN
  FOR i IN (
    SELECT * FROM ALL_ind_partitions
    where index_owner = :owner
      and tablespace_name != :index_tbs
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX ' 
      || i.index_name || '  REBUILD PARTITION '
      || i.partition_name || ' TABLESPACE ' || :index_tbs;
  END LOOP;
END;
 
Moving Lob segment:

 BEGIN
  FOR i IN (
    select * from user_lobs where (tablespace_name is null or tablespace_name != :tbs)
    ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE '  || i.table_name || ' MOVE TABLESPACE ' || :tbs
      || ' LOB (' || i.column_name || ')'
      || ' STORE AS ' || i.segment_name || ' (TABLESPACE ' ||  :tbs ||' )';
  END LOOP;

Case Statement and Decode Function

Case Statements:
---------------------
-->CASE statement has the functionality of an IF-THEN-ELSE statement. Starting in Oracle 9i, you can use the CASE statement within a SQL statement or Pl/sql as well.
syntax:
CASE [ expression ]
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n
   ELSE result
END
Expression is optional :- It is the value that you are comparing to the list of conditions.
Condition_1 to
Condition_n :- Must all be the same datatype. Conditions are evaluated in the order listed.
        Once a condition is found to be true, the CASE statement will return the result and not evaluate the conditions any further.
Result_1 to result_n :- must all be the same datatype. This is the value returned once a condition is found to be true.
-->If no condition is found to be true, then the CASE statement will return the value in the ELSE clause.
-->If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.
-->You can have up to 255 comparisons in a CASE statement. Each WHEN ... THEN clause is considered 2 comparisons.
You could use the CASE statement in a SQL statement as follows: (includes the expression clause)
SELECT table_name,
CASE owner
  WHEN 'SYS' THEN 'The owner is SYS'
  WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
  ELSE 'The owner is another value'
END
FROM all_tables;
Or you could write the SQL statement using the CASE statement like this: (omits the expression clause)
SELECT table_name,
CASE
  WHEN owner='SYS' THEN 'The owner is SYS'
  WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
  ELSE 'The owner is another value'
END
FROM all_tables;
The above two CASE statements are equivalent to the following IF-THEN-ELSE statement:
IF owner = 'SYS' THEN
   result := 'The owner is SYS';
ELSIF owner = 'SYSTEM' THEN
   result := 'The owner is SYSTEM'';
ELSE
   result := 'The owner is another value';
END IF;
-->Multiple expression in case using and operator:
SELECT supplier_id,
CASE
  WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
  WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
FROM suppliers;
So if supplier_name field is IBM and the supplier_type field is Hardware, then the CASE statement will return North office.
If the supplier_name field is IBM and the supplier_type is Software, the CASE statement will return South office.

Decode Function:
------------------------
-->DECODE function has the functionality of an IF-THEN-ELSE statement.
--> maximum number of components that you can have in a DECODE function is 255. This includes the expression, search, and result arguments.
Syntax:DECODE( expression , search , result [, search , result]... [, default] )
Parameters or Arguments
expression :- is the value to compare.
search :-  It is the value that is compared against expression.
result :-  It is the value returned, if expression is equal to search.
default :- It is optional. If no matches are found, the DECODE function will return default.
    If default is omitted, then the DECODE function will return null (if no matches are found).
SELECT supplier_name,
DECODE(supplier_id, 10000, 'IBM',
                    10001, 'Microsoft',
                    10002, 'Hewlett Packard',
                    'Gateway') result
FROM suppliers;
The above DECODE statement is equivalent to the following IF-THEN-ELSE statement:
 IF supplier_id = 10000 THEN
   result := 'IBM';
ELSIF supplier_id = 10001 THEN
   result := 'Microsoft';
ELSIF supplier_id = 10002 THEN
   result := 'Hewlett Packard';
ELSE
   result := 'Gateway';
END IF;