Nonstop Database, HiRDB Version 9 System Definition

[Contents][Index][Back][Next]

Appendix C.3 Formulas for determining size of view analysis information buffers (pd_view_def_cache_size)

Organization of this subsection
(1) Formulas for determining the size of the view analysis information buffer for a single view table
(2) LPTREE formulas
(3) Variables used in the formulas

(1) Formulas for determining the size of the view analysis information buffer for a single view table

The size of the view analysis information buffer for a single view table can be found from the following formulas. See (2) and (3) for details about the variables used in the formulas.

Formula for HiRDB in 32-bit mode

Size of the view analysis information buffer per view table (in kilobytes) =
[Figure]view-analysis-information-size-per-view-table [Figure] 1,024[Figure]

view-analysis-information-size-per-view-table (bytes) =
[Figure](1,024 + LCNST + 15) [Figure] 16[Figure] [Figure] 16
+ [Figure](LPTREE + 15) [Figure] 16[Figure] [Figure] 16
+ [Figure](28 [Figure] NINCC + 15) [Figure] 16[Figure] [Figure] 16
+ [Figure](28 [Figure] NINCP + 15) [Figure] 16[Figure] [Figure] 16
+ [Figure](24 + 512 [Figure] NTBL + 40 [Figure] (NTBL + NDTBL)
+ 128 [Figure] NCLM + 15) [Figure] 16[Figure] [Figure] 16
+ [Figure](16 [Figure] MAX([Figure]NADTL [Figure] 50[Figure], 1)
+ 200 [Figure] NADTL) [Figure] 16[Figure] [Figure] 16
+ [Figure](16 [Figure] MAX([Figure]NATTL [Figure] 50[Figure], 1)
+ 144 [Figure] NATTL) [Figure] 16[Figure] [Figure] 16
+ 16 + 16 [Figure] (3 + NINCC + NINCP)

Formula for HiRDB in 64-bit mode

Size of view analysis information buffer per view table (in kilobytes) =
[Figure]view-analysis-information-size-per-view-table [Figure] 1,024[Figure]

view-analysis-information-size-per-view-table (bytes) =
[Figure](1,600 + LCNST + 15) [Figure] 16[Figure] [Figure] 16
+ [Figure](LPTREE + 15) [Figure] 16[Figure] [Figure] 16
+ [Figure](40 [Figure] NINCC + 15) [Figure] 16[Figure] [Figure] 16
+ [Figure](40 [Figure] NINCP + 15) [Figure] 16[Figure] [Figure] 16
+ [Figure](32 + 768 [Figure] NTBL + 48 [Figure] (NTBL + NDTBL)
+ 184 [Figure] NCLM + 15) [Figure] 16[Figure] [Figure] 16
+ [Figure](20 [Figure] MAX([Figure]NADTL [Figure] 50[Figure], 1)
+ 224 [Figure] NADTL) [Figure] 16[Figure] [Figure] 16
+ [Figure](20 [Figure] MAX([Figure]NATTL [Figure] 50[Figure], 1)
+ 160 [Figure] NATTL) [Figure] 16[Figure] [Figure] 16
+ 16 + 24 [Figure] (3 + NINCC + NINCP)

Note
When a view definition includes a view table that generates an internal derived table, add the formula for the size of the view analysis information buffer for that table.
If the view table does not generate an internal derived table, add the value calculated for the SQL statement that is specified directly without going through its view table.

(2) LPTREE formulas

Determine LPTREE from the following formulas. See (3) for details about the variables used in the formulas.

Formula for HiRDB in 32-bit mode

