Can we Update Views?

Can we Update Views?

Oh yes, we can. There are a few golden rules to be followed to write an updateable view.

BTW, a view is a precompiled SQL statement.You can refer to my other blogpost to quickly recap on what is a view?

http://naveenieus.blogspot.in/2014/09/views-in-sql.html


To put it simple, the following are the NOT HAVE rules to be followed in the definition of a view;

1. Aggregate Functions
2. Distinct, Group By, Having
3. Sub Queries
4. Reference to a non-updateable view in the FROM clause
5. Sub Query
6. Temporary Tables
7. No duplicate view column Names
8. All columns in the base table not having Default Value should be included in the View Definition
9. Columns should be simple and not derived

Reference / Further Reading:

http://dev.mysql.com/doc/refman/5.0/en/view-updatability.html

Tags: Updateable View, View, Rules for Updateable View

Views in SQL

Views are precompiled SQL statements, which can involve more thatn one table. The SQL developer / user can refer to all the columns in the views as being referred to a single table.

Trivia on Views

• Can use joins to get columns from more than one table.
• Can hide columns form the user.
• Can act as aggregated tables where there are complex calculations performed to get the column values.
• Takes lesser tome to execute, since the statement is precompiled.
• Does occupy a little memory but does not store the records as in table.
• Views can be updated, if there is one to one relationship between the rows in the view and tables.

 
 
Tags: Views in SQL, Views, Trivia on Views,

SQL Performance Optimization Checklist (Part -1)

A few points that I came across and implemented for a better SQL query execution and results;


1. Always in a JOIN condition the precedence comes from right. So RIGHT JOIN the table which has less number of rows or is of less size.

2.WHERE clause will always be executed from bottom to top. So tune the filter accordingly.

WHERE c1=1
AND c2 = 2
AND c3= 3 //First Executed

3. Implement any function in the WHERE clause as the last condition.

4. Use indexed column in the WHERE clause. Indexing implies faster search results.
Note: Indexing will create indexed trees for faster search / retrieval.

5. Do not use unwanted indexes in the table.
Note: We can always remove unwanted index in a table.

Database Used: Oracle