SCAN
  # Table Name  :aa...a(aa...a) bbbbbbbbbb(bb...b) (xx...x) cc...c{AA...A}
    Cost        :d(ee...eROW) {T-BB...B,I-CC...C,P-DD...D,AND-EE...E,OR-FF...F}
    RDAREA      :ff...f-CLM gg...g(hh...h) (ii...iRD/jj...jBES) [kkkk(kk...k),...] ll...l
    Rebalance   :mm...m
    Scan Type   :nn...n(oo...o)  [pp...p]
    Index Name  :qq...q rrrrrrrrrr(rr...r) (ss...s)tt...t
                      SearchCnd :uu...u[vv...v],...
                      KeyCnd    :ww...w
    RowCnd      :{HH...H}
    IfThenCnd   :{II...I}
 

Explanation

The RDAREA line is not displayed if information about retrieval processing is displayed on the temporary table.

If the INSERT statement with VALUES specified was executed, the Scan Type line is not displayed.

aa...a(aa...a)

Displays the name of the table (correlation name) being retrieved.

The correlation name is not displayed if it is not used. If retrieval processing is executed more than once, information about each retrieval processing is separated by this line.

bbbbbbbbbb(bb...b)

Displays in hexadecimal and in decimal (the latter in parentheses) the ID of the table being retrieved.

cc...c{AA...A}

Displays information if a work table is to be created after data retrieval from the base table. This information is not displayed if no work table is created.

If work table creation does not accompany a sort operation, the utility displays LIST. If work table creation accompanies a sort operation, the utility displays LIST(SORT).

d

Displays whether or not optimization information is to be collected by the optimizing information collection utility (pdgetcst):

Y: Optimization information collected

N: Optimization information not collected

ee...e

Displays the number of table rows used by HiRDB to determine the access path.

The default value depends on the SQL optimization mode:

• Cost-based optimization mode 1 or cost-based optimization mode 2 in a HiRDB single server configuration:
  10 million lines
  
• Cost-based optimization mode 1 in a HiRDB parallel server configuration:
  10 million lines number of back-end servers containing the table storage RDAREAs
  

By executing the optimizing information collection utility (pdgetcst), you can change the number of table rows to be used by HiRDB to determine the access path.

ff...f

Displays the number of columns into which a table is divided.

gg...g

Displays the type of table partitioning:

NON DIVISION: not partitioned

KEY RANGE: key range partitioning (with storage conditions specified)

PARTITION: key range partitioning (with boundary values specified)

MULTIDIM PARTITION: matrix partitioning (with boundary values specified)

MULTIDIM: Matrix partitioning including hash partitioning

FLEXIBLE HASH: flexible hash partitioning without using the rebalancing facility

FIX HASH: fix hash partitioning without using the rebalancing facility

RB FLEXIBLE HASH: flexible hash partitioning using the rebalancing facility

RB FIX HASH: fix hash partitioning using the rebalancing facility

hh...h

Displays the hash function during hash partitioning. This information is not displayed when hash partitioning is not performed. This information is not displayed for matrix partitioning that includes hash partitioning.

(ii...iRD/jj...jBES)

ii...i displays the number of table partitions (number of RDAREAs) and jj...j displays the number of back-end servers containing these table storage RDAREAs.

For a non-partitioned table, ii...i displays a value of 1. /jj...jBES is not displayed for a HiRDB single server configuration.

kkkk(kk...k)

Displays the ID of the table storage RDAREA in hexadecimal and in decimal (the latter in parentheses).

If the table is partitioned, the utility displays as many RDAREA IDs as there are table partitions. To obtain the name of the RDAREA associated with a given RDAREA ID, execute the pddbls command.

ll...l

ALL

This is displayed when all RDAREAs defining a table are to be retrieved.

RESTRICTED

This is displayed when, among the RDAREAs defining the table, only those RDAREAs that can potentially contain the target data based on the specified search conditions are to be retrieved.

mm...m

Displays the rebalancing status for a table using the rebalancing facility.

This line is not displayed for a table that does not use the rebalancing facility. For details about the rebalancing facility, see the HiRDB Version 9 System Operation Guide.

NORMAL

This is the normal status or the status when ALTER TABLE was used to add an RDAREA but the rebalancing utility (pdrbal) has not been executed yet.

ON REBALANCE

This is the status when the table is being rebalanced (from the start to the end of rebalance operation).

nn...n

Displays the retrieval method to be used.

For details about types of retrieval methods, see 17.5.6 Types of retrieval methods.

oo...o

Displays the index retrieval method if Group by Mode is IMPLICIT MIN-MAX INDEX

MIN

The utility searches a retrieval range of the index in ascending order and stops the retrieval at the point where a row satisfying the retrieval condition is found.

MAX

The utility searches a retrieval range of the index in descending order and stops the retrieval at the point where a row satisfying the retrieval condition is found.

MIN,MAX

The utility first searches the index in ascending order to find the MIN value, then searches the index in descending order to find the MAX value.

The retrieval time is about twice as much as when either MIN or MAX is specified in the set function.

