Books in black and white
 Books Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Previous << 1 .. 189 190 191 192 193 194 < 195 > 196 197 198 199 200 201 .. 241 >> Next

Example 6: Large Table SELECT with More Complex Condition

Assume that a table named Main is a very large table and that its columns NumA, NumB, Kind, and Event are each defined as non-unique secondary indexes. The following request uses these indexes to apply a complex conditional expression.

If the EXPLAIN modifier precedes the request, as follows:

EXPLAIN SELECT COUNT(*) FROM Main WHERE NumA='101'

AND NumB='02'

AND Kind='B'

AND Event='001';

A possible response shows that bit mapping would be used:

Explanation -----------------------------------------------------------

1) First, we lock TESTING.Main for read.

2) Next, we do a BMSMS (bit map set manipulation) step that intersects the following row id bit maps:

1) The bit map built for TESTING.Main by way of index

# 12 "TESTING.Main.Kind = 'B'".

2) The bit map build for TESTING.Main by way of index

# 8 "TESTING.Main.NumB = '02'".

3) The bit map built for TESTING.Main by way of index

9-28

Teradata RDBMS for UNIX SQL Reference

EXPLAIN Modifier: Examples of Complex Queries

# 16 "TESTING.Main.Event = '001'".

The resulting bit map is placed in Spool 3. The estimated time for this step is 17.77 seconds.

3) We do a SUM step to aggregate from TESTING.Main by way of index # 4 "TESTING.Main.NumA = '101'" and the bit map in Spool 3 (Last Use) with a residual condition of ("(TESTING.Main.NumB = '02') and ((TESTING.Main.Kind = 'B') and (TESTING.Main.Event = '001'))"). Aggregate Intermediate Results are computed globally, then placed in Spool 2.

4) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of an all-rows scan into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated to be 20 rows. The estimated time for this step is 0.11 seconds.

5) 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 a result of statement 1.

Example 7: Implicit Multistatement Transaction, INSERT

In the following BTEQ multi-statement request, which is treated as an implicit transaction, the statements are processed concurrently.

Therefore, an EXPLAIN modifier preceding this request:

EXPLAIN

INSERT Charges (3 INSERT Charges INSERT Charges INSERT Charges INSERT Charges INSERT Charges INSERT Charges INSERT Charges INSERT Charges INSERT Charges

0001, ' AP2-00 04', 890825, 4 5.0)
(30002 'AP2- 0004' , 890721, 12. 0)
(30003, 'AP2- 0004' , 890811, 5 2 )
(30004, 'AP2- 0004' , 890831, 37. 5
(30005, 'AP2- 0004' , 890825, 11. 0)
(30006, 'AP2- 0004' , 890721, 24. 5)
(30007, 'AP2- 0004' , 890811, 40. 5)
(30008 'AP2- 0004' , 890831, 32. 0
(30009 'AP2- 0004' , 890825, 41. 5)
(30010, 'AP2- 0004' , 890721, 22. 0)

Teradata RDBMS for UNIX SQL Reference

9-29

EXPLAIN Modifier: Examples of Complex Queries

produces the following possible response (Teradata mode):

Explanation ----------------------------------------------------------

First, we execute the following steps in parallel
1) We do an INSERT into PERSONNEL.charges.
2) We do an INSERT into PERSONNEL.charges.
3) We do an INSERT into PERSONNEL.charges.
4) We do an INSERT into PERSONNEL.charges.
5) We do an INSERT into PERSONNEL.charges.
6) We do an INSERT into PERSONNEL.charges.
7) We do an INSERT into PERSONNEL.charges.
8) We do an INSERT into PERSONNEL.charges.
9) We do an INSERT into PERSONNEL.charges.
10) We do an INSERT into PERSONNEL.charges.

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

-> No rows are returned to the user as the result of statement 1.

No rows are returned to the user as the result of statement 2.

No rows are returned to the user as the result of statement 3.

No rows are returned to the user as the result of statement 4.

No rows are returned to the user as the result of statement 5.

No rows are returned to the user as the result of statement 6.

No rows are returned to the user as the result of statement 7.

No rows are returned to the user as the result of statement 8.

No rows are returned to the user as the result of statement 9.

No rows are returned to the user as the result of statement 10.

Example 8: ANSI Versus Teradata Mode

This example shows the EXPLAIN differences between running the session in ANSI versus Teradata mode.

EXPLAIN UPDATE Employee set deptno = 650 WHERE deptno = 640;

ANSI mode produces the following result:

Explanation

1) First, we lock a distinct PERSONNEL."pseudo table" for write on a RowHash to prevent global deadlock for PERSONNEL.employee.

2) Next, we lock PERSONNEL.employee for write.

3) We do an all-AMPs UPDATE from PERSONNEL.employee by way of an all-rows scan with a condition of ("PERSONNEL.employee.DeptNo = 640").

-> No rows are returned to the user as the result of statement 1.

9-30

Teradata RDBMS for UNIX SQL Reference
Previous << 1 .. 189 190 191 192 193 194 < 195 > 196 197 198 199 200 201 .. 241 >> Next