What is the impact of creating unnecessary views on data base?

[TekMem]

What is the impact of creating unnecessary views on data base? Creating views makes my work easier.
Thanks.

 

[SQLDenis]

>>What is the impact of creating unnecessary views on data base

Having more views in the DB. There is no performance impact

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SgtJarrow]

If you are using views, how can you define them as unnecessary?

To my knowledge, a view doesn't affect the performance of the database much if at all. It may affect your applications ACCESSING the view, depending on the joins, associations, and indexes accessed when displaying the view. But I will quickly defer to any of the SQL gurus here.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[TekMem]

Here I am trying to add at least 16 columns in one of the row in a table to find out percentage. So I have created 16 views OTC1,OTC2...
in OTCn,OTCm I am getting percentage. Is there a way so that I create only one or two views?

CREATE OR REPLACE VIEW VEW_OTC1
(col1,col2) as
select 1  as col1,VARCHAR_255_17
from otc_env_main where to_date(VARCHAR_10_7,'mm-dd-yyyy')-to_date(VARCHAR_255_10,'mm-dd-yyyy') >60
union
select 0 as col1,VARCHAR_255_17
from otc_env_main where to_date(VARCHAR_10_7,'mm-dd-yyyy')-to_date(VARCHAR_255_10,'mm-dd-yyyy') <60

CREATE OR REPLACE VIEW VEW_OTC2
(col3,col4)as
select 1 as col2,VARCHAR_255_17
from otc_env_main where to_date(VARCHAR_10_1,'mm-dd-yyyy')-to_date(VARCHAR_255_11,'mm-dd-yyyy') >60
union
select 0 as col2,VARCHAR_255_17 
from otc_env_main where to_date(VARCHAR_10_1,'mm-dd-yyyy')-to_date(VARCHAR_255_11,'mm-dd-yyyy') <60


CREATE OR REPLACE VIEW VEW_OTCn
(col1,col3,col2)
AS select col1,col3,col4 from VEW_OTC1, VEW_OTC2,...
where col2=col4 and ...

CREATE OR REPLACE VIEW VEW_OTCm
(col1,col3,col2,sum,per)
AS select col1,col3,col2,(col1+col3),(col1+col3...)*100/16 from VEW_OTCn

 

[SQLDenis]

BTW

CREATE OR REPLACE VIEW VEW_OTC1


this is not SQL server, is this Oracle?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[TekMem]

Yes It is oracle but select statements should be similar.

 

[ESquared]

It doesn't matter. There are a host of differences between the two databases. For example, I don't know how to do it in Oracle, but your expression to_date() with a format specifier won't be sargable. Consult the Oracle forum (there is one here!) about that.

 

[ca8msm]

You've also been pointed to the Oracle forum in previous posts you've made here. There is a reason for this - the syntax, functions and methods it uses are different to SQL Server.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]