Re: Indexing an SQL view

[rob@xxxxxxxxx]

No, you cannot index a view.  You'll need to create two indexes.  One over 
cumas00 and one over cumas01 that best fit how this statement runs.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





<Rick.Chevalier@xxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
04/19/2004 11:24 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
<midrange-l@xxxxxxxxxxxx>
cc

Subject
Indexing an SQL view






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
 <<ole0.bmp>> 
Enterprise Solutions
Ext. 57178

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.