LPTREE (bytes) =
276 [Figure] NQRY
+ 12 [Figure] (2 [Figure] NQRY + NSBQ + NSTOP)
+ 12 [Figure] (2 [Figure] NQRY + NSLST + NSLAS)
+ 12 [Figure] (4 [Figure] NTBL + 4 [Figure] NJTBL + 5 [Figure] NDTBL + NDCLM)
+ 12 [Figure] (NWHRC + NGPHV)
+ 12 [Figure] (NVLCM + NEXCM + NNLCM
+ 3 [Figure] NRANG + 3 [Figure] NLKCM + 3 [Figure] NSMCM + NETCM)
+ 12 [Figure] NRVCL
+ 12 [Figure] NTVCL
+ 12 [Figure] NLGEX
+ 12 [Figure] (NCNST + NSREG + NPRCS)
+ 12 [Figure] 4 [Figure] NCSCV
+ 12 [Figure] (NARTH + NCNCT)
+ 12 [Figure] NSFNC
+ 12 [Figure] NLBLD
+ 12 [Figure] (3 [Figure] NCLM)
+ 12 [Figure] (NCASE + NWHEN)
+ 12 [Figure] 2 [Figure] (NSCLF + 2 [Figure] NCSSP)
+ 12 [Figure] (2 [Figure] NPOS + 3 [Figure] NDTV)
+ 12 [Figure] (NEXTR + 2 [Figure] NSBST + 3 [Figure] NVALU + NBTEX + NCAST)
+ 12 [Figure] (4 [Figure] NFCSP + NFPRM)
+ 12 [Figure] (3 [Figure] NATTL + NATNM)
+ 28 [Figure] NOPTL + 20 [Figure] NOPTJ + 32 [Figure] NOPTT + 20 [Figure] NOPTIX
+ 8 [Figure] NSLAS
+ 120 [Figure] NJTBL
+ 480 [Figure] NCSET
+ 84 [Figure] NSPDT
+ 72 [Figure] (NSCLF + NCASE + NCSSP [Figure] 2)
+ (144 + 20 + 32) [Figure] NFCSP + (300 + 136) [Figure] NRTNL
+ 260 [Figure] (NEXRTN + NCLASS + NJAR) + 32 [Figure] NPVOW
+ 140 [Figure] NPLGL + 172 [Figure] NPPRL
+ (260 + 257 + 257) [Figure] NPLGL
+ 32 [Figure] NCUD
+ 512 [Figure] NDTBL
+ 40 [Figure] NDTBL
+ 128 [Figure] NDCLM
+ 182
+ 376
+ 1,384
+ 356 [Figure] NVCLM
+ 24 [Figure] NVCSC

Formula for HiRDB in 64-bit mode

LPTREE (bytes) =
496 [Figure] NQRY
+ 24 [Figure] (2 [Figure] NQRY + NSBQ + NSTOP)
+ 24 [Figure] (2 [Figure] NQRY + NSLST + NSLAS)
+ 24 [Figure] (4 [Figure] NTBL + 4 [Figure] NJTBL + 5 [Figure] NDTBL + NDCLM)
+ 24 [Figure] (NWHRC + NGPHV)
+ 24 [Figure] (NVLCM + NEXCM + NNLCM
+ 3 [Figure] NRANG + 3 [Figure] NLKCM + 3 [Figure] NSMCM + NETCM)
+ 24 [Figure] NRVCL
+ 24 [Figure] NTVCL
+ 24 [Figure] NLGEX
+ 24 [Figure] (NCNST + NSREG + NPRCS)
+ 24 [Figure] 4 [Figure] NCSCV
+ 24 [Figure] (NARTH + NCNCT)
+ 24 [Figure] NSFNC
+ 24 [Figure] NLBLD
+ 24 [Figure] (3 [Figure] NCLM)
+ 24 [Figure] (NCASE + NWHEN)
+ 24 [Figure] 2 [Figure] (NSCLF + 2 [Figure] NCSSP)
+ 24 [Figure] (2 [Figure] NPOS + 3 [Figure] NDTV)
+ 24 [Figure] (NEXTR + 2 [Figure] NSBST + 3 [Figure] NVALU + NBTEX + NCAST)
+ 24 [Figure] (4 [Figure] NFCSP + NFPRM)
+ 24 [Figure] (3 [Figure] NATTL + NATNM)
+ 56 [Figure] NOPTL + 40 [Figure] NOPTJ + 64 [Figure] NOPTT + 40 [Figure] NOPTIX
+ 16 [Figure] NSLAS
+ 208 [Figure] NJTBL
+ 480 [Figure] NCSET
+ 96 [Figure] NSPDT
+ 96 [Figure] (NSCLF + NCASE + NCSSP [Figure] 2)
+ (176 + 24 + 40) [Figure] NFCSP + (328 + 144) [Figure] NRTNL
+ 260 [Figure] (NEXRTN + NCLASS + NJAR) + 32 [Figure] NPVOW
+ 168 [Figure] NPLGL + 184 [Figure] NPPRL
+ (260 + 264 + 257) [Figure] NPLGL
+ 40 [Figure] NCUD
+ 768 [Figure] NDTBL
+ 48 [Figure] NDTBL
+ 184 [Figure] NDCLM
+ 226
+ 568
+ 1,496
+ 480 [Figure] NVCLM
+ 32 [Figure] NVCSC