Because MIN and MAX use mutually opposite search directions, their index search ranges based on the search condition are also opposite. If both MIN and MAX are used, the utility displays the search range only or MIN.

pp...p

If the SQL optimization specification is used for the index used, this item displays whether or not the specification takes effect. For details about the SQL optimization specification, see the manual HiRDB Version 9 SQL Reference.

AS SPECIFIED: Takes effect

SPECIFICATION IGNORED: Ignored

PARTIALLY IGNORED: Part of the specification is ignored

qq...q

Displays the name of the index used for retrieval.

If no index is used for retrieval, the utility does not display this line.

rrrrrrrrrr(rr...r)

Displays the index ID used for retrieval in hexadecimal and in decimal (the latter in parentheses).

ss...s

Displays a combination of index attributes:

Numeric character: Number of columns composing the index

G: Plug-in index

U: Unique key index

C: Cluster key index

D: Partition key index or plug-in index

d: Non-partitioning key index partitioned in the server

E: Index including an exceptional key value

M: Index containing repetition columns

P: Primary key index

tt...t

Indexes other than plug-in index or substructure index:

The utility displays the index component column information by separating each column by a comma and enclosing the entire information item in parentheses.

The index component column information consists of the search direction and the name of the index component column.

As the search direction, + means that the component column is searched in ascending order while - means that it is searched in descending order.

Example

Searching component column 1 (C1) in ascending order and component column 2 (C2) in descending order:

(+C1,-C2)

Substructure index:

The utility displays index component information by enclosing the entire information item in parentheses. The index component information consists of the following:

1. Name of column for which the XML type is defined
2. Search direction for the substructure path
   For the search direction, + means that the substructure path is searched in ascending order, while - means that it is searched in descending order.
   
3. Substructure paths composing the substructure index
4. Number of substructure paths constituting the substructure index
5. Whether USING UNIQUE TAG is specified in CREATE INDEX
   U means that USING UNIQUE TAG is specified.
   

Items 2 through 5 are enclosed in square brackets [ ]. Items 4 and 5 are enclosed in parentheses within the square brackets.

Example 1

Searching the substructure paths (/A/B/C) for which USING UNIQUE TAG is specified in the XML-type column (C1) in ascending order (when XML namespace declaration is not specified)

(C1[(1U)+/A/B/C])

Example 2

Searching the substructure paths (/A/B/C) for which USING UNIQUE TAG is specified in the XML-type column (C1) in ascending order (when the prefix specified in the XML namespace declaration is used)

(C1[(1U)+declare namespace aaa
="http://www.w3.org/XML/1998/namespace";/A/B/aaa:C])

Example 3

Searching the substructure paths (/A/B/C) for which USING UNIQUE TAG is specified in the XML-type column (C1) in ascending order (when the prefix (xml) defined in HiRDB is used)

(C1[(1U)+/A/B/xml:C])

Plug-in index:

The utility displays detailed information according to types of plug-in-provided functions. For details about the types of plug-in-provided functions, see 17.5.10 Types of plug-in-provided functions. For the function call, only the first argument is displayed.

Example 1

Searching component column 1 (C1) using a plug-in-provided function (WITHIN) with type SCAN TYPE:

WITHIN(C1,..)[SCAN TYPE]

Example 2

Searching component column 1 (C1) using a plug-in-provided function (CONTAINS) with type INDEX SCAN TYPE:

CONTAINS (C1,..)[INDEX SCAN TYPE]

Example 3

Searching component column 1 (C1) using a plug-in-provided function (SEARCHFEATUREDATA) with type FULL SCAN TYPE:

SEARCHFEATUREDATA (C1,..)[FULL SCAN TYPE]

uu...u[vv...v],..

Displays the type and narrowed range of search condition. For details about the search conditions, see 17.5.11 Search conditions.

If there is no search condition for a retrieval using an index, the utility displays NONE(FULL SCAN) during the index all-range search. If the Group by mode is IMPLICIT MIN-MAX INDEX, the utility displays NONE (when the search condition becomes true, the utility stops the search, in which case (FULL SCAN) is not displayed because this may not be the index all-range search).

The number of narrowed ranges may become 0 or 1 depending on the search condition. If there is no search condition for the first component column, but there are search conditions for the subsequent columns, the utility displays (FULL SCAN) following the narrowed range of search conditions.

ww...w

Displays the key condition. For details about key conditions, see 17.5.12 Key conditions.

If there is no key condition, the utility does not display this line.

xx...x

