VIEW

Former Member — Fri, 27 Jul 2007 09:08:51 GMT

Hi,

While getting data from DB tables we will make use of indexes. If the same to be retrieved from a view...what is the retrieval mechanism?

Assume View v1 has fields f1,f2,f3,f4 and while making query on V1 will it improve performance...if we provide conditions in Where clause as the same sequence which matches with View fields sequence.

Can any body clariry?

PRa

Re: VIEW

Former Member — Fri, 27 Jul 2007 09:10:49 GMT

Hi Praneet,

yes it will be more faster. it will definately improve. you can directly place your select stmt on this.

reward if help ful

regards

Azad.

Re: VIEW

Former Member — Fri, 27 Jul 2007 09:10:54 GMT

Retrieving data through views

When you retrieve data through a view, Adaptive Server checks to make sure that all the database objects referenced anywhere in the statement exist and that they are valid in the context of the statement. If the checks are successful, Adaptive Server combines the statement with the stored definition of the view and translates it into a query on the view's underlying tables. This process is called view resolution.

Consider the following view definition statement and a query against it:

create view hiprice

as select *

from titles

where price > $15

and advance > $5000

select title, type

from hiprice

where type = "popular_comp"

Internally, Adaptive Server combines the query of hiprice with its definition, converting the query to:

select title, type

from titles

where price > $15

and advance > $5000

and type = "popular_comp"

In general, you can query any view in any way just as if it were a real table. You can use joins, group by clauses, subqueries, and other query techniques on views, in any combination. Note, however, that if the view is defined with an outer join or aggregate function, you may get unexpected results when you query the view. See "Views derived from other views".

You can use select on text and image columns in views. However, you cannot use readtext and writetext in views.

View resolution

When you define a view, Adaptive Server verifies that all the tables or views listed in the from clause exist. Similar checks are performed when you query through the view.

Between the time a view is defined and the time it is used in a statement, things can change. For example, one or more of the tables or views listed in the from clause of the view definition may have been dropped. Or one or more of the columns listed in the select clause of the view definition may have been renamed.

To fully resolve a view, Adaptive Server verifies that:

All the tables, views, and columns from which the view was derived still exist.

The datatype of each column on which a view column depends has not been changed to an incompatible type.

If the statement is an update, insert, or delete, it does not violate the restrictions on modifying views. These are discussed under "Modifying data through views".

If any of these checks fails, Adaptive Server issues an error message.

Redefining views

Adaptive Server allows you to redefine a view without forcing you to redefine other views that depend on it, unless the redefinition makes it impossible for Adaptive Server to translate the dependent view.

For example, the authors table and three possible views are shown below. Each succeeding view is defined using the view that preceded it: view2 is created from view1, and view3 is created from view2. In this way, view2 depends on view1 and view3 depends on both the preceding views.

Each view name is followed by the select statement used to create it.

view1:

create view view1

as select au_lname, phone

from authors

where postalcode like "94%"

view2:

create view view2

as select au_lname, phone

from view1

where au_lname like "[M-Z]%"

view3:

create view view3

as select au_lname, phone

from view2

where au_lname = "MacFeather"

The authors table on which these views are based consists of these columns: au_id, au_lname, au_fname, phone, address, city, state, and postalcode.

You can drop view2 and replace it with another view, also named view2, that contains slightly different selection criteria, such as:

create view view2

as select au_lname, phone

from view3

where au_lname like "[M-P]"

view3, which depends on view2, is still valid and does not need to be redefined. When you use a query that references either view2 or view3, view resolution takes place as usual.

If you redefine view2 so that view3 cannot be derived from it, view3 becomes invalid. For example, if another new version of view2 contains a single column, au_lname, rather than the two columns that view3 expects, view3 can no longer be used because it cannot derive the phone column from the object on which it depends.

However, view3 still exists and you can use it again by dropping view2 and re-creating view2 with both the au_lname and the phone columns.

In short, you can change the definition of an intermediate view without affecting dependent views as long as the select list of the dependent views remains valid. If this rule is violated, a query that references the invalid view produces an error message.

Renaming views

You can rename a view using sp_rename:

sp_rename objname , newname

For example, to rename titleview to bookview, enter:

sp_rename titleview, bookview

Follow these conventions when renaming views:

Make sure the new name follows the rules used for identifiers discussed under "Identifiers".

You can change the name only of views that you own. The Database Owner can change the name of any user's view.

Make sure the view is in the current database.

Altering or dropping underlying objects

You can change the name of a view's underlying objects. For example, if a view references a table entitled new_sales, and you rename that table to old_sales, the view will work on the renamed table.

However, if a table referenced by a view has been dropped, and someone tries to use the view, Adaptive Server produces an error message. If a new table or view is created to replace the one that was dropped, the view again becomes usable.

If you define a view with a select * clause, and then alter the structure of its underlying tables by adding columns, the new columns do not appear. This is because the asterisk shorthand is interpreted and expanded when the view is first created. To see the new columns, drop the view and re-create it.

Re: VIEW

Former Member — Fri, 27 Jul 2007 09:11:46 GMT

Hi Praneet,

You can do the samething with view like normal

but performance wil be bad when compare to the normal tables

<b>Regards,

Azhar</b>

Re: VIEW

Former Member — Fri, 27 Jul 2007 09:14:45 GMT

as far as select there will be no difference in writing it, only thing there will be some performance issue but if it is requried they we can always take the performence into consideration

Re: VIEW

Former Member — Fri, 27 Jul 2007 09:14:53 GMT

a view does'nt contain key but u should consider keys of included tables in the view also same for tables indexes.

u'r performance will definitely improve.

don't forget to reward.

Sameer

Re: VIEW

seshatalpasai_madala — Fri, 27 Jul 2007 09:16:39 GMT

Hi,

Since there is nothing Physical for a view, I think when we fetch from the View database optomizer can use the INDICES of the individual tables.

And it will clearly help if we have the fields in the same sequence as we have for the Tables involved.

A query on a view will just be again resolved on to a JOIN query so INDICES can play some role there.

But still good one to think about

Regards,

Sesh

topic Re: VIEW in Application Development and Automation Discussions

VIEW

Re: VIEW

Re: VIEW

Re: VIEW

Re: VIEW

Re: VIEW

Re: VIEW