Can I create an index on a view?

[Shirley]

We are running DB2 on iSeries V5R2.
Using AQUA DATA STUDIO with a connection to our iSeries, I created a
view using SQL and I am trying to create an index on this view using
the code below.

CREATE INDEX reports.Ivendorname ON reports.transbyvendor05
(vendorname)

However I get the following error:

Error: [SQL0156] TRANSBYVENDOR05 in REPORTS not a table.

Is it possible to create an index on a view? If not, what other options
do I have to improve
the performance of my queries on the view I created?
Thanks, Shirley..

[Tim M]

Post by Shirley
We are running DB2 on iSeries V5R2.
Using AQUA DATA STUDIO with a connection to our iSeries, I created a
view using SQL and I am trying to create an index on this view using
the code below.
CREATE INDEX reports.Ivendorname ON reports.transbyvendor05
(vendorname)
Error: [SQL0156] TRANSBYVENDOR05 in REPORTS not a table.
Is it possible to create an index on a view? If not, what other options
do I have to improve
the performance of my queries on the view I created?
Thanks, Shirley..

No. although you can specify a view in the from clause of a select
statment and specify an order by clause.

[wildfish]

maybe u can try the logical file:)
index is only used to phycial file.

[Dr.UgoGagliardelli]

Post by Shirley
We are running DB2 on iSeries V5R2.
Using AQUA DATA STUDIO with a connection to our iSeries, I created a
view using SQL and I am trying to create an index on this view using
the code below.
CREATE INDEX reports.Ivendorname ON reports.transbyvendor05
(vendorname)
Error: [SQL0156] TRANSBYVENDOR05 in REPORTS not a table.
Is it possible to create an index on a view? If not, what other options
do I have to improve
the performance of my queries on the view I created?
Thanks, Shirley..

Create the index on the table, it would have the same effects you
expected creating the index on the view, I guess.
Creating an index on a view make no sense as the view does not contain
data, the table on which the view is based on contains data you want to
index.

[B***@lp-gmbh.com]

Hi,

there is no way to create an index over a view.

Creating a logical file won't help, even though in a logical file keys,
select/omit clauses and joins can be specified.

If you specify a logical file in an select statement, the query
optimizer only takes the field selection, select/omit clauses and join
information and rewrites the select statement using the underlying
physical files (or SQL tables) and the information from the logical
file.
The key information gets not considered.

In an second step ALL access paths (stored in either SQL indexes or
logical files) are evaluated to determine the optimal way to return the
required data. If the access path in the specified logical file gets
used, it's nothing else than by hazard.

If you use a view, the SQL statement gets rewritten by replacing the
view-name through the SQL statement stored in the view.
In the second step again ALL access paths get searched.

In this way it makes no sense to create an index over a view.

In an index only a single file (table) can be specified.
An index can only be created over original fields (columns) in the
physical files (SQL tables), i.e. it's not possible to create an index
by specifying a scalare function.
For example: CREATE INDEX ... ON ... (Year(MyDate), Month(MyDate),
...) is not valid.
This may cause sometimes performance problems, because instead of using
an index a table scan must be performed.

By the way, specifiying a logical file in an SQL statement will cause
that the SQL-Statement gets executed by the old (classic) Query Engine
(CQE) and cannot profite from the enhancements of the new SQL Query
Engine (SQE). The rerouting from SQE to CQE may cost 10-15% of
performance.

Birgitta

[Jonathan Ball]

Post by Shirley
We are running DB2 on iSeries V5R2.
Using AQUA DATA STUDIO with a connection to our iSeries, I created a
view using SQL and I am trying to create an index on this view using
the code below.
CREATE INDEX reports.Ivendorname ON reports.transbyvendor05
(vendorname)
Error: [SQL0156] TRANSBYVENDOR05 in REPORTS not a table.
Is it possible to create an index on a view? If not, what other options
do I have to improve
the performance of my queries on the view I created?
Thanks, Shirley..

Create the index on the table that contains the
vendorname column. When your SQL statements use the
view, the index will be used by the query optimizer.

[Lou]

Create the index over the table, and you will get the performance
improvement you are hoping for.

At the lowest layer, OS/400 maintains tables, views and indices as
separate objects. The way programmers perceive these objects depends on
what approach there are taking. An RPG/DDS programmer thinks in terms
of Physical Files (tables) and Logical Files (views), both of which can
have keys (an index). Neither really do. The index is stored separately
by the OS. SQL programmers think in terms of tables, views and indices.
This is a more correct view in terms of the objects actually created by
the OS; however, they are still separate objects.

The fact that you name the table when you create the index does not
affect how the SQL optimizer considers using the index when you use any
view. All indices are available to the SQL optimizer for any query
against any view of a table and the table itself.