Displays SHARED for a retrieval target that is a shared table and TEMPORARY for a retrieval target that is a temporary table. Nothing is displayed if the retrieval target is not a shared or a temporary table.

 SCAN
  # Table Name   :aa...a(aa...a) bbbbbbbbbb(bb...b) (CC...C) cc...c{DD...DD}
    Cost         :d (ee...eROW)  {T-EE...E,I-FF...F,P-GG...G,AND-HH...H,OR-II...I}
    RDAREA       :ff...f-CLM gg...g(hh...h) (ii...iRD/jj...jBES) [kkkk(kk...k),...] LL...L
    Rebalance    :mm...m
    Scan Type    :nn...n  [oo...o]
    Index Name   :pp...p = qq...q rrrrrrrrrr(rr...r) (ss...s) tt...t BB...B{KK...K}
                         Scan Type :uu...u
                         SearchCnd :vv...v[ww...w],...
                         KeyCnd    :xx...x
                         RowCnd    :{HH...H}
                         IfThenCnd :{NN...N}
                  pp...p = qq...q rrrrrrrrrr(rr...r) (ss...s) tt...t BB...B{KK...K}
                         Scan Type :uu...u
                         SearchCnd :vv...v[ww...w],...
                         KeyCnd    :xx...x
                         RowCnd    :{HH...H}
                         IfThenCnd :{NN...N}
                  pp...p = [yy...y zz...z{JJ...J}] AA...A[yy...y zz...z{JJ...J}]
    RowCnd       :{MM...M}
    IfThenCnd    :{OO...O}
 

Explanation

The RDAREA line is not displayed if information about retrieval processing is displayed on the temporary table.

aa...a(aa...a)

Displays the name of the table (correlation name) being retrieved.

The correlation name is not displayed if it is not used. If retrieval processing is executed more than once, information about each retrieval processing is separated by this line.

bbbbbbbbbb(bb...b)

Displays in hexadecimal and in decimal (the latter in parentheses) the ID of the table being retrieved.

cc...c{DD...D}

Displays information if a work table is to be created after data retrieval from the base table. This information is not displayed if no work table is created.

If work table creation does not accompany a sort operation, the utility displays LIST. If work table creation accompanies a sort operation, the utility displays LIST(SORT).

d

Displays whether or not optimization information is to be collected by the optimizing information collection utility (pdgetcst):

Y: Optimization information collected

N: Optimization information not collected

ee...e

Displays the number of table rows used by HiRDB to determine the access path.

The default value depends on the SQL optimization mode:

• Cost-based optimization mode 1 or cost-based optimization mode 2 in a HiRDB single server configuration:
  10 million lines
  
• Cost-based optimization mode 1 in a HiRDB parallel server configuration:
  10 million lines number of back-end servers containing the table storage RDAREAs
  

By executing the optimizing information collection utility (pdgetcst), you can change the number of table rows to be used by HiRDB to determine the access path.

ff...f

Displays the number of columns into which a table is divided.

gg...g

Displays the type of table partitioning:

NON DIVISION: Not partitioned

KEY RANGE: Key range partitioning (with storage conditions specified)

PARTITION: Key range partitioning (with boundary values specified)

MULTIDIM PARTITION: Matrix partitioning (with boundary values specified)

MULTIDIM: Matrix partitioning including hash partitioning

FLEXIBLE HASH: Flexible hash partitioning without using the rebalancing facility

FIX HASH: Fix hash partitioning without using the rebalancing facility

RB FLEXIBLE HASH: Flexible hash partitioning using the rebalancing facility

RB FIX HASH: Fix hash partitioning using the rebalancing facility

hh...h

Displays the hash function during hash partitioning. This information is not displayed when hash partitioning is not performed. This information is not displayed for matrix partitioning that includes hash partitioning.

(ii...iRD/jj...jBES)

ii...i displays the number of table partitions (number of RDAREAs) and jj...j displays the number of back-end servers containing these table storage RDAREAs.

For a non-partitioned table, ii...i displays a value of 1. /jj...jBES is not displayed for a HiRDB single server configuration.

kkkk(kk...k)

Displays the ID of the table storage RDAREA in hexadecimal and in decimal (the latter in parentheses).

If the table is partitioned, the utility displays as many RDAREA IDs as there are table partitions. To obtain the name of the RDAREA associated with a given RDAREA ID, execute the pddbls command.

ll...l

ALL

This is displayed when all RDAREAs defining a table are to be retrieved.

RESTRICTED

This is displayed when, among the RDAREAs defining the table, only those RDAREAs that can potentially contain the target data based on the specified search conditions are to be retrieved.

mm...m

Displays the rebalancing status for a table using the rebalancing facility.

This line is not displayed for a table that does not use the rebalancing facility. For details about the rebalancing facility, see the HiRDB Version 9 System Operation Guide.

NORMAL

This is the normal status or the status when ALTER TABLE was used to add an RDAREA but the rebalancing utility (pdrbal) has not been executed yet.

ON REBALANCE

This is the status when the table is being rebalanced (from the start to the end of rebalance operation).

nn...n

Displays the retrieval method to be used.

For details about types of retrieval methods, see 17.5.6 Types of retrieval methods.

oo...o

If the SQL optimization specification is used for the index used, this item displays whether or not the specification takes effect. For details about the SQL optimization specification, see the manual HiRDB Version 9 SQL Reference.

AS SPECIFIED: Takes effect

SPECIFICATION IGNORED: Ignored

PARTIALLY IGNORED: Part of the specification is ignored

pp...p

