Re: Oracle 9i features?

Serge Rielau 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;
> >>>

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

AFAICT, there are 2 places that a computed field could be used - in the projection (select list) and in the selection (where clause). Would not Oracle's Function Based Indexes (FBI) handle the 'selection' requirement and simple expressions or views handle the 'projection' requirement? (Should be close - other than it isn't stored in the dictionary against the base table - which is probably against the relational rules anyway, isn't it?)

<ASIDE>
I've always had a problem with the traditional definition of 'base table'. IMO, when we do a 'create table' we create a storage definition and define an intrinsic view mapped directly to that storage definition. Using this argument, it's all views anyways, so creating another view on top should be acceptable. </ASIDE>

Just my $0.02 (Cdn)
/Hans
forbrich at telusplanet dot net Received on Sat Feb 07 2004 - 21:49:21 CST