A view cannot be altered like a table. Instead, the entire view (SELECT) is replaced using the REPLACE VIEW format of DDL. Unlike the CREATE VIEW, the REPLACE VIEW does not verify that the name is unique. Instead, it anticipates that the view exists and replaces it with the new SELECT statement. Therefore, it is advisable to manually verify that the correct VIEW is being replaced.
It is advisable to do a SHOW VIEW to obtain the latest version of the view. Then, copy and modify it to replace the current view. Besides making it easier than rewriting the DDL, the SHOW VIEW makes it safer and guarantees that nothing is inadvertently missed from a previous REPLACE VIEW operation.
When using the REPLACE VIEW, if the view name does not exist, an error does not occur. Instead, the REPLACE builds a new view the same as using CREATE VIEW.
The syntax of the REPLACE VIEW follows:
The next example changes the Aggreg_Order_v view to process only orders for the year 2001:
Notice that the keyword REPLACE appears instead of the original CREATE and the WHERE clause is changed from the original CREATE VIEW statement.
Modifying Rows Using Views
Although views are primarily used for retrieving rows from one or more tables, they can also be used for modifying the rows in a data table. That's right views can UPDATE tables! Since views are "virtual tables," users can do anything with a view that their privileges allow, including updates. Privileges work the same on views as they do on tables. Hence, they possess the same ability for row modification, with a few additional rules.
All Data Manipulation Language (DML) commands (INSERT, INSERT/SELECT, UPDATE, and DELETE) may be used. The only difference is that the name of the view and its columns are used instead of the underlying table and column names.
DML Restrictions when using Views
There are a few restrictions that disallow maintenance activity on a view with an INSERT, UPDATE or DELETE request. A view cannot be used for maintenance if it:
INSERT using Views
A view may be used to create new rows within a data table. Like the update process, an INSERT cannot enter data into a column that is not listed in the view. Although this is also a form of security, it can cause operational errors. When a view does not reference a column, that column cannot receive data using that view. Therefore, a NULL will be stored in all columns not named in the view. If one of these columns is declared as NOT NULL in the data table, the INSERT fails.
UPDATE or DELETE using Views
A view may be used to modify (UPDATE) the columns of an existing row or remove rows (DELETE) in a data table. However, the UPDATE cannot change the values in columns not specified in the view. Therefore, it is impossible for users to accidentally update data that they do not have access to within the view, hence increasing security and data integrity.
WITH CHECK OPTION
For a long time, Teradata has allowed views to modify data rows. In doing this, only the rows that the view returned were eligible to be updated. Since the incorporation of ANSI functionality into Teradata, this is no longer true. ANSI indicates that when an UPDATE or DELETE reference a view to modify or delete rows of a table, all the rows of the table should be eligible. This means that by default, the WHERE clause is ignored.
Although this can be a good thing, it may not always be the desired outcome. For instance, if a user updates a row using its PI, only the row(s) with that specific value is changed. However, when a non-indexed column is used, there is far more likelihood that more than one row to be updated.
Here is why: Let's say that it is time to give a raise to an employee. Furthermore, it is decided to reference the employee's name for the comparison because every SELECT performed on the view returns only one employee with that name. Remember, when a SELECT uses a view, the internal WHERE clause compares and eliminates rows not meeting the conditional comparison.
However, ANSI indicates that when the view is used for the maintenance, the WHERE clause is ignored. The system looks at all rows for potential modifications. If there are two or more people with the same last name anywhere in the table, all of them get the raise. Therefore, a WHERE should be used to constrain the UPDATE, or the WITH CHECK OPTION should be specified in the view at creation time.
It is worth mentioning that the WITH CHECK OPTION did not exist in previous releases of Teradata. In those releases prior to V2R2.0, the WHERE clause conditions were always applied when an UPDATE or DELETE was performed through a view. In all releases since V2R2.0, any UPDATE or DELETE activity using a view, that does not have a WITH CHECK OPTION explicitly defined, allows an authorized user to manipulate all rows of a table, not just those seen in a SELECT. NCR provided a migration script that added the check option phrase to existing views when upgrading to the later releases.
In Teradata, the additional key phase: WITH CHECK OPTION, indicates that the WHERE clause conditions should be applied during the execution of an UPDATE or DELETE against the view. This is not a concern if views are not used for maintenance activity due to restricted privileges.
With that being stated: in the later V2R3 releases, the WHERE is always being applied against the data, incorrectly, when performing an UPDATE or DELETE against a view. NCR has been notified and is looking at a fix. Currently, when maintenance is performed in ANSI mode, the WITH CHECK OPTION applies the WHERE clause two times (this can be seen in the output of the EXPLAIN on the following page).
Locking and Views
Now that views have been demonstrated there is another consideration to understand. In an active data warehouse, there exists the potential for rows to be locked for a change (WRITE) while other users are attempting to read them. When users need immediate access to rows, the LOCKING modifier is often used in views to request an ACCESS lock to prevent a query from suspending when other users are modifying the underlying table. A WRITE lock does not block an ACCESS lock. That's the good news.
On the other side of the coin, it means that one or more returned rows might be before or after a pending change. In other words, running the same request twice might return different results due to the timing of the modifications. That is why the ACCESS lock is referred to as a "dirty read." There is more information on LOCKING in the transaction chapter in this book.
The following CREATE VIEW uses the LOCKING modifier to downgrade the normal READ lock of the SELECT to an ACCESS lock:
Views are a good option whenever:
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.