Displays the work table number to be created for AND PLURAL INDEXES SCAN in the format LID(work-table-number).

qq...q

Displays the index name used to create a work table for AND PLURAL INDEXES SCAN or OR PLURAL INDEXES SCAN as many times as there are such indexes. For the work table created without using an index, the utility displays (NO USE).

rrrrrrrrrr(rr...r)

Displays the index ID used for retrieval in hexadecimal and in decimal (the latter in parentheses).

ss...s

Displays a combination of index attributes:

Numeric character: Number of columns composing the index

G: Plug-in index

U: Unique key index

C: Cluster key index

D: Partition key index or plug-in index

d: Non-partitioning key index partitioned in the server

E: Index including an exceptional key value

M: Index containing repetition columns

P: Primary key index

tt...t

Indexes other than plug-in index or substructure index:

The utility displays the index component column information by separating each column by a comma and enclosing the entire information item in parentheses.

The index component column information consists of the search direction and the name of the index component column.

As the search direction, + means that the component column is searched in ascending order while - means that it is searched in descending order.

Example

Searching component column 1 (C1) in the ascending order and component column 2 (C2) in the descending order:

(+C1,-C2)

Substructure index:

The utility displays index component information by enclosing the entire information item in parentheses. The index component information consists of the following information:

1. Name of column for which the XML type is defined
2. Search direction for the substructure path
   For the search direction, + means that the substructure path is searched in ascending order, while - means that it is searched in descending order.
   
3. Substructure paths composing the substructure index
4. Number of substructure paths constituting the substructure index
5. Whether USING UNIQUE TAG is specified in CREATE INDEX
   U means that USING UNIQUE TAG is specified.
   

Items 2 through 5 are enclosed in square brackets [ ]. Items 4 and 5 are enclosed in parentheses within the square brackets.

Example 1

Searching the substructure paths (/A/B/C) for which USING UNIQUE TAG is specified in the XML-type column (C1) in ascending order (when XML namespace declaration is not specified)

      (C1[(1U)+/A/B/C])

Example 2

Searching the substructure paths (/A/B/C) for which USING UNIQUE TAG is specified in the XML-type column (C1) in ascending order (when the prefix specified in the XML namespace declaration is used)

      (C1[(1U)+declare namespace aaa
      ="http://www.w3.org/XML/1998/namespace";/A/B/aaa:C])

Example 3

Searching the substructure paths (/A/B/C) for which USING UNIQUE TAG is specified in the XML-type column (C1) in ascending order (when the prefix (xml) defined in HiRDB is used)

      (C1[(1U)+/A/B/xml:C])

Plug-in index:

The utility displays detailed information according to types of plug-in-provided functions. For details about the types of plug-in-provided functions, see 17.5.10 Types of plug-in-provided functions. For the function call, only the first argument is displayed.

Example 1

Searching component column 1 (C1) using a plug-in-provided function (WITHIN) with type SCAN TYPE:

WITHIN(C1,..)[SCAN TYPE]

Example 2

Searching component column 1 (C1) using a plug-in-provided function (CONTAINS) with type INDEX SCAN TYPE:

CONTAINS (C1,..)[INDEX SCAN TYPE]

Example 3

Searching component column 1 (C1) using a plug-in-provided function (SEARCHFEATUREDATA) with type FULL SCAN TYPE:

SEARCHFEATUREDATA (C1,..)[FULL SCAN TYPE]

uu...u

Displays the search method for creating each work table.

For details about the types of retrieval methods, see 17.5.6 Types of retrieval methods.

vv...v[ww...w],..

Displays the type and narrowed range of search condition. For details about the search conditions, see 17.5.11 Search conditions.

If there is no search condition for a retrieval using an index, the utility displays NONE(FULL SCAN) as the type of search condition.

The number of narrowed ranges may become 0 or 1 depending on the search condition. If there is no search condition for the first component column, but there are search conditions for the subsequent columns, the utility displays (FULL SCAN) following the narrowed range of search condition.

xx...x

Displays the key condition. For details about key conditions, see 17.5.12 Key conditions.

If there is no key condition, the utility does not display this line.

pp...p = [yy...y zz...z{JJ...J}] AA...A [yy...y zz...z{JJ...J}]

Displays the order in which work tables are created during AND PLURAL INDEXES SCAN. If more than two indexes are used for retrieval, this information is displayed for each work table.

pp...p displays the work table number in the format LID(work-table-number).

AA...A displays AND, OR, or ANDNOT as the type of operation performed on the work tables.

yy...y displays the input work table for the operation in the format LID(work-table-number).

For zz...z, the utility displays LIST if work table creation does not accompany sorting; it displays LIST(SORT) if work table creation accompanies sorting.

Example: The following SQL statement was executed for a table with indexes IX1(C1) and IX2(C2) defined:

 
where C1='A'                   work table number: 1
  or C2=between 'a' and 'z'    work table number: 2
 

If Scantype is AND PLURAL INDEXES SCAN, then the following result is output:

