Modifying Views Teradata

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:

  • Performs a join operation – more than one table
  • Selects the same column twice – wouldn't know which one to use
  • Derives data – does not undo the math or calculation
  • Performs aggregation – eliminates detail data
  • Uses OLAP functions – data does not exist in a column
  • Uses a DISTINCT or GROUP BY – eliminate duplicate rows

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).


9 Rows Returned

Explanation

  1. First, we lock a distinct MIKEL."pseudo table" for write on a RowHash to prevent global deadlock for MIKEL.customer_table.
  2. Next, we lock MIKEL.customer_table for write.
  3. We do an all-AMPs UPDATE from MIKEL.customer_table by way of an all-rows scan with a condition of (
  4. "(MIKEL.customer_table.Customer_name = 'myname')AND
  5. ((MIKEL.customer_table.Customer_name = 'myname')AND
  6. (MIKEL.customer_table.phone_number = 1 ))").
    −> No rows are returned to the user as the result of statement 1.

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:

  1. Data values are needed and they are not stored in a real table
  2. Writing the SQL needs to be simplified
  3. There is a need to mix OLAP and aggregation
  4. Aggregation processing on aggregate values is needed
  5. Table data needs insulation from end user access (protection) or security
    • At the row level with a WHERE
    • At the column level by not selecting one or more columns

Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics