1.1.1

1 node_details FLIGHTAVAILABILITY :
CONSTRAINTSCAN (45.39%) execute_time 8.362 ms returned_rows 14
no_opens 1 scan_qualifiers None scanned_object
APP.6__FLIGHTAVAILABILITY__FLIGHT_ID__SEGMENT_NUMBER:base-table:APP.FLIGHTAVAILABILITY
scan_type
node_details WHERE : ((F.FLIGHT_ID = FA.FLIGHT_ID) and true)
3 rows selected
In this example, the query is distributed from the peer client (the ORIGINATOR) to the single data store in
the cluster. As there is only one data store member in the cluster, the fastest and slowest plans reported in
the output refer to the same plan (the same information is repeated) in both sections.
The body of a SQLFire query plan is divided into two main sections identified by the query plan codes
QUERY-SCATTER and QUERY-RECEIVE. Each section contains indented rows showing nested tasks.
Because each task feeds into the task above it, you should read the query plan starting with the bottom,
most-nested task in each section. Individual task rows show the task execution time in milliseconds, as well
as the overall percentage of time spent completing that task relative to all of the tasks in the section, combined.
QUERY-SCATTER is displayed only when you use the EXPLAIN stmt_id syntax (it does not appear
in the above output). QUERY-SCATTER includes all of the tasks associated with delivering the query from
the query coordinator to one or more data stores in the cluster, and then receiving and processing results on
the query coordinator. This example uses a single data store member to provide all results, so very little time
is spent on the query coordinator. When data is distributed across multiple data stores (for partitioned and/or
replicated tables) additional time will be spent on the query coordinator to merge the results from each data
store.
The QUERY-RECEIVE section of the plan describes the query processing tasks that a data store member
takes in order to execute a query. This header displays the member ID and process ID of the data store, as
well as the total aggregate time for all of the subordinate tasks. If the query is distributed to multiple data
store members, then each member will display a separate QUERY-RECEIVE section. Each section should
be identical, unless tables are partitioned across different sets of machines of VMs.
The example shows that a nested loop join (NLJOIN) is performed in order to produce the results. NLJOIN
always has two nested subtasks that represent the outer and inner tables in the join. In this example, a
TABLESCAN is first performed on the FLIGHTS table, which holds the foreign key FLIGHT_ID. The
TABLESCAN returns a single row. The inner table then uses the results of the TABLESCAN in a
CONSTRAINTSCAN performed on the FLIGHTAVAILABILITY table.
Table scans are very expensive operations in query processing, and even this quick query of a small data set
shows that the majority of the time (51% of the query execution time) was spent scanning the FLIGHTS
table. The remaining steps show how creating an index changes the query plan and improves performance.
7. To avoid the costly table scan used in the sample query, execute the following command to create an index
on the FLIGHT_ID column of the FLIGHTS table:
CREATE INDEX fa_index ON flights (flight_id);
8. Re-run the query, creating a new query plan. Then examine the new plan:
explain select * from flights f, flightavailability fa where
f.flight_id=fa.flight_id and f.flight_id='AA1116';
You should receive output similar to:
MEMBER_PLAN
--------------------------------------------------------------------------------------------------------------------------------
ORIGINATOR curwen(26888)<v1>:47762 BEGIN TIME 2013-06-14 14:54:20.852
vFabric SQLFire User's Guide274
Managing and Monitoring vFabric SQLFire