Download (direct link):
Using the GROUP BY clause If you use a column defined as NULL for grouping aggregate results, the results may be confusing. For example, assume you submit this query to find the average salary for each department in the organization: SELECT DeptNo, AVG(Salary) FROM Employee GROUP BY DeptNo; The results can differ, as follows:
IF the DeptNo column is . THEN the result .
allowed to default to NULL, and two employees have not yet been assigned a department number lists the computed average for those two employees under a NULL department number. This might be confusing.
defined with DEFAULT and the specified constant is meaningful (such as Unknown) is more meaningful.
Note: You can convert a NULL into a default value (with NULLIF) and a default value into NULL (with COALESCE), as explained previously for aggregated results.
Using a column in a join that contains NULLs or constants If a column used in a join contains either NULLs or specified constants, the results might be misleading. For example, assume you want to join the Employee and Department tables to obtain a listing of employees and their workplaces: SELECT Name, Loc FROM Employee, Department WHERE Employee.DeptNo = Department.DeptNo;
Using a column in a join that contains NULLs or constants Without some care, incorrect results may occur, such as:
IF the . THEN the .
row for Omura in the Employee table contains a NULL in DeptNo, and the row for Administration in the Department table also contains a NULL in DeptNo result contains: • No information for Omura • No information for any employee in Administration.
DeptNo column for Omura, Administration, and Engineering contain the same constant (for example, NONE) WHERE condition for the query is satisfied. This links Omura with both Administration and Engineering, even though Omura works for only one department (which might not be either Administration or Engineering): NameLoc Omura ENG Omura ADM
2 - 10 Teradata RDBMS Database Administration
Chapter 2: Building the Teradata RDBMS
Using a column in a join that contains NULLs or constants (continued)
To prevent an employee row with an unknown department number from matching an unrelated department row that also has an unknown department number, use a different default value to represent unknowns in the DeptNo column, as follows:
In the Employee table, add a dummy "unknown" employee who has a DeptNo equal to the value of the default used for the Department table.
To the Department table, add a dummy "unknown" department that has a DeptNo equal to the value of the default used for the Employee table.
Note: In the example query, a full outer join could be used instead of the inner join to obtain information for non-matching rows when there is a NULL in DeptNo. (Also, as noted above, the COALESCE function could be used to change a NULL to a value.) However, using outer joins to obtain non-matching rows may be more efficient.
Compression can reduce storage costs and enhance system performance. Use the
COMPRESS phrase to compress specific values and/or NULLs to zero space. You can
compress all NULLS in a column and up to 255 distinct, frequently repeated column
values. (For table header space considerations, see "COMPRESS: State of Presence Bits per
Column" on page 2-15.)
• You cannot compress the following:
- component of the PI, whether partitioned or not
- volatile table columns
- identity column
- derived table columns
- spool table columns
- referenced PK columns
- referencing FK columns
The general rules for data compression on all other columns are as follows:
• To qualify for compression, a field must be fixed-length and have a length of 255 or less characters for a CHARACTER type and 255 or less bytes for a BYTE type.
• Most data types can be compressed, including date (if enclosed in quotes), but the value to be compressed must also be in the repertoire of the character set defined for the session.
• When you use the COMPRESS keyword alone with no argument, NULLs are compressed to zero space.
• If you include one or more constant arguments, each occurrence of the specified constants and all NULLs for the column are compressed to zero space.
• You can compress more than one column, and you can specify up to 255 distinct constant values to be compressed per column.
Using the IDENTITY column attribute
IDENTITY is an optional attribute used to generated a unique number for every row inserted into the table on which it is defined. An identity column does not have to be the first column in the table or defined as an index. (For more information, see "Using an Identity Column" on page 2-66.)
2 - 10
Teradata RDBMS Database AdministrationChapter 2: Building the Teradata RDBMS