(3) Variables used in the formulas

Variable name Explanation
LCNST Total size of the constants resulting from adding the following expressions:#1, #2, #3
  • Total size of constants in the view definition + number of character set conversions in the view definition
  • Total size after conversion of the target constants + number of numeric character conversions in the view definition
  • Total size after conversion of the target constants + number of scalar operations in the view definition
NINCC
  • When LCNST > 11,516 bytes:
    32-bit mode: [Figure](LCNST - 11,516) [Figure] 4,020[Figure]
    64-bit mode: [Figure](LCNST - 11,516) [Figure] 4,024[Figure]
  • When LCNST [Figure] 11,516 bytes:
    0
NINCP
  • When LTREE > 11,516 bytes:
    32-bit mode: [Figure](LTREE - 11,516) [Figure] 4,020[Figure]
    64-bit mode: [Figure](LTREE - 11,516) [Figure] 4,024[Figure]
  • When LCNST [Figure] 11,516 bytes:
    0
NQRY Number of queries in the view definition
NSBQ Number of subqueries in the view definition
NSTOP Number of set operations in the view definition
NSLST Total selection items for all query specifications in the view definition#4
NWHRC Number of WHERE clauses in the view definition
NGPHV Number of GROUP BY clauses in the view definition + Number of HAVING clauses in the view definition
NVLCM Number of comparison predicates in the view definition + Number of quantified predicates in the view definition
NEXCM Number of IN predicates in the view definition
NNLCM Number of NULL predicates in the view definition
NRANG Number of BETWEEN predicates in the view definition
NLKCM Number of LIKE and XLIKE predicates in the view definition
NSMCM Number of SIMILAR predicates in the view definition
NETCM Number of EXISTS predicates in the view definition
NRVCL Number of row value constructor elements in the view definition
NTVCL Number of row value constructor elements on the right side of IN predicates in the view definition
NLGEX Number of logical operators in the view definition
NCNST Total number of constants in the view definition + Total number of pattern characters in the view definition
NSREG Total number of USER, CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP specifications in the view definition
NPRCS Number of CURRENT TIMESTAMP decimal seconds precision specifications in the view definition
NCSCV Number of constants subject to character set conversion in the view definition#2
NARTH Total number of arithmetic, date, and time operations in the view definition
NCNCT Number of concatenation operations in the view definition
NLBLD Number of labeled durations in the view definition
NSFNC Number of set functions in the view definition
NSCLF Number of system built-in scalar functions in the view definition
NFCSP Number of function invocations or scalar functions in the view definition
NCLM Total number of columns in the view definition
NCASE Number of CASE expressions in the view definition + Number of NULLIF CASE abbreviations in the view definition
NWHEN Number of WHEN CASE expressions in the view definition + 1
NCSSP Number of CAST specifications in the view definition
NPOS Number of system built-in POSITION scalar functions in the view definition
NDTV Number of system built-in scalar functions (DATE, TIME, TIMESTAMP, or VARCHAR_FORMAT) in the view definition
NEXTR Number of system built-in scalar functions (YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND) in the view definition
NSBST Number of system built-in SUBSTR scalar functions in the view definition
NVALU Number of system built-in VALUE scalar functions in the view definition + Number of COALESCE CASE abbreviations in the view definition
NBTEX Number of system built-in BIT_AND_TEST scalar functions in the view definition
NCAST Number of system built-in scalar functions (INTEGER or DECIMAL) in the view definition
NOPTL Number of SQL optimization specifications in the view definition
NOPTJ Number of joined format SQL optimization specifications in the view definition
NOPTT Number of table SQL optimization specifications in the view definition
NOPTIX Number of index SQL optimization specifications in the view definition
NSLAS Number of select expression AS clauses in the view definition
NJTBL Number of joined tables in the view definition
NCSET Number of types of character sets that can be specified by HiRDB + 1#5
NSPDT Number of AS data-type specifications in the view definition
NFPRM Total number of function invocation arguments and system-defined scalar function arguments in the view definition
NRTNL Total number of functions potentially invoked by function invocations and functions potentially invoked by system-defined scalar functions in the view definition
NEXRTN Total number of external routines among functions that could potentially be invoked in the view definition
NCLASS Total number of JAVA function classes in the view definition
NJAR Total number of JAVA function JAVA archives in the view definition
NPVOW Total number of public view owner names in the view definition
NDTBL Number of FROM clause derived tables in the view definition + Number of view tables that generate internal derived tables in the view definition
NTBL
  • When the total number of tables in the view definition is 15 or fewer and the total of the number of correlation names in the view definition and the NDTBL value is 15 or less:
    15
  • When the total number of tables in the view definition is 16 or more and the total of the number of correlation names in the view definition and the NDTBL value is 16 or more:
    Max(Total number of tables in the view definition, Number of correlation names in the view definition + NDTBL value)
