6.1.2 How to check access paths

(1) How to check access path information by running #SET OPT REPORT

You can display access path information by running the #SET OPT REPORT subcommand of the adbsql command. To display access path information:

Procedure

1. Run the adbsql command.

2. Run the #SET OPT REPORT subcommand of the adbsql command.

   #SET OPT REPORT ON TYPE=PATH EXEC=PREPARE;

   Note

   In this example, EXEC=PREPARE is specified because the intention is to display the access path information without actually running the SQL statement. If you want to run the SQL statement and display the access path information, do not specify EXEC=PREPARE.

3. Run the SQL statement.

   SELECT "C1" FROM "T1" WHERE "C1">10;

   When you run this SQL statement, access path information is displayed as follows:

   <<Tree View>>
    
        1 QUERY : 1
        2   SELECT STATEMENT
        3   +-KEY SCAN(ADBUSER01.T1)
    
    
   <<Detail   >>
    
   QUERY : 1
        3 KEY SCAN(ADBUSER01.T1)
          INDEX NAME        : IDX_C1
           INDEX TYPE       : B-TREE
           INDEX COLUMN     : C1 ASC (>)
    
    
   <<SQL Info >>
    
   Version           : 03-01(Aug  5 2015 09:32:34)
   Transaction ID    : 6197
   Connection Number : 3
   SQL Serial Number : 1

In this example, the table and B-tree index are defined as follows:

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

To Page Top

(2) How to check access path information output in SQL trace information

Use a text editor to view SQL trace information output in an SQL trace file. SQL trace files have the following file names:

• $ADBDIR/spool/adbsqltrc01.log to adbsqltrc08.log

The following is an example of access path information output as SQL trace information:

Example:

[SQL]
SELECT * FROM "T1" WHERE "C1"=? AND "C2"=? AND "C3"=?
 
[access path]
<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   +-TABLE SCAN(T1)

For details about SQL trace information, see Running SQL tracing in the HADB Setup and Operation Guide.

To Page Top