: Scan Type :AND PLURAL INDEXES SCAN Index Name :LID(1)=IX1 (1) (+C1) Scan Type :INDEX SCAN SearchCnd :AT['A'] LID(2)=IX2 (1) (+C2) Scan Type :INDEX SCAN SearchCnd :RANGE(CS-CE) ['a','z'] LID(3)=[LID(1) LIST(SORT){..}] OR [LID(2) LIST(SORT){..}]

BB...B{KK...K}

If a work table is to be created after the table search separately from the work table for AND PLURAL INDEXES SCAN, the utility displays this information.

If the work table is to be created without sorting, the utility displays LIST for II...I. If work table creation involves sorting, the utility displays LIST(SORT) for II...I. If the work table is not to be created, the utility does not display this information.

CC...C

Displays SHARED for a retrieval target that is a shared table and TEMPORARY for a retrieval target that is a temporary table. Nothing is displayed if the retrieval target is not a shared or a temporary table.

 SCAN
  # Table Name   :aa...a(aa...a) bbbbbbbbbb(bb...b) (zz...z)
    Cost         :c (dd...dROW) {T-AA...A,I-BB...B,P-CC...C,AND-DD...D,OR-EE...E}
    RDAREA       :ee...e-CLM ff...f(gg...g) (hh...hRD/ii...iBES) [jjjj(jj...j),...] kk...k
    Rebalance    :ll...l
    Scan Type    :SELECT-APSL
          Table Name   :mm...m(mm...m) nnnnnnnnnn(nn...n)
          Column ID    :oooooo(oo...o)
          Predicate    :pp...p
          Threshold    :qq...q
       [1] rr...r  ................................................1
            Index Name :ss...s tttttttttt(tt...t) (uu...u) vv...v
                           SearchCnd :ww...w[xx...x],...
                           KeyCnd    :yy...y
                           RowCnd    :{FF...F}
       [2] rr...r  ................................................2
            Index Name :ss...s tttttttttt(tt...t) (uu...u) vv...v
                           SearchCnd :ww...w[xx...x],...
                           KeyCnd    :yy...y
                           RowCnd    :{FF...F}
    IfThenCnd    :{GG...G}
 

Explanation

Nos. 1 and 2 indicate the first and second candidates, respectively, for the retrieval method. For details about the retrieval method, see 17.5.6 Types of retrieval methods.

aa...a(aa...a)

Displays the name of the table (correlation name) being retrieved.

The correlation name is not displayed if it is not used. If retrieval processing is executed more than once, information about each retrieval processing is separated by this line.

bbbbbbbbbb(bb...b)

Displays in hexadecimal and in decimal (the latter in parentheses) the ID of the table being retrieved.

c

Displays whether or not optimization information is to be collected by the optimizing information collection utility (pdgetcst):

Y: Optimization information collected

N: Optimization information not collected

dd...d

Displays the number of table rows used by HiRDB to determine the access path.

The default value depends on the SQL optimization mode:

• Cost-based optimization mode 1 or cost-based optimization mode 2 in a HiRDB single server configuration:
  10 million lines
  
• Cost-based optimization mode 1 in a HiRDB parallel server configuration:
  10 million lines number of back-end servers containing the table storage RDAREAs
  

By executing the optimizing information collection utility (pdgetcst), you can change the number of table rows to be used by HiRDB to determine the access path.

ee...e

Displays the number of columns into which a table is divided.

ff...f

Displays the type of table partitioning:

NON DIVISION: Not partitioned

KEY RANGE: Key range partitioning (with storage conditions specified)

PARTITION: Key range partitioning (with boundary values specified)

MULTIDIM PARTITION: Matrix partitioning (with boundary values specified)

MULTIDIM: Matrix partitioning including hash partitioning

FLEXIBLE HASH: Flexible hash partitioning without using the rebalancing facility

FIX HASH: Fix hash partitioning without using the rebalancing facility

RB FLEXIBLE HASH: Flexible hash partitioning using the rebalancing facility

RB FIX HASH: Fix hash partitioning using the rebalancing facility

gg...g

Displays the hash function during hash partitioning. This information is not displayed when hash partitioning is not performed. This information is not displayed for matrix partitioning that includes hash partitioning.

(hh...hRD/ii...iBES)

hh...h displays the number of table partitions (number of RDAREAs) and ii...i displays the number of back-end servers containing these table storage RDAREAs.

For a non-partitioned table, hh...h displays a value of 1. /ii...iBES is not displayed for a HiRDB single server configuration.

jjjj(jj...j)

Displays the ID of the table storage RDAREA in hexadecimal and in decimal (the latter in parentheses).

If the table is partitioned, the utility displays as many RDAREA IDs as there are table partitions. To obtain the name of the RDAREA associated with a given RDAREA ID, execute the pddbls command.

kk...k

ALL

This is displayed when all RDAREAs defining a table are to be retrieved.

RESTRICTED

This is displayed when, among the RDAREAs defining the table, only those RDAREAs that can potentially contain the target data based on the specified search conditions are to be retrieved.

ll...l

Displays the rebalancing status for a table using the rebalancing facility.