NDCLM Total number of columns of FROM clause derived tables in the view definition + Total number of columns of view tables that generate internal derived tables in the view definition
NVCLM Number of columns derived by the view definition
NVCSC Number of columns derived by the view definition that specify a character set other than the default character set
NADTL Number of all abstract data types in the view definition, including those in inheritance relationships#6
NATTL Number of component specifications in the view definition
NATNM Number of attribute names of all component specifications in the view definition
NPLGL Number of functions provided by plug-ins in the view definition
NPPRL Number of parameters of functions provided by plug-ins in the view definition
NCUD Number of abstract data type columns in the view definition

#1
Use the following to calculate the sizes of the various types of constants:
Constant type Size (bytes) Maximum size required by boundary adjustment (bytes)
Integer constant 4 7
Decimal constant [Figure](decimal constant precision + 1) [Figure] 2[Figure] [Figure](decimal constant precision + 1) [Figure] 2[Figure] + 7
Floating-point numeric literal 8 15
Character string constant 2 + character string constant character count 2 + character string constant character count + 3
Hexadecimal constant 4 + (hexadecimal constant character string length) [Figure] 2 4 + (hexadecimal constant character string length) [Figure] 2 + 3
National character string literal 2 + national character string literal character count [Figure] 2 2 + national character string literal character count [Figure] 2 + 3
Mixed character string literal 2 + mixed character string literal single-byte character count + mixed character string literal double-byte character count [Figure] 2 2 + mixed character string literal single-byte character count + mixed character string literal double-byte character count [Figure] 2 + 3

#2
The following constants are subject to character set conversion:
  • Character string constants that are compared to character set items.
  • Character string constants for which a character set was specified by a CAST specification in the post-conversion data type.

#3
The following constants are subject to numeric character conversion:
  • Character string constants that are compared to numeric attribute items.
  • Numeric constants specified in an operand of a concatenation operation.
  • Character string constants specified in the operand of an arithmetic operation.

#4
When * is specified in a selection item, add the total column count for all tables specified in FROM clauses. When table-name.* is specified in a select expression, add the column count of that table.

#5
The only character set that can be specified in HiRDB is EBCDIK.

#6
For details about the inheritance relationships of abstract data types provided by plug-ins, see the applicable manuals for the specific plug-ins.

[Contents][Back][Next]

[Trademarks]

All Rights Reserved. Copyright (C) 2014, Hitachi, Ltd.