Ruihan Wang |
Process SQL statement and access data using primary index (using EXPLAIN to display how data is accessed).
*The rxw05.order_log table have unique primary index: log_No, secondary index: student_name.
BTEQ -- Enter your DBC/SQL request or BTEQ command:
select * from rxw05.order_log where
log_No
= 15;
*** Query completed. 1 rows found. 4 columns
returned.
*** Total elapsed time was 1 second.
log_No
student_name order_date
checkin_date
------
--------------- ----------
------------
15
Jone Smith
12/02/99 12/16/99
11
Adam
11/01/99 11/15/99
BTEQ -- Enter your DBC/SQL request or BTEQ command:
explain select * from rxw05.order_log
where
log_No = 15;
*** Help information returned. 6 rows.
*** Total elapsed time was 1 second.
Explanation
---------------------------------------------------------------------------
1) First, we do a single-AMP RETRIEVE
step from rxw05.order_log by
way of the unique primary
index "rxw05.order_log.log_No = 15" with
no residual conditions.
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.
Process SQL statement and access data using secondary index (using EXPLAIN to display how data is accessed).
*The rxw05.organic table have unique primary index: serial_No, secondary index: organic_name.
BTEQ -- Enter your DBC/SQL request or BTEQ command:
select * from rxw05.organic where organic_name
= 'methanol';
*** Query completed. 1 rows found. 4 columns
returned.
*** Total elapsed time was 1 second.
serial_No organic_name
Carbon_number amount
----------- ---------------
------------- ------
1 methanol
1
500
BTEQ -- Enter your DBC/SQL request or BTEQ command:
explain select * from rxw05.organic where organic_name
= 'methanol';
*** Help information returned. 6 rows.
*** Total elapsed time was 1 second.
Explanation
---------------------------------------------------------------------------
1) First, we do a two-AMP RETRIEVE step from rxw05.organic by
way of
unique index # 4 "rxw05.organic.organic_name
= 'methanol'" with no
residual conditions. The estimated time
for this step is 0.07
seconds.
-> The row is sent directly back to the user as the result of
statement 1. The total estimated time
is 0.07 seconds.
Process SQL statement and access data by full base table scan (using EXPLAIN to display how data is accessed).
*The rxw05.order_log table have unique primary index: log_No, secondary index: student_name.
BTEQ -- Enter your DBC/SQL request or BTEQ command:
select * from rxw05.order_log where
log_no>10;
*** Query completed. 2 rows found. 4 columns
returned.
*** Total elapsed time was 1 second.
log_No
student_name order_date
checkin_date
------
--------------- ----------
------------
15
Jone Smith
12/02/99 12/16/99
11
Adam
11/01/99 11/15/99
BTEQ -- Enter your DBC/SQL request or BTEQ command:
explain select * from rxw05.order_log
where
log_no>10;
*** Help information returned. 12 rows.
*** Total elapsed time was 1 second.
Explanation
---------------------------------------------------------------------------
1) First, we lock a distinct rxw05."pseudo
table" for read on a
RowHash to prevent global
deadlock for rxw05.order_log.
2) Next, we lock rxw05.order_log for read.
3) We do an all-AMPs RETRIEVE step from
rxw05.order_log by way of an
all-rows scan with a
condition of ("rxw05.order_log.log_No > 10")
into Spool 1, which
is built locally on the AMPs. The size of
Spool 1 is estimated
with no confidence to be 27 rows. The
estimated time for this
step is 0.15 seconds.
4) 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.15 seconds.