This line is not displayed for a table that does not use the rebalancing facility. For details about the rebalancing facility, see the HiRDB Version 9 System Operation Guide.

NORMAL

This is the normal status or the status when ALTER TABLE was used to add an RDAREA but the rebalancing utility (pdrbal) has not been executed yet.

ON REBALANCE

This is the status when the table is being rebalanced (from the start to the end of rebalance operation).

mm...m(mm...m)

Displays the table name (correlation name) for the column used in the predicate that is subject to calculation of the SQL run-time hit rate when selecting an SQL object.

nnnnnnnnnn(nn...n)

Displays in hexadecimal and in decimal (the latter in parentheses) the table ID for the column used in the predicate that is subject to calculation of the SQL run-time hit rate when selecting an SQL object.

oooooo(oo...o)

Displays in hexadecimal and in decimal (the latter in parentheses) the column ID used in the predicate that is subject to calculation of the SQL run-time hit rate when selecting an SQL object.

pp...p

Displays the predicate subject to calculation of the SQL run-time hit rate when selecting an SQL object.

qq...q

Displays the reference hit rate for selecting an SQL object.

If the hit rate obtained from calculation is less than this value, the first candidate is used for the SQL object; if it is equal to or greater than this value, the second candidate is used for the SQL object.

rr...r

Displays the retrieval method to be used.

For details about the types of retrieval methods, see 17.5.6 Types of retrieval methods.

ss...s

Displays the name of the index used for retrieval.

If no index is used for retrieval, the utility does not display this line.

tttttttttt(tt...t)

Displays the index ID used for retrieval in hexadecimal and in decimal (the latter in parentheses).

uu...u

Displays a combination of index attributes:

Numeric character: Number of columns composing the index

G: Plug-in index

U: Unique key index

C: Cluster key index

D: Partition key index or plug-in index

d: Non-partitioning key index partitioned in the server

E: Index including an exceptional key value

M: Index containing repetition columns

P: Primary key index

vv...v

The utility displays the index component column information by separating each column by a comma and enclosing the entire information item in parentheses.

The index component column information consists of the search direction and the name of the index component column.

As the search direction, + means that the component column is searched in ascending order while - means that it is searched in descending order.

Example

Searching component column 1 (C1) in ascending order and component column 2 (C2) in descending order:

(+C1,-C2)

ww...w[xx...x],..

Displays the type and narrowed range of search condition. For details about the search conditions, see 17.5.11 Search conditions.

If there is no search condition for a retrieval using an index, the utility displays NONE(FULL SCAN) as the type of search condition.

The number of narrowed ranges may become 0 or 1 depending on the search condition. If there is no search condition for the first component column, but there are search conditions for the subsequent columns, the utility displays (FULL SCAN) following the narrowed range of search conditions.

yy...y

Displays the key condition. For details about key conditions, see 17.5.12 Key conditions.

If there is no key condition, the utility does not display this line.

zz...z

If a shared table is the search target, the utility displays SHARED; if not, the utility does not display this item.

 
 SCAN
  # Table Name   :aa...a(aa...a) bbbbbbbbbb(bb...b) (AA...A)
    Cost         :c (dd...dROW) {T-BB...B,I-CC...C,P-DD...D,AND-EE...E,OR-FF...F}
    RDAREA       :ee...e-CLM ff...f(gg...g) (hh...hRD/ii...iBES) [jjjj(jj...j),...] kk...k
    Rebalance    :ll...l
    Scan Type    :SELECT-APSL
          Table Name   :mm...m(mm...m) nnnnnnnnnn(nn...n)
          Column ID    :oooooo(oo...o)
          Predicate    :pp...p
          Threshold    :qq...q
       [1] rr...r (ss...s)  ........................................1
            Index Name :tt...t uuuuuuuuuu(uu...u) (vv...v) ww...w
                           SearchCnd :xx...x[yy...y],...
                           KeyCnd    :zz...z
                           RowCnd    :{GG...G}
                           IfThenCnd :{HH...H}
       [2] SELECT-APSL (ss...s)  ...................................2
             Table Name   :mm...m(mm...m) nnnnnnnnnn(nn...n)
             Column ID    :oooooo(oo...o)
             Predicate    :pp...p
             Threshold    :qq...q
          [1] rr...r  ..............................................3
            Index Name :tt...t uuuuuuuuuu(uu...u) (vv...v) ww...w
                           SearchCnd :xx...x[yy...y],...
                           KeyCnd    :zz...z
            RowCnd     :{GG...G}
            IfThenCnd  :{HH...H}
          [2] rr...r  ..............................................4
            RowCnd     :{GG...G}
            IfThenCnd  :{HH...H}
 

Explanation

1 indicates the table retrieval method used when the first candidate is selected for the join method.

2 indicates the table retrieval method used when the second candidate is selected for the join method (SELECT-APSL).

3 indicates the first candidate for the table retrieval method when the second candidate is selected for the join method.

4 indicates the second candidate for the table retrieval method when the second candidate is selected for the join method.

