Views- Advantages and Disadvantages

On 5/9/07, Ashish Karalkar <ashish.karalkar@info-spectrum.com> wrote:

Hello All,

Hi,

Can anybody please point me to Advantages and Disadvantages
of using view

The most obvious advantage (for me, anyway) is to restrict which
columns a user can see in a table.

I'm sure there are others.

With Regards
Ashish...

Cheers,
Andrej

--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

Thanks Andrej
for your replay

I have found out some of them n are as follows, but I want more reasons
for not using views . I only got one

Advantages:

1) Permission to user can be given to access the database only
through view containing specific data the user is authorized to see

2) View convert multiple table queries into single table
queries

3) Views give personalized view

4) Views provide consistent unchanged image even if the underlying
table columns changed.

Disadvantages:

1) Performance : If a view is defined by complex multitable query,then
simple query against that view becomes a coplecated join, and it may take a
long time to complete

----- Original Message -----
From: "Andrej Ricnik-Bay" <andrej.groups@gmail.com>
To: "Ashish Karalkar" <ashish.karalkar@info-spectrum.com>; "Postgres
General" <pgsql-general@postgresql.org>
Sent: Wednesday, May 09, 2007 12:08 PM
Subject: Re: [GENERAL] Views- Advantages and Disadvantages

On 5/9/07, Ashish Karalkar <ashish.karalkar@info-spectrum.com> wrote:

Hello All,
Can anybody please point me to Advantages and Disadvantages
of using view

Hi Ashish,

There are several, but they are generally about implementing a more user
friendly database from a well normalised structure.

You can use views to do any or all of:

act as a filter on fields or records which can be accessed by given users,

act as a stored query to avoid writing a commonly used SQL,

aggregate normalised tables into a single virtual table, so that access
does not require as complex a join, thus it can hide the complexity of the
database structure from applications or users.

An example use is to have a view which provides only the current version
of data from a database which stores multiple versions of the data.

Cheers,

Brent Wood

Thanks Brent for your replay,

What about the Disadvantages, Performance issues?

With Regards
Ashish...

----- Original Message -----
From: <woodb@niwa.co.nz>
To: "Postgres General" <pgsql-general@postgresql.org>
Cc: "Ashish Karalkar" <ashish.karalkar@info-spectrum.com>
Sent: Wednesday, May 09, 2007 1:24 PM
Subject: Re: [GENERAL] Views- Advantages and Disadvantages

On 5/9/07, Ashish Karalkar <ashish.karalkar@info-spectrum.com> wrote:

Hello All,
Can anybody please point me to Advantages and Disadvantages
of using view

Hi Ashish,

There are several, but they are generally about implementing a more user
friendly database from a well normalised structure.

You can use views to do any or all of:

act as a filter on fields or records which can be accessed by given users,

act as a stored query to avoid writing a commonly used SQL,

aggregate normalised tables into a single virtual table, so that access
does not require as complex a join, thus it can hide the complexity of the
database structure from applications or users.

An example use is to have a view which provides only the current version
of data from a database which stores multiple versions of the data.

Cheers,

Brent Wood

----- Original Message -----
From: "Ashish Karalkar" <ashish.karalkar@info-spectrum.com>
To: "Andrej Ricnik-Bay" <andrej.groups@gmail.com>
Cc: "Postgres General" <pgsql-general@postgresql.org>
Sent: Wednesday, May 09, 2007 12:46 PM
Subject: Re: [GENERAL] Views- Advantages and Disadvantages

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/09/07 02:16, Ashish Karalkar wrote:
[snip]

Disadvantages:

1) Performance : If a view is defined by complex multitable
query,then simple query against that view becomes a coplecated
join, and it may take a long time to complete

I don't see that as relevant, since we know which objects are tables
and which are views. But maybe that's just our site.

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGQYL9S9HxQb37XmcRAqhIAKDTiOQpvifDk5nVU87MvGOjLqdV8wCffjf5
3lzftdxJzUDVhNaD9GME3VQ=
=51Vx
-----END PGP SIGNATURE-----

On 5/9/07, Ron Johnson <ron.l.johnson@cox.net> wrote:

1) Performance : If a view is defined by complex multitable
query,then simple query against that view becomes a coplecated
join, and it may take a long time to complete

I don't see that as relevant, since we know which objects are tables
and which are views. But maybe that's just our site.

Plus, to retrieve the same information w/o the view he'd
still have to have the same multi-table query which would
a) take as long to run and b) take much longer to type :)

