O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Using triggers and virtual columns

In this recipe, we will see how to use virtual columns, a new feature in Oracle Database 11g, to avoid the use of DML triggers, resulting in a performance gain in our applications.

Note

Virtual columns can also be used in referential integrity, tables can be partitioned by them, and statistics can be gathered on them.

How to do it...

The following steps will demonstrate the use of virtual columns:

  1. Connect to the SH schema:
    CONNECT sh@TESTDB/sh
    
  2. Create a table and call it LOANS:
    CREATE TABLE sh.LOANS (
    LOAN_ID INT NOT NULL,
    PAYMENT NUMBER,
    NUMBER_PAYMENTS NUMBER,
    GROSS_CAPITAL NUMBER);
    
  3. Create a trigger on the LOANS table to calculate the GROSS_CAPITAL field, giving the number of payments and the amount of every single ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required