aa...a(aa...a)

Displays the name of the table (correlation name) being retrieved.

The correlation name is not displayed if it is not used. If retrieval processing is executed more than once, information about each retrieval processing is separated by this line.

bbbbbbbbbb(bb...b)

Displays in hexadecimal and in decimal (the latter in parentheses) the ID of the table being retrieved.

c

Displays whether or not optimization information is to be collected by the optimizing information collection utility (pdgetcst):

Y: Optimization information collected

N: Optimization information not collected

dd...d

Displays the number of table rows used by HiRDB to determine the access path.

The default value depends on the SQL optimization mode:

• Cost-based optimization mode 1 or cost-based optimization mode 2 in a HiRDB single server configuration:
  10 million lines
  
• Cost-based optimization mode 1 in a HiRDB parallel server configuration:
  10 million lines number of back-end servers containing the table storage RDAREAs
  

By executing the optimizing information collection utility (pdgetcst), you can change the number of table rows to be used by HiRDB to determine the access path.

ee...e

Displays the number of columns into which a table is divided.

ff...f

Displays the type of table partitioning:

NON DIVISION: Not partitioned

KEY RANGE: Key range partitioning (with storage conditions specified)

PARTITION: Key range partitioning (with boundary values specified)

MULTIDIM PARTITION: Matrix partitioning (with boundary values specified)

MULTIDIM: Matrix partitioning including hash partitioning

FLEXIBLE HASH: Flexible hash partitioning without using the rebalancing facility

FIX HASH: Fix hash partitioning without using the rebalancing facility

RB FLEXIBLE HASH: Flexible hash partitioning using the rebalancing facility

RB FIX HASH: Fix hash partitioning using the rebalancing facility

gg...g

Displays the hash function during hash partitioning. This information is not displayed when hash partitioning is not performed. This information is not displayed for matrix partitioning that includes hash partitioning.

(hh...hRD/ii...iBES)

hh...h displays the number of table partitions (number of RDAREAs) and ii...i displays the number of back-end servers containing these table storage RDAREAs.

For a non-partitioned table, hh...h displays a value of 1. /ii...iBES is not displayed for a HiRDB single server configuration.

jjjj(jj...j)

Displays the ID of the table storage RDAREA in hexadecimal and in decimal (the latter in parentheses).

If the table is partitioned, the utility displays as many RDAREA IDs as there are table partitions. To obtain the name of the RDAREA associated with a given RDAREA ID, execute the pddbls command.

kk...k

ALL

This is displayed when all RDAREAs defining a table are to be retrieved.

RESTRICTED

This is displayed when, among the RDAREAs defining the table, only those RDAREAs that can potentially contain the target data based on the specified search conditions are to be retrieved.

ll...l

Displays the rebalancing status for a table using the rebalancing facility.

This line is not displayed for a table that does not use the rebalancing facility. For details about the rebalancing facility, see the HiRDB Version 9 System Operation Guide.

NORMAL

This is the normal status or the status when ALTER TABLE was used to add an RDAREA but the rebalancing utility (pdrbal) has not been executed yet.

ON REBALANCE

This is the status when the table is being rebalanced (from the start to the end of the rebalance operation).

mm...m(mm...m)

Displays the table name (correlation name) for the column used in the predicate that is subject to calculation of the SQL run-time hit rate when selecting an SQL object.

nnnnnnnnnn(nn...n)

Displays in hexadecimal and in decimal (the latter in parentheses) the table ID for the column used in the predicate that is subject to calculation of the SQL run-time hit rate when selecting an SQL object.

oooooo(oo...o)

Displays in hexadecimal and in decimal (the latter in parentheses) the column ID used in the predicate that is subject to calculation of the SQL run-time hit rate when selecting an SQL object.

pp...p

Displays the predicate subject to calculation of the SQL run-time hit rate when selecting an SQL object.

qq...q

Displays the reference hit rate for selecting an SQL object.

If the hit rate obtained from calculation is less than this value, the first candidate is used for the SQL object; if it is equal to or greater than this value, the second candidate is used for the SQL object.

rr...r

Displays the retrieval method to be used.

For details about the types of retrieval methods, see 17.5.6 Types of retrieval methods.

ss...s

Displays the join method corresponding to the retrieval processing.

For details about the types of join methods, see 17.5.5 Types of join methods.

tt...t

Displays the name of the index used for retrieval.

If no index is used for retrieval, the utility does not display this line.

uuuuuuuuuu(uu...u)

Displays the index ID used for retrieval in hexadecimal and in decimal (the latter in parentheses).

vv...v

Displays a combination of index attributes:

Numeric character: Number of columns composing the index

G: Plug-in index

U: Unique key index

C: Cluster key index

D: Partition key index or plug-in index

E: Index including an exceptional key value

M: Index containing repetition columns

P: Primary key index

ww...w

The utility displays the index component column information by separating each column by a comma and enclosing the entire information in parentheses.

The index component column information consists of the search direction and the name of the index component column.

As the search direction, + means that the component column is searched in ascending order while - means that it is searched in descending order.