Cheers,
Andrej

Thanks All for your replies,

But then dont we have any disadvantage of using View???

With Reagrds
Ashish...

----- Original Message -----
From: "Andrej Ricnik-Bay" <andrej.groups@gmail.com>
To: "Ron Johnson" <ron.l.johnson@cox.net>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, May 09, 2007 2:03 PM
Subject: Re: [GENERAL] Views- Advantages and Disadvantages

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/09/07 03:36, Ashish Karalkar wrote:

Thanks All for your replies,
But then dont we have any disadvantage of using View???

You can't insert into multi-table views.

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGQYrNS9HxQb37XmcRAs+uAJ9cVMufkVvGiAFxS6saK0E0B8jOFwCfYYVP
tHQ/Pf7jbmuy7IwdQkneRiU=
=P0t3
-----END PGP SIGNATURE-----

Ok. That is on insert update delete part.
ant disadvantage on select part?

With Regards
Ashish...

----- Original Message -----
From: "Ron Johnson" <ron.l.johnson@cox.net>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, May 09, 2007 2:18 PM
Subject: Re: [GENERAL] Views- Advantages and Disadvantages

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/09/07 03:48, Ashish Karalkar wrote:

Ok. That is on insert update delete part.
ant disadvantage on select part?

Performance-wise? No.

Both an advantage and disadvantage of views is that it codifies
certain queries in the database.

P.S. - top-posting is rude.

----- Original Message ----- From: "Ron Johnson" <ron.l.johnson@cox.net>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, May 09, 2007 2:18 PM
Subject: Re: [GENERAL] Views- Advantages and Disadvantages

On 05/09/07 03:36, Ashish Karalkar wrote:

Thanks All for your replies,
But then dont we have any disadvantage of using View???

You can't insert into multi-table views.

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGQY5QS9HxQb37XmcRAmgZAJ9LiKaJHwsPlTnZi9UpbiqEANpfdACaAqeS
KtJSyTH7FcWp4JOna9BqNkc=
=EBCc
-----END PGP SIGNATURE-----

On mi�, 2007-05-09 at 12:46 +0530, Ashish Karalkar wrote:

I have found out some of them n are as follows, but I want more reasons
for not using views . I only got one

Disadvantages:

1) Performance : If a view is defined by complex multitable query,then
simple query against that view becomes a coplecated join, and it may take a
long time to complete

2) UPDATES on a view are more tricky.

gnari

On 5/9/07, Ashish Karalkar <ashish.karalkar@info-spectrum.com> wrote:

Thanks Brent for your replay,

What about the Disadvantages, Performance issues [of views]

Views are pretty much neutral from a performance perspective. There
are certain small considerations here and there to think about but you
should really be focusing on how they impact your development and
design strategies.

Probably there are two disadvantages of using views. The first is
that the introduce dependencies on your tables so that whenever you
need to certain things to your table you have to drop the view, do the
thing (like drop a column) and re-create the view. Second issue is
that views are not updatable without the introduction of rules which
are extra dependencies and can be tricky to write.

Now the first problem is not really a problem...in fact, views are a
great way to tell the dba what applications are doing and gives him a
chance to introduce application compatibility if the table structures
change. So I count this as a feature of using views although it will
feel like extra work to do, especially in the beginning. At some
point, views (especially trivial ones) in PostgreSQL will become
automatically updatable which will mitigate the impact of the second
disadvantage. If and when that happens,

IMNSHO, Views are a critical foundation to good development. Complex
queries drifting around in various applications is A Bad Thing, and
views allow you to control this and provide much better encapsulation
of the database and exercise control over what's going on. Many
application developers are uncomfortable with the idea of views (and
other high level database features like functions) but when used
properly, the benefits they can bring to your development and
maintenance can be really dramatic. Use them.

merlin

On May 9, 2007, at 4:36 AM, Ashish Karalkar wrote:

Thanks All for your replies,
But then dont we have any disadvantage of using View???

With Reagrds
Ashish...

I once inherited a database that made extensive use of constants in
views (a la magic numbers) as well as had several instances of
complex views referencing other complex views. The magic numbers made
it extremely difficult at first understand what was going on with the
entire application (both on server and client) because it appeared
that I was getting values out of thin air that were not coming from
any tables or the client. The views-referencing-other-views made
changing a view several layers up in the dependency hierarchy a real
PITA as I had to completely drop all the views and re-add them.

