Re: Oracle 9i features?

From: Serge Rielau <srielau_at_ca.eye-be-em.com>
Date: Sat, 07 Feb 2004 21:28:22 -0500
Message-ID: <c046ss$70l$1@hanover.torolab.ibm.com>

Daniel Morgan wrote:

> Serge Rielau wrote:
>

>> Sybrand Bakker wrote:
>>
>>> On 7 Feb 2004 00:39:22 -0800, pascaldamian_at_icqmail.com (Pascal Damian)
>>> wrote:
>>>
>>>> * computed field/column is not supported, use VIEW for that purpose;
>>>
>>>
>>>
>>>
>>> Has been supported all the time
>>>
>> Sybrand,
>> can you elaborate? When I researched generated columns I did
>> not see that concept in the Oracle docs. Also I don't recall Oracle 
>> standards folks bringing it up when IBM brought the feature into SQL 4.
>>
>> What Pascal seems to be refering to is:
>> SQL Server 2000: CREATE TABLE ... (c1 INT, c2 AS c1 + 5); (a virutal 
>> column)
>> DB2 V7: ...c1 INT, c2 GENERATED ALWAYS AS (c1 + 5)..) (a materialized, 
>> functionally dependend, column)
>>
>> AFAIK you would either use index on expression or a combination of 
>> before trigger and check constraint in Oracle, depending on the 
>> intended use.
>>
>> Cheers
>> Serge

>
>
> CREATE TABLE test (
> somecol NUMBER(5),
> morecol NUMBER(5));
>
> INSERT INTO test VALUES (1, 1);
> COMMIT;
>
> CREATE OR REPLACE VIEW test_view AS
> SELECT somecol, morecol, (somecol + morecol) product_col
> FROM test;
>
> SELECT * FROM test_view;
>
> This ability has been in Oracle for more than a decade.
>

I shouldn'thave given the SQL Server example. Should have known only that part would be addressed, my bad.
I don't think Pascal's question was whether Oracle can create a view. Let's presume a more complex example to illustrate. Be somecol a shape and morecol another shape. now use intersect(comecol, morecol)->shape.
overlap() is an expensive operation. The idea is to keep the computed result persistent for faster access to shift the computation from the frequent select to the rarer update at the cost of storage.

There are four ways of achieving this that I can think of: 1. A materialized view (whith all teh associated cave-ats 2. Index on expression. Works if index access would the optimizers choice in the first place, otherwise it forced the optimizers hand. 3. An extra column in the base table with a before trigger for insert/update to maintain the information and a check constraint to ensure integrity
4. A generated column in the base table which combines the trigger with the check constraint.

It the last point that I believe Pascal is inquiring about. Note that he posted similar questions in other newsgroups. Obviously he is doing a feature/function comparison.
generated columns are in SQL4.

Cheers
Serge

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Received on Sat Feb 07 2004 - 20:28:22 CST