iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Oracle 11g New Features

Online Oracle Training

 

Oracle 11g: New Features - Partitioning on Virtual Columns



I will assume that you know about partitioning in general. If not, you can read the following link: Partitioning

On Oracle 11g, there have been lots of enhancements such as reference partition, interval partitioning, partitioning virtual columns, and extended composite partitioning. 

Partitioning on Virtual columns
This feature allows you to create a column that is not stored in your table. The column will be computed at run time. This column can be partition on Oracle 11g database.

The following is an example of how to create a table partitioning on virtual columns.
CREATE TABLE company-sales
(
   SALES_ID      NUMBER,
   CUST_ID       NUMBER,
   SALES_AMT     NUMBER,
   SALE_CATEGORY VARCHAR2(6)
   GENERATED ALWAYS AS
   (
      CASE
         WHEN SALES_AMT <= 10000
            THEN 'LOW'
         WHEN SALES_AMT > 10000
            AND SALES_AMT <= 100000
            THEN CASE
               WHEN CUST_ID < 101 THEN 'LOW'
               WHEN CUST_ID BETWEEN 101 AND 200 THEN 'MEDIUM'
               ELSE 'MEDIUM'
            END
         WHEN SALES_AMT > 100000
            AND SALES_AMT <= 1000000
            THEN CASE
               WHEN CUST_ID < 101 THEN 'MEDIUM'
               WHEN CUST_ID BETWEEN 101 AND 200 THEN 'HIGH'
               ELSE 'ULTRA'
            END
         ELSE 'ULTRA'
      END
    ) VIRTUAL
)
PARTITION BY LIST (SALE_CATEGORY)
(
   PARTITION P_LOW VALUES ('LOW'),
   PARTITION P_MEDIUM VALUES ('MEDIUM'),
   PARTITION P_HIGH VALUES ('HIGH'),
   PARTITION P_ULTRA VALUES ('ULTRA')
)

 

 

Good Luck!

 

Google
 
Web web site