Views are powerful, but with great power comes great responsibility.
They can be very easily abused IMHO.

--
Ilan Volow
Implicit code is inherently evil, and here's the reason why:

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Ashish Karalkar
Sent: Wednesday, May 09, 2007 1:36 AM
To: Andrej Ricnik-Bay; Ron Johnson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Views- Advantages and Disadvantages

Thanks All for your replies,

But then dont we have any disadvantage of using View???

Views can hide important information from the optimizer (especially
index information).

On May 9, 2007, at 14:02 , Dann Corbit wrote:

Views can hide important information from the optimizer (especially
index information).

Really? AIUI, views—at least in PostgreSQL—are implemented using
PostgreSQL's rule system: the entire query is rewritten to include
the view query, and the optimizer sees the rewritten query. What the
optimizer sees is the same as if the view were inlined in the
original query.

Michael Glaesemann
grzm seespotcode net

-----Original Message-----
From: Michael Glaesemann [mailto:grzm@seespotcode.net]
Sent: Wednesday, May 09, 2007 12:14 PM
To: Dann Corbit
Cc: Ashish Karalkar; Andrej Ricnik-Bay; Ron Johnson; pgsql-
general@postgresql.org
Subject: Re: [GENERAL] Views- Advantages and Disadvantages

On May 9, 2007, at 14:02 , Dann Corbit wrote:

Views can hide important information from the optimizer (especially
index information).

Really? AIUI, views-at least in PostgreSQL-are implemented using
PostgreSQL's rule system: the entire query is rewritten to include
the view query, and the optimizer sees the rewritten query. What the
optimizer sees is the same as if the view were inlined in the
original query.

That is a significant achievement, since many database systems do not
have that ability.

Dann Corbit wrote:

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Ashish Karalkar
Sent: Wednesday, May 09, 2007 1:36 AM
To: Andrej Ricnik-Bay; Ron Johnson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Views- Advantages and Disadvantages

Thanks All for your replies,

But then dont we have any disadvantage of using View???

Views can hide important information from the optimizer (especially
index information).

I don't believe that is true in PostgreSQL's case.

Joshua D. Drake

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Views can hide important information from the optimizer (especially
index information).

Really? AIUI, views-at least in PostgreSQL-are implemented using
PostgreSQL's rule system: the entire query is rewritten to include
the view query, and the optimizer sees the rewritten query. What the
optimizer sees is the same as if the view were inlined in the
original query.

That is a significant achievement, since many database systems do not
have that ability.

Another advantage of views is the ability to give zero rights to access
the objects the view is on, but allow the user to execute the view
itself. This allows limiting the view to not only a specific where
clause but also specific columns the user will be able to view.

Joshua D. Drake

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On Wed, 2007-05-09 at 12:02 -0700, Dann Corbit wrote:

Views can hide important information from the optimizer (especially
index information).

I believe that you're mistaken, and you can see it rather easily by
explaining a select on a view (or even a view of views). For example:

rkh@csb-dev=> \d palias
View "unison.palias"
Column | Type | Modifiers
-------------+--------------------------+-----------
palias_id | integer |
pseq_id | integer |
origin_id | integer |
alias | text |
descr | text |
tax_id | integer |
ref_pseq_id | integer |
added | timestamp with time zone |
View definition:
SELECT pa.palias_id, pv.pseq_id, pa.origin_id, pa.alias,
pa.descr, pa.tax_id, pv.ref_pseq_id, pv.added
FROM paliasorigin pa, pseqalias pv
WHERE pv.palias_id = pa.palias_id AND pv.is_current = true;

rkh@csb-dev=> explain select * from palias where tax_id=9606;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------
Hash Join (cost=701397.95..1634572.27 rows=1293890 width=137)
Hash Cond: (pv.palias_id = pa.palias_id)
-> Seq Scan on pseqalias pv (cost=0.00..474670.85
rows=20706650 width=20)
Filter: is_current
-> Hash (cost=647199.80..647199.80 rows=1692012 width=121)
-> Bitmap Heap Scan on paliasorigin pa
(cost=33808.65..647199.80 rows=1692012 width=121)
Recheck Cond: (tax_id = 9606)
-> Bitmap Index Scan on paliasorigin_tax_id_idx
(cost=0.00..33385.65 rows=1692012 width=0)
Index Cond: (tax_id = 9606)
(9 rows)

Long ago I compared a few views with their inlined counterparts and the
upshot is that there is exactly or practically zero difference.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0