Data Maintenance Teradata

In a data-warehousing environment, the trend is to spend 90 to 96% of the time selecting data. The rows of the table are there to provide insight into the operation of the business. Everyone is looking for the golden query, the one that saves the corporation, $10,000,000.00.

Another 2 to 3% of the time using the data warehouse is spent loading new data. Most of this data is pulled from the operational on-line systems or the operations of the business. Of the remaining time, 1 to 2% will be spent deleting old rows. For the most part, a data warehouse might never update or modify existing data; since to a large degree, it is historic data.

Regardless of how the Teradata RDBMS data warehouse is used, there will come a point in time when you need to use the functionality of the other Data Manipulation Language (DML) commands besides SELECT.

Considerations for Data Maintenance

Whenever data maintenance is being performed attention needs to be paid to the result of the operation. All changes made by one of these DML commands should be verified in a test database before being executed on a production database. Unless the before data image of rows is stored in the Permanent Journal, once changes are made and committed to the database, they are permanent. So, make sure the SQL is making the changes that were intended.

Safeguards

In order to use the commands in this chapter, the appropriate privileges are required to make changes to a given table within a database. These privileges are named the same as the DML operation: INSERT, UPDATE, and DELETE.

All three of these DML statements need to obtain a write lock on a table or row. Therefore, a row cannot be changed by one DML statement while another DML statement is reading or modifying the row. This is because a lock is placed on the object during an update. Any conflicting SQL commands are queued until the previous lock is released. Likewise, a row cannot obtain a read lock on a row while it is being updated.

A row cannot be changed or inserted if a new data value breaks the rules established in the constraint. Constraints are sometimes placed on one or more columns in a table. The constraint defines rules regarding the characteristics of and the types of data values that may be stored within the column(s) of a row.

Constraints are of these types:

  • Check, a specific or range of values
  • Referential Integrity, Primary key exists for a Foreign key
  • Unique, there is one and only one row per legal value
  • NOT NULL, there must be a value and NULL is not allowed

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

Teradata Topics