Re: Indexes on Views

Another option to look into is the advanced indexing technology of OMNIDEX from DISC (Dynamic Information Systems Corporation). (Attention: Promotional information follows. Please disregard if not interested in another solution.)

OMNIDEX uses specialized Multidimensional Keyword Indexes to deliver instant full text searches and unlimited multidimensional analysis by any number of criteria or columns. OMNIDEX layers on top of your existing database TABLES OR VIEWS, and it supports numerous databases and document files, including Oracle, SQL Server, Informix, Sybase, flat files, HTML and Word documents.

Designed for performance, OMNIDEX is ideal for fast ad-hoc querying, and is case-insensitive. It works well for both high and low cardinality data, ranging from Names to Status flags,and is very efficient in terms of build time and disk space.

How much OMNIDEX can help depends on your needs and environment. If you would be interested in a free performance analysis or more information, please contact me.

Cheryl Grandy
DISC
cgrandy_at_disc.com
303 444-4000
www.disc.com/home

OMNIDEX - for the fastest applications ever!

In article <u3dobjx2m.fsf_at_fmr.com>,
  steve.tolkin_at_fmr.com wrote:
> 1. As others have said, a query on a view can often benefit from using
> indexes defined on its underlying physical base tables.
> This assumes the optimizer/runtime is good enough to find/use the
> appropriate index(es), which often is not the case.
> 2. A materialized view is a physically stored table that corresponds
> to (has the same content as) a select query. These too can
> generally have indexes defined that will be used (hopefully).
> 3. My main point is that indexes on ordinary views are
> perfectly well defined, useful, and have been implemented in
> database systems supporting the relational model.
> (It is a pity that they are not supported by the SQL standard.
> This is just another case where SQL does not cleanly separate the
> logical relational model from its physical implementation.)
>
> This technique (indexing a view) is useful for almost any
> computed column.
> One specific example would be to support case insensitive
> searching, e.g. of names. The view could have a column
> e.g. upper(original_last_name) as last_name,
> with an index defined on it. Then a typical query
> would be: select original_last_name from people where
> last_name like 'SMITH%'
> The point of using an index on the view column is to
> save space; this avoids storing the uppercase version of the name
> column both in the table (where it is not needed) and in the index.
>
> Hopefully helpfully yours,
> Steve
> --
> Steven Tolkin steve.tolkin_at_fmr.com 617-563-0516
> Fidelity Investments 82 Devonshire St. R24D Boston MA 02109
> There is nothing so practical as a good theory. Comments are by me,
> not Fidelity Investments, its subsidiaries or affiliates.
>

--
Cheryl Grandy
DISC
Get OMNIDEX for the fastest
applications ever


Sent via Deja.com http://www.deja.com/
Before you buy.