Can a view be indexed?

[EzLogic]

I have a DBC that contains 3 tables that are normalized.

maintable (IID, and fk_to the rest of the tables)
Table2 (contains IID)
Table3 (contains iid)
Table4 (contains IID)
Table5 (contains IID)
Table1 has forein keys to table2, table3 (bunch of them)
example, in maintable
fk_table1, fk_table2,fk_table3,fk_table4,fk_table5

and

I need to create a view so it will make it easier for the user to search on a grid form.

I usualy display a grid and text boxes with interactive search which displays all the records and uses indexes.





Ali Koumaiha
Wireless Toyz
Farmington Hills, Michigan

 

[Mike Lewis]

Ali,

The indexes need to be on the underlying tables. If you have indexes on your ID and FK fields, the view will be optimised. You don't need to specifically create an index on the view itself.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Nifrabar]

Ali,
In addition to Mikes valuable answers:
The question in the name of this thread is : "Can a view be indexed".
The answer is yes, same as normal tables can be indexed.
-Bart

 

[Olaf Doschke]

in addition to both right answers:

a) indexes defined on the table will optimise the query, but you don't inherit them, so if you want to use an index on the view to sort it's display, you can't make use of the indexes defined on the table level.

b) There is no way to define an index in the view designer, but yes, you can index the view.

beacuse

c) as long as the view is not buffered or table buffered, you can use the INDEX command on a view alias.

So additional to USE someview or to adding a view to a data environment, you'll need some INDEX ON commands to create tags on the fly after retrieving the view data. But there are no permanent view indexes/tags, as there is no permanent data in a view, a view is mainly it's sql query.

Bye, Olaf.

 

[Mike Lewis]

. But there are no permanent view indexes/tags, as there is no permanent data in a view, a view is mainly it's sql query.

Yes, that's right. And that in turn means you have to build the index again each time you open the view, which takes time. So you could end up with the index making the system slower rather than faster.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

Yes, that's true, indexing a view takes some time. It depends on the case if that helps, if the amount of data is small it shouldn't matter much anyway, if the amount of data is large you would need to experiment if indexing is faster then requerying the view with a dfferent order, but it should pay off, if you reorder many times for example. And requerying in a different order is not evan that simple, as that needs a parameteization of the order by clause.

Bye, Olaf.