Re: Indexing View Columns?

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Thu, 10 Mar 2011 06:18:34 -0800 (PST)
Message-ID: <6fcfd72c-32e5-4f2a-a837-ead395a02434_at_j9g2000prj.googlegroups.com>

On Mar 10, 8:30 am, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> On Thu, 10 Mar 2011 04:50:11 -0800, NewUser2008 wrote:
> > Is there any way to go around this issue? Like indexing the col2 or
> > giving
> > it a hint on which UNION line to use?
>
> Indexing column in a view? Sure, as soon as it is possible to assign
> rowid to the rows of a view. Not a problem, as long as the view is
> materialized.
> I would strongly advise to consider alternative design options.
>
> --http://mgogala.byethost5.com

NewUser2k8, as Mladen said you can only build indexes on materialized views, which are really tables, that get updated at periodic times based on the material view definition.

What is the purpose of this view? If assigning a specific value to column2 based on the value of another column in the row then could you use a CASE statement instead?

  1 select fld2,

  2         case when fld2 = 4 then 'SALES'
  3              when fld2 = 5 then 'RETAIL PARTS'
  4              when fld2 = 6 then 'FIELD TECH'
  5              when fld2 = 7 then 'WEB SALE'
  6         end as "SALES CREDIT"

  7* from marktest
SQL> /       FLD2 SALES CREDIT
---------- ------------

         4 SALES
         5 RETAIL PARTS
         6 FIELD TECH
         7 WEB SALE

HTH -- Mark D Powell -- Received on Thu Mar 10 2011 - 08:18:34 CST