Example

Searching component column 1 (C1) in ascending order and component column 2 (C2) in descending order:

(+C1,-C2)

xx...x[yy...y],...

Displays the type and narrowed range of a search condition. For details about the search conditions, see 17.5.11 Search conditions.

If there is no search condition for a retrieval using an index, the utility displays NONE(FULL SCAN) as the type of search condition.

The number of narrowed ranges may become 0 or 1 depending on the search condition. If there is no search condition for the first component column, but there are search conditions for the subsequent columns, the utility displays (FULL SCAN) following the narrowed range of search conditions.

zz...z

Displays the key condition. For details about key conditions, see 17.5.12 Key conditions.

If there is no key condition, the utility does not display this line.

AA...A

If a shared table is the search target, the utility displays SHARED; if not, the utility does not display this item.

 
 SCAN
  # Table Name :aa...a(aa...a) bbbbbbbbbb(bb...b)  {AA...A}
    Cost       :(cc...cROW)  {T-BB...B}
    Scan Type  :dd...d
    RowCnd     :{CC...C}
    IfThenCnd  :{DD...D}
 

Explanation

aa...a(aa...a)

Displays the name of the view table (correlation name).

The correlation name is not displayed if it is not used.

bbbbbbbbbb(bb...b)

Displays in hexadecimal and in decimal (the latter in parentheses) the ID of the view table.

cc...c

Displays the number of table rows to be used by HiRDB to determine the access path.

dd...d

Displays LIST SCAN.

For details about LIST SCAN, see 17.5.6 Types of retrieval methods.

 
 SCAN
  # Table Name :aa...a(aa...a)  {AA...A}
    Cost       :(bb...bROW)  {T-BB...B}
    Scan Type  :cc...c
    RowCnd     :{CC...C}
    IfThenCnd  :{DD...D}
 

Explanation

aa...a(aa...a)

Displays the name of the WITH clause query (correlation name).

The correlation name is not displayed if it is not used.

bb...b

Displays the number of table rows to be used by HiRDB to determine the access path.

cc...c

Displays LIST SCAN.

For details about LIST SCAN, see 17.5.6 Types of retrieval methods.

 
 SCAN
  # Table Name :aa...a(aa...a) {AA...A}
    Cost       :(bb...bROW)  {T-BB...B}
    Scan Type  :cc...c
    RowCnd     :{CC...C}
    IfThenCnd  :{DD...D}
 

Explanation

aa...a(aa...a)

Displays (NO NAME) or (NO NAME)(correlation-name).

bb...b

Displays the number of table rows to be used by HiRDB to determine the access path.

cc...c

Displays LIST SCAN.

For details about LIST SCAN, see 17.5.6 Types of retrieval methods.

 
 SCAN
  # Table Name :aa...a  {AA...A}
    Cost       :(bb...bROW)  {T-BB...B}
    Scan Type  :cc...c
    RowCnd     :{CC...C}
    IfThenCnd  :{DD...D}
 

Explanation

aa...a

Displays the name of the work table created internally by HiRDB.

(DUMMY work-table-number) is the name of a temporary work table created internally by HiRDB. The work table number is a three-digit integer.

bb...b

Displays the number of table rows to be used by HiRDB to determine the access path.

cc...c

Displays LIST SCAN.

For details about LIST SCAN, see 17.5.6 Types of retrieval methods.

 
 --- SELECT-APSL ---
   Table Name    :aa...a(aa...a) bbbbbbbbbb(bb...b)
   Column ID     :cccccc(cc...c)
   Predicate     :dd...d
   Threshold     :ee...e
 [1]  ......................................................1
 Section No     :ff...f
    :
 [2]  ......................................................2
 Section No     :ff...f
    :
 

Explanation

1 and 2 indicate the first candidate and second candidate, respectively, for the access path. For details about the types of access paths, see 17.5.8 Types of access paths.

For a HiRDB single server configuration, the single server creates two SQL objects for the entire SQL statement without executing SELECT-APSL for each join and retrieval processing.

aa...a(aa...a)

Displays the table name (correlation name) for the column used in the predicate that is subject to calculation of the SQL run-time hit rate when selecting an SQL object.

bbbbbbbbbb(bb...b)

Displays in hexadecimal and in decimal (the latter in parentheses) the table ID for the column used in the predicate that is subject to calculation of the SQL run-time hit rate when selecting an SQL object.

cccccc(cc...c)

Displays in hexadecimal and in decimal (the latter in parentheses) the column ID for the column used in the predicate that is subject to calculation of the SQL run-time hit rate when selecting an SQL object.

dd...d

Displays the predicate subject to calculation of the SQL run-time hit rate when selecting an SQL object.

ee...e

Displays the reference hit rate for selecting an SQL object.

If the hit rate obtained from calculation is less than this value, the first candidate is used for the SQL object; if it is equal to or greater than this value, the second candidate is used for the SQL object.

ff...f

Displays the section number (assigned to each SQL statement).

For a dynamic SQL statement, this value is 1.