Re: Indexing an SQL view

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Rick

Can't be done. Mostly because a view is merely a container for an SQL 
SELECT statement - no data. The idea with SQL is to create a view for the 
data you want, and an index that matches that data, just in case the 
optimizer can use it.

Since you also cannot index on expressions (substr, e.g., comes to mind), I 
suggest creating a true logical file with the separate components as 
substrings and key on those calculated fields. Then it should have a chance 
at running faster.

An appropriate combination of native file types and SQL can be truly powerful.

Good luck, and let us know.
Vern

At 11:24 AM 4/19/2004 -0500, you wrote:
> content-class: urn:content-classes:message
> Content-Type: multipart/related; type="multipart/alternative";
>         boundary="----_=_NextPart_001_01C4262A.D894525C"
>
> Is there a way to index an SQL view?  It appears from the documentation 
> that it can't be done.  Searching Google didn't produce a way to do this 
> either.  I'm hoping someone on the list will know how to do it, if it can 
> indeed be done.
>
> As background, I have created a view which takes a ten byte alpha field 
> and breaks it up into its components (Last name 6-bytes, first initial 
> 1-byte, middle initial 1-byte, and tie breaker number 2-bytes) so they can 
> be joined to another file with these stored as separate values.  The goal 
> is to create a select statement that will return the name and address for 
> a customer.  I have a working statement but the run time is not quick 
> enough.  I have tried indexing the name and address files (both binary and 
> EVI) but it isn't getting me where I want to be.
>
> Here is the statement in case it would help.  The account number is only 
> in file CUXRF1V1.  The name and address are then located in CUMAS00 and 
> CUMAS01 using the fields described above.
>
> select cuna1, cuna2, cuna3, cuna4, cuna5, cuna6, cuzip, cuzip2
> from cuxrf1v1 join cumas00 a on cuxlast = a.culast
> and cuxfrst = a.cufrst and cuxmidl = a.cumidl and cuxtie = a.cutie
> left outer join cumas01 b on cuxlast = b.culast
> and cuxfrst = b.cufrst and cuxmidl = b.cumidl and cuxtie = b.cutie
> where cux1ac = xxxxxxxxx
>
>
> Rick Chevalier