6.1.3 Examples of access paths

CREATE TABLE "T1"("C1" INT,"C2" DEC,"C3" CHAR(10)) IN "DBAREA01"
CREATE INDEX "IDX_C1" ON "T1"("C1") IN "DBAREA02" EMPTY
CREATE INDEX "IDX_C3" ON "T1"("C3") IN "DBAREA02" EMPTY
CREATE TABLE "T2"("C1" INT,"C2" DEC,"C3" CHAR(10)) IN "DBAREA01"

SELECT * FROM "T1","T2"
    WHERE "T1"."C1"="T2"."C2"

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT                       ...1
     3   +-NESTED LOOP JOIN                     ...2
     4     |-TABLE SCAN(ADBUSER01.T2)           ...3
     5     +-INDEX SCAN(ADBUSER01.T1) -ORDER    ...4
 
 
<<Detail   >>
 
QUERY : 1
     5 INDEX SCAN(ADBUSER01.T1)                 ...5
       INDEX NAME        : IDX_C1               ...6
        INDEX TYPE       : B-TREE               ...7
        INDEX COLUMN     : C1 ASC (=)           ...8
 
 
<<SQL Info >>
 
Version           : 03-01(Aug  5 2015 09:32:34)
Transaction ID    : 6197
Connection Number : 3
SQL Serial Number : 2

Explanation:

1. The SELECT statement is executed.

2. In the table join processing, a nested loop join is executed.

3. In table T2 retrieval processing, a table scan is performed.

4. In table T1 retrieval processing, an index scan is performed. -ORDER indicates that sequential execution, not out-of-order execution, is applied.

5. Detailed information about the index scan on table T1 is displayed.

6. B-tree index IDX_C1 is used during the index scan.

7. The index type is displayed. B-TREE means that this is a B-tree index.

8. Information about B-tree index IDX_C1 is displayed:

   • C1: Indexed column of B-tree index IDX_C1

   • ASC: Key value sort order (ascending)

   • (=): Range search condition specification

CREATE TABLE "T1"("C1" INT,"C2" DEC,"C3" CHAR(10)) IN "DBAREA01"
CREATE TABLE "T2"("C1" INT,"C2" DEC,"C3" CHAR(10)) IN "DBAREA01"

SELECT * FROM "T1"
     WHERE "C1"=ANY(SELECT "C2" FROM "T2" WHERE "C1"="T1"."C2")

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT                               ...1
     3   +-TABLE SCAN(ADBUSER01.T1)                     ...2
     4     +-SUBQUERY LOOP                              ...3
     5       |-CREATE LOCAL WORK TABLE(WORK TABLE 1)    ...4
     6       | +-QUERY SCAN(QUERY 2)                    ...5
     7       +-WORK TABLE SCAN(WORK TABLE 1)            ...6
     8
     9 QUERY : 2                                        ...7
    10   SUBQUERY LOOP                                  ...8
    11   +-TABLE SCAN(ADBUSER01.T2)                     ...9
 
 
<<SQL Info >>
 
Version           : 03-01(Aug  5 2015 09:32:34)
Transaction ID    : 6197
Connection Number : 3
SQL Serial Number : 3

Explanation:

1. The SELECT statement is executed.

2. In table T1 retrieval processing, a table scan is executed.

3. Nested loops work table execution or nested loops row value execution is used to process the subquery specified in the quantified predicate (ANY).

4. In the subquery processing, a local work table is created.

5. A query scan is performed. The query scan QUERY : 2 displayed in tree row number 9 is performed.

6. In the subquery processing, the work table is scanned.

7. Detailed information about the subquery is displayed.

8. In the subquery processing, nested loops work table execution or nested loops row value execution is used.

9. In the table T2 retrieval processing, a table scan is performed.