There may be times when an existing production database table does not provide precisely what you need. Sometimes, a particular query might need summarized or aggregated data. At other times, a small number of rows, from a very large table or data for a specific organization, are required to find an answer.
In a data warehouse with millions of rows, it might take too long to locate, derive or mathematically calculate the data needed. This is especially true when it is needed more than once per day. So, a view might not be the best solution or a view does not exist and you do not have the privilege to create one and both a view and derived table take too long. Any of these conditions prevent the ability to complete the request.
In the past, temporary tables have been created and used to help SQL run faster or be more efficient. They are extremely useful for solving problems that require stored "temporary"' results or which require multiple SQL steps. They are also great for holding aggregated or summarized data.
Most databases lose speed when they have to:
Temporary tables are often useful in a de-normalization effort. This might be done to make certain queries execute faster. Other times it is done to make the SQL easier to write, especially when using tools that generate SQL. However, these temporary tables are real tables and require manual operations to create, populate, and maintain them.
As a result, better name for these temporary tables might be interim or temporal tables. They exist for a specific period of time and when no longer needed, they are dropped to free up the disk space. During the interim time, they provide a valuable service. However, if the data in the original tables changes, the interim tables must be repopulated to reflect that change. This adds a level of difficulty or complexity regarding their use.
Creating Interim or Temporal Tables
The following series of commands provide an example of creating, inserting, and then entering SQL queries on an interim or temporal table (real table for short-term use):
This table breaks First Normal Form (1NF) in that it contains a repeating group for twelve monthly sales columns in every row. Normally, each row in a table represents an hour, a day, a week, or a month worth of sales. However with billions of rows, it requires extra time to read all the rows and consolidate them as calculated subtotals.
Therefore, the above table will make summary processing faster. In some cases, it will also make the SQL easier to write. Both of these become important considerations as the frequency of requests and the number of users needing this data, increases.
The Employee, Department and Sales tables below are used to demonstrate temporary tables:
To populate the interim table with data for September through December of the year 2000, the next INSERT / SELECT might be used:
Then, to display the sales for the last quarter of the year 2000, all that is required is a SELECT like the following:3 Rows Returned
The previous SQL shows how to create, insert, and access an interim table. Aside from the positive aspects of using this technique, there are other considerations. First, to provide current data, the rows in the table might need to be updated periodically. Otherwise, using active data warehouse tables and the summary table result in different answers to the same question.
Another issue regarding this technique is that a "real table" must be created and later it will be dropped manually. Both of these actions require updates in the Data Dictionary (DD). Additionally, you must have the privileges to CREATE and DROP a table as well as available Permanent space. Most data warehouse sites do not allow for this type of loose security and space management. It is not normally an acceptable practice to allow all users to create these temporal tables for this purpose.
Potential gains using an Interim table:
The associated costs of this table:
The main problem is that a permanent table had to be created. This action is manual and is done within the DD because it is a real table. Another problem is that the contents of the table may quickly become out of date the moment a row changes in the original table.
The biggest single stumbling block is probably the inability to create a table, due to a lack of the proper privileges or Permanent space. The only solution is to submit a change request to the Database Administrator (DBA). Therefore, the process might take a couple of days to complete. This can be devastating when the data is needed immediately. So instead of using interim tables, this may be a perfect opportunity to use temporary tables.
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.