Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Introduction to the Teradata® RDBMS for UNIX® Version 2 Release 2.1 - NCR

NCR Introduction to the Teradata® RDBMS for UNIX® Version 2 Release 2.1 - NCR, 1998. - 315 p.
Download (direct link): inntroduktionteradata1998.pdf
Previous << 1 .. 36 37 38 39 40 41 < 42 > 43 44 45 46 47 48 .. 76 >> Next


CREATE MACRO NewEmp (name (VARCHAR(12)),

number (INTEGER, NOT NULL), dept (INTEGER, DEFAULT 100)

)

AS (INSERT INTO Employee (Name,

EmpNo,

DeptNo

)

VALUES (:name,

:number,

:dept

)

UPDATE Department SET EmpCount=EmpCount+1 WHERE DeptNo=:dept ;

)

;

This macro defines parameters that must be filled in each time it is executed. These are indicated with a leading : character.

8-4

Introduction to the Teradata RDBMS for UNIX
Application Development

Using Macros as SQL Applications

Using a Macro

Modifying a Macro

This example illustrates how to use the NewEmp macro to insert data into the Employee and Department tables.

The information to be inserted is the name, employee number, and department number for employee H Goldsmith. The EXECUTE macro statement looks like this:

EXECUTE NewEmp ('Goldsmith H', 10015, 600);

This example illustrates how to modify a macro. Suppose you wanted to change the NewEmp macro so the default department number is 300 instead of 100. The REPLACE MACRO statement looks like this:

REPLACE MACRO NewEmp (name (VARCHAR(12)),

number (INTEGER, NOT NULL), dept (INTEGER, DEFAULT 300)

)

AS (INSERT INTO Employee (Name,

EmpNo,

DeptNo

)

VALUES (

)

name,

number,

dept

UPDATE Department SET EmpCount=EmpCount+1 WHERE DeptNo=:dept

)

Deleting a Macro

This example illustrates how to delete a macro. Suppose you wanted to drop the NewEmp macro from the database. The DROP MACRO statement looks like this:

DROP MACRO NewEmp;

Introduction to the Teradata RDBMS for UNIX

8-5
Application Development

Using the EXPLAIN Statement As a Tool To Optimize Your SQL Code

Introduction

Using the EXPLAIN Statement As a Tool To Optimize Your SQL Code

Teradata SQL supplies a very powerful EXPLAIN statement that allows you to try out various approaches to the same answer. The EXPLAIN statement not only explains how it would go about executing your SQL query, it provides information about the relative time the query would take to execute.

While it is true that the optimizer uses indexes to maximize query performance, it does not reformulate a query it is presented with to make it more efficient; it only performs that particular query in the most efficient way it knows how. The power of EXPLAIN is that it allows you to experiment with different approaches to an answer, then select the one that performs best.

EXPLAIN details what indexes (if any) the optimizer would use to process the request, identifies any temporary files that would be generated, shows whether the transactions for the statement would be dispatched in parallel, and so on.

You should always make the results of EXPLAINs an integral part of your code review process; they might indicate inefficiencies or errors in the structure of your queries.

8-6

Introduction to the Teradata RDBMS for UNIX
Application Development

Using the EXPLAIN Statement As a Tool To Optimize Your SQL Code

The Personnel.Employee table has a unique primary index defined Using EXPLAIN: First on the EmpNo column and a nonunique secondary index defined

Example on the Name column. The EXPLAIN statement to examine this

query looks like this:

EXPLAIN SELECT Name,

DeptNo FROM Employee WHERE EmpNo = 10009

;

The output of the query looks like this:

Explanation

1) First, we do a single-AMP RETRIEVE step from Personnel.Employee by way of the unique primary index "PERSONNEL.Employee.EmpNo = 10009" with no residual conditions. The input table will not be cached in memory. The result pool will not be cached in memory. The estimated time for this step is 0.03 seconds -> The row is sent directly back to the user as the result of statement 1. The total estimated time is 0.03 seconds.

Introduction to the Teradata RDBMS for UNIX

8-7
Application Development

Using the EXPLAIN Statement As a Tool To Optimize Your SQL Code

Using EXPLAIN: Second Example

EXPLAIN SELECT EmpNo,

DeptNo FROM Employee WHERE Name = 'Smith T'

t

The output of the query looks like this:

Explanation

1) First, we lock PERSONNEL.Employee for read.

2) Next, we do an all-AMPS RETRIEVE step from PERSONNEL.Employee by way of an all-rows scan with a condition of ("PERSONNEL.Employee.Name = 'Smith T'") into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated to be 2rows. The estimated time for this step is 0.03 seconds.

3) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.

-> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0 hours and 0.03 seconds.

The Personnel.Employee table request EXPLAINed below has a WHERE condition that is based on a column defined as a nonunique index. The Teradata RDBMS places a READ lock on the table. The EXPLAIN statement to examine the query looks like this:

8-8

Introduction to the Teradata RDBMS for UNIX
Previous << 1 .. 36 37 38 39 40 41 < 42 > 43 44 45 46 47 48 .. 76 >> Next