Performance Properties

AGER_WAIT_MAXIMUM (Unit: microsecond)

Data Type

Unsigned Integer

Default Value

1000000

Attributes

Read-Only, Single Value

Range

[0, 2³²-1]

Description

This property specifies the maximum waiting time, in microseconds, of the garbage collector (also known as the "Ager").

This property is intended to prevent deterioration in performance (especially in HP systems) resulting from excessive "sleep" system calls by threads related to the garbage collector while the garbage collector is asleep. This parameter allows the maximum sleep time of the garbage collector to be suitably regulated while the server is running.

AGER_WAIT_MINIMUM (Unit: microsecond)

Data Type

Unsigned Integer

Default Value

200000

Attributes

Read-Only, Single Value

Range

[0, 2³²-1]

Description

This property specifies the minimum waiting time, in microseconds, of the garbage collector (also known as the "Ager").

This property is intended to prevent deterioration in performance (especially in HP systems) resulting from excessive "sleep" system calls by threads related to the garbage collector while the garbage collector is asleep. This parameter allows the minimum sleep time of the garbage collector to be suitably regulated while the server is running.

BUFFER_VICTIM_SEARCH_INTERVAL (Unit: millisecond)

Data Type

Unsigned Integer

Default Value

3000

Attributes

Read-Write, Single Value

Range

[0, 86400000]

Description

After a search for a replacement BCB ("victim") fails, this property specifies the amount of time, in milliseconds, to wait for the flusher to flush dirty buffer frames before searching again for a victim.

If, as the result of the subsequent search, a replacement BCB still cannot be found, the value of VICTIM_SEARCH_WARP in the V$BUFFPOOL_STAT performance view is increased.

BUFFER_VICTIM_SEARCH_PCT(Unit: Percentage)

Data Type

Unsigned Integer

Default Value

5

Attributes

Read-Write, Single Value

Range

[0, 100]

Description

This property sets how much to explore when searching for replacement buffers in an LRU list. In other words, this property indicates the percentage of an LRU list that is searched, with the least recently accessed records searched first. A value of 100 indicates that the entire list is searched.

CHECKPOINT_BULK_SYNC_PAGE_COUNT (Unit: page count)

Data Type

Unsigned Integer

Default Value

3200

Attributes

Read-Write, Single Value

Range

[0, 2³²-1]

Description

When performing checkpointing between memory and disk tables, this property sets the number of pages that are synchronized at one time.

This property can be changed using the ALTER SYSTEM statement while Altibase is running.

CHECKPOINT_BULK_WRITE_PAGE_COUNT (Unit: count)

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Write, Single Value

Range

[0, 2³²-1]

Description

When checkpointing, a given number of dirty pages can be separated and saved to disk. When this happens, this property specifies the number of dirty pages that are saved to disk at one time. If this is set to 0, all of the dirty pages are saved to the disk database at one time.

This property can be changed using the ALTER SYSTEM statement while Altibase is running.

CHECKPOINT_BULK_WRITE_SLEEP_SEC (Unit: second)

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Write, Single Value

Range

[0, 2592000]

Description

This property specifies the amount of time to wait (in seconds) after saving dirty pages to disk if the value of CHECKPOINT_BULK_WRITE_PAGE_COUNT is not set to 0.

This property can be changed using the ALTER SYSTEM statement while Altibase is running.

CHECKPOINT_BULK_WRITE_SLEEP_USEC (Unit: microsecond)

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Write, Single Value

Range

[0, 60000000]

Description

This property specifies the amount of time to wait (in microseconds) after saving dirty pages to disk if the value of CHECKPOINT_BULK_WRITE_PAGE_COUNT is not set to 0.

This property can be changed using the ALTER SYSTEM statement while Altibase is running.

CHECKPOINT_FLUSH_COUNT (Unit: frame count)

Data Type

Unsigned Integer

Default Value

64

Attributes

Read-Write, Single Value

Range

[1, 2⁶⁴-1]

Description

This property specifies the number of buffer pages (frames) that can be flushed in one flusher cycle when checkpoint flushing.

CHECKPOINT_FLUSH_MAX_GAP (Unit: log files count)

Data Type

Unsigned Integer

Default Value

10

Attributes

Read-Write, Single Value

Range

[0, 2³²-1]

Description

This is one of the conditions for conducting checkpoint processing. Checkpoint flushing is performed when the number of logfiles between the most recent LSN (Log Sequence Number) and the earliest LSN reaches this value.

This property influences the recovery time when the server is restarted. Greater values mean that checkpoint processing is performed less often, and that it takes more time for the server to recover when restarted.

CHECKPOINT_FLUSH_MAX_WAIT_SEC (Unit: second)

Data Type

Unsigned Integer

Default Value

10

Attributes

Read-Write, Single Value

Range

[0, 2³²-1]

Description

This is one of the conditions for conducting checkpoint processing. Checkpoint flushing is performed when the number of seconds specified by this property has passed since the most recent flush.

CM_BUFFER_MAX_PENDING_LIST

Data Type

Unsigned Integer

Default Value

512

Attributes

Read-Only, Single Value

Range

[1, 512]

Description

In order to prevent sudden increases in memory usage, this property specifies the maximum number of communication buffer blocks that can be allocated in one session.

CM_DISPATCHER_SOCK_POLL_TYPE

Data Type

Unsigned Integer

Default Value

3

Attribute

Read-Only, Single Value

Range

[1, 2, 3]

Description

This property enables the Altibase dispatcher to select the system call for detecting sockets. If there are more than 1,000 concurrent client connections, using the POLL() system call helps improve performance.

• 1: SELECT() system call
• 2: POLL() system call; available for use only on supported operating systems
• 3: EPOLL() system call; available for use only on supported operating systems

DATABASE_IO_TYPE

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Only, Single Value

Range

[0, 1]

Description

Altibase provides two disk I/O methods related to database files:

• Direct I/O

• Buffered I/O

To use direct I/O, set this parameter to 1, or to use buffered I/O, set it to 0.

The advantage of Direct I/O is that it reduces CPU resources during the occurrence of Disk I/O. On the other hand, since buffered I/O uses the read-ahead and asynchronous write techniques, it does not necessarily incur disk access every time disk I/O is requested. This means that buffered I/O can realize better performance than direct I/O from the aspect of client applications; however, buffered I/O consumes more CPU resources than direct I/O.

DATAFILE_WRITE_UNIT_SIZE (Unit: count)

Data Type

Unsigned Long

Default Value

1024

Attributes

Read-Write, Single Value

Range

[1, 1024]

Description

This property specifies the default data unit size when a data file is created.

This property can be changed using the ALTER SYSTEM statement while Altibase is running.

DB_FILE_MULTIPAGE_READ_COUNT

Data Type

Unsigned Integer

Default Value

8

Attributes

Read-Write, Single Value

Range

[1, 128]

Description

This property determines the number of pages to read at a time when a full scan is performed on a disk table.

At this time, if a disk table's extent size, that is, the number of pages in the extent, is a multiple of (and greater than) the value specified here, Multiple Page Read (MPR) is conducted. However, if the extent size is not a multiple of, or is smaller than, the value specified here, Single Page Read (SPR) is conducted.

This property can be changed using the ALTER SYSTEM statement while Altibase is running.

DEDICATED_THREAD_CHECK_INTERVAL (Unit: second)

Data Type

Unsigned Integer

Default Value

3600

Attributes

Read-Only, Single Value

Range

[0, 2⁶⁴-1]

Description

This property specifies the time interval at which to terminate service threads in dedicated thread mode. This property is only valid if the value of the DEDICATED_THREAD_MODE property is 1.

Idle service threads are terminated at the time interval set in this property; however, the number of service threads specified in the DEDICATED_THREAD_INIT_COUNT property is maintained.

Service threads are not terminated if this property is set to 0.

DEDICATED_THREAD_INIT_COUNT

Data Type

Unsigned Integer

Default Value

1

Attributes

Read-Only, Single Value

Range

[0, 65535]

Description

This property specifies the number of initial service threads to be created in dedicated thread mode. This property is only valid if the value of the DEDICATED_THREAD_MODE property is 1.

The value of the DEDICATED_THREAD_INIT_COUNT property must be smaller than the value set in the DEDICATED_THREAD_MAX_COUNT property.

DEDICATED_THREAD_MAX_COUNT

Data Type

Unsigned Integer

Default Value

1

Attributes

Read-Only, Single Value

Range

[0, 65535]

Description

This property specifies the maximum number of service threads to be created in dedicated thread mode. This property is only valid if the value of the DEDICATED_THREAD_MODE property is 1.

If all of the initial service threads created are allocated to clients, the number of service threads can increase up to the value of the DEDICATED_THREAD_MAX_COUNT property.

The value of the DEDICATED_THREAD_MAX_COUNT property must be smaller than the value set in the MAX_CLIENT property.

DEDICATED_THREAD_MODE

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Only, Single Value

Range

[0, 1]

Description

This property specifies whether or not service threads are to be run in dedicated thread mode for clients.

• 1: Service threads run in dedicated mode
• 0: Service threads run in multiplexing mode

DEFAULT_FLUSHER_WAIT_SEC (Unit: second)

Data Type

Unsigned Integer

Default Value

1

Attributes

Read-Write, Single Value

Range

[1, 2³²-1]

Description

This property sets the minimum number of seconds that the flusher waits. As long as there are no special conditions, flushing is always conducted after waiting this amount of time. The wait time is repeatedly incremented 1 second at a time if the flusher is removed from the queue or doesn't perform any flushing work.

DELAYED_FLUSH_LIST_PCT (Unit: percentage)

Data Type

Unsigned Integer

Default Value

30

Attributes

Read-Write, Single Value

Range

[0, 100]

Description

This property specifies the maximum rate of the delayed flush list. This property can be changed using the ALTER SYSTEM statement while the server is running.

DELAYED_FLUSH_PROTECTION_TIME_MSEC (Unit: millisecond)

Data Type

Unsigned Integer

Default Value

100

Attributes

Read-Write, Single Value

Range

[0, 100000]

Description

This property indicates the reference time to determine the last used page. If the last used time is below the set value, it is judged to be the last used page. This property can be changed using the ALTER SYSTEM statement while the server is running.

DIRECT_IO_ENABLED

Data Types

Unsigned Integer

Default Value

1

Attributes

Read-Only, Single Value

Range

[0, 1]

Description

This property indicates whether database I/O can be performed via direct disk access.

0: Disable

1: Enable

DISK_INDEX_BUILD_MERGE_PAGE_COUNT (Unit: page count)

Data Type

Unsigned Integer

Default Value

128

Attributes

Read-Write, Single Value

Range

[2, 2³²-1]

Description

When a disk index is created, if the keys extracted from data cannot all be sorted in memory at the same time, this property specifies the number of pages to be used for external sorting.

This property can be changed using the ALTER SYSTEM statement during system operation.

EXECUTE_STMT_MEMORY_MAXIMUM (Unit: byte)

Data Type

Unsigned Long

Default Value

1G

Attributes

Read-Write, Single Value

Range

[1024*1024, 2⁶⁴-1]

Description

This property limits the number of bytes of memory that can be used to execute a single query statement.

This property can be changed using the ALTER SYSTEM statement during system operation.

EXECUTOR_FAST_SIMPLE_QUERY

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Write, Single Value

Range

[0, 2]

Description

This property prints a simple query plan; it optimizes the execution plan of simple DML statements or statements that use nested loop joins for faster performance.

The value of this property can be changed using the ALTER SYSTEM statement while Altibase is running.

0: Uses the SIMPLE QUERY PLAN.

1: Uses the SIMPLE QUERY PLAN on a memory table.

2: Uses the SIMPLE QUERY PLAN on a partition table.

FAST_START_IO_TARGET (Unit: page count)

Data Type

Unsigned Long

Default Value

10000

Attributes

Read-Write, Single Value

Range

[1, 2⁶⁴-1]

Description

This property indicates the number of redo pages that the server reads when performing recovery after being restarted.

When the flusher performs checkpoint flushing while the system is running, if the number of dirty pages remaining in the buffer is greater than the value saved in this property, the oldest dirty pages, equal in number to the difference there between, are written to disk.

This value is important in determining the recovery time when the server is restarted. Because the number of pages to be flushed increases as this value is decreased, the recovery time when the server is restarted can be reduced.

The value of this property can be changed using the ALTER SYSTEM statement while the server is running.

FAST_START_LOGFILE_TARGET (Unit: log file count)

Data Type

Unsigned Integer

Default Value

10

Attributes

Read-Write, Single Value

Range

[1, 2³²-1]

Description

This property indicates the number of log files that the server reads when performing recovery after being restarted.

When the flusher performs checkpoint flushing while the server is running, if the difference between the LogFileNo of the LSN of the current log and the LogFileNo of the LSN of one of the dirty pages in the checkpoint list is greater than the value specified in this property, that page is flushed.

This value is important in determining the recovery time when the server is restarted. Because the number of pages to be flushed increases as this value is decreased, the recovery time when the server is restarted can be reduced.

The value of this property can be changed using the ALTER SYSTEM statement while the server is running.

FAST_UNLOCK_LOG_ALLOC_MUTEX

Data Type

Unsigned Integer

Default Value

1

Attributes

Read-Only, Single Value

Range

[0, 1]

Description

This property specifies whether or not the log buffer can be simultaneously accessed by multiple threads of the server. If the value of this property is set to 0, multiple server threads can sequentially access the log buffer; if the value of this property is set to 1, server threads can simultaneously access the log buffer.

0: Simultaneous access is disabled.

1: Simultaneous access is enabled.

HASH_AREA_SIZE (Unit: byte)

Data Type

Unsigned Long

Default Value

4MB

Attributes

Read-Write, Single Value

Range

[3MB, 2⁶⁴-1]

Description

This property specifies the memory size, in bytes, of each temporary table that the server uses for hashing operations.

The value of this property can be changed using the ALTER SYSTEM statement while Altibase is running.

HASH_JOIN_MEM_TEMP_AUTO_BUCKET_COUNT_DISABLE

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Write, Single Value

Range

[0, 1]

Description

Sets the necessary number of buckets for memory hash temporary tables used in hash joins.

• 0 : The number of actual records inserted into a hash table.
• 1 : The number of buckets predicted by the query optimizer or set with the /*+ HASH BUCKET COUNT () */ hint.

If the value for the HASH_JOIN_MEM_TEMP_PARTITIONING_DISABLE property is 0 (partitioning method), it is efficient to divide as many sections as there are buckets.

If the value for the HASH_JOIN_MEM_TEMP_PARTITIONING_DISABLE property is 1 (bucket method), the number of buckets is predicted by the query optimizer or set by a hint.

The value of the HASH_JOIN_MEM_TEMP_PARTITIONING_DISABLE property can be altered using the ALTER SYSTEM statement while Altibase is running.

HASH_JOIN_MEM_TEMP_PARTITIONING_DISABLE

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Write, Single Value

Range

[0, 1]

Description

Sets how to insert records into and scan memory hash temporary tables that are used in hash joins.

0 : Partitioning method

1 : Bucket method

The partitioning method scans the records by partitions; the number of partitions is automatically set according to the number of records inserted into the temporary space. The bucket method stores records into buckets in list format and scans records by buckets. The partitioning method consumes additional memory; however, it inserts and scans faster than the bucket method when handling bulk databases.

The value of the HASH_JOIN_MEM_TEMP_PARTITIONING_DISABLE property can be altered using the ALTER SYSTEM statement while Altibase is running.

HIGH_FLUSH_PCT (Unit: percentage)

Data Type

Unsigned Integer

Default Value

5

Attributes

Read-Write, Single Value

Range

[0, 100]

Description

When the flusher is not in a waiting state, if the flush list is longer than the percentage of the total buffer size specified here, replacement flushing occurs. At this time, all updated buffers in the flush list are flushed sequentially without waiting.

HOT_LIST_PCT (Unit: percentage)

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Write, Single Value

Default Value

[0, 100]

Description

This property specifies the percentage of an LRU list that is a hot area. The value of this property can be changed while the server is running.

HOT_TOUCH_CNT

Data Type

Unsigned Integer

Default Value

2

Attributes

Read-Write, Single Value

Range

[1, 2³²-1]

Description

This property defines what constitutes a hot buffer in terms of the number of times the buffer is accessed. If the buffer is accessed more times than the value specified for this property, the buffer is considered hot. Hot buffers are moved to the hot list when replacement buffer searching is performed.

INDEX_BUILD_THREAD_COUNT

Data Type

Unsigned Integer

Default Value

The number of physical core

Attributes

Read-Write, Single Value

Range

[1, 512]

Description

This property indicates the number of index-building threads that are created when an index is rebuilt at runtime.

If this property is commented out, the default number of parallel threads generated by the system is equal to the number of physical core.

INDEX_INITRANS (Unit: count)

Data Type

Unsigned Integer

Default Value

8

Attributes

Read-Only, Single Value

Range

[0, 50]

Description

This property indicates the initial number of TTS (Touched Transaction Slots) in an index page.

INDEX_MAXTRANS (Unit: count)

Data Type

Unsigned Integer

Range

50

Attributes

Read-Only, Single Value

Range

[0, 50]

Description

This property indicates the maximum number of TTS (Touched Transaction Slots) in an index page.

LFG_GROUP_COMMIT_INTERVAL_USEC (Unit: microsecond)

Data Type

Unsigned Integer

Default Value

1000

Attributes

Read-Only, Single Value

Range

[0, 2³²-1]

Description

This property pertains to group commit.

A record is kept of the last time point at which disk I/O was performed for writing logs to disk to commit transactions. Logs are written to disk after the number of microseconds specified in this property has passed since that time point.

In this way, multiple transactions can be collectively committed to disk at the same time, and the required disk I/O can all be performed at once.

LFG_GROUP_COMMIT_RETRY_USEC (Unit: microsecond)

Data Type

Unsigned Integer

Default Value

100

Attributes

Read-Only, Single Value

Range

[0, 60000000]

Description

This property pertains to group commit.

If the amount of time specified in LFG_GROUP_COMMIT_INTERVAL_USEC has not passed since the last time disk I/O was performed to record logs, a transaction to be committed waits for the number of microseconds specified in this property and then checks again whether sufficient time has passed to perform disk I/O.

LFG_GROUP_COMMIT_UPDATE_TX_COUNT

Data Type

Unsigned Integer

Default Value

80

Attributes

Read-Only, Single Value

Range

[0, 2³²-1]

Description

This property pertains to group commit.

When the number of uncommitted database UPDATE transactions (note: this can be checked by querying the UPDATE_TX_COUNT column in the V$LFG performance view) is greater than the value specified in this property, group commit is performed. If this property is set to 0, group commit is disabled.

LOB_CACHE_THRESHOLD (Unit: byte)

Data Type

Unsigned Integer

Default Value

8192

Attributes

Read-Write, Single Value

Range

[0, 524288]

Description

This property specifies the maximum size, in bytes, for LOB data capable of being stored on the client LOB cache. If a value larger than 0 is specified for this property, LOB data that does not exceed the specified value is temporarily stored on the client; if 0 is specified for this property, LOB data is not temporarily stored on the client.

If the threshold is appropriately adjusted with the use of this property, LOB data is stored on the client LOB cache and Bulk-select speed is improved.

This property can be changed using either the ALTER SYSTEM or ALTER SESSION statement while Altibase is running.

LOCK_ESCALATION_MEMORY_SIZE (Unit: byte)

Data Type

Unsigned Integer

Default Value

100MB

Attributes

Read-Write, Single Value

Range

[0, 1000MB]

Description

This property is used to prevent abnormal increases in memory usage due to versioning when large-volume UPDATE batch tasks are performed on memory tables. If the size of logs created by a DML statement increases beyond the value specified in this property, so-called "In-place update"¹ is performed without versioning in order to prevent increased memory usage.

When using versioning while updating records, an X lock is placed on the record, and an IX lock is placed on the table. However, when in-place update is performed, an X lock, that is, an exclusive lock, is placed on the table. Therefore, care must be taken when setting this value as it can degrade the scalability of the corresponding table if the value is set too low. This property value can be changed using the ALTER SYSTEM statement while A is running.

This property can be changed using the ALTER SYSTEM statement while Altibase is running.

LOG_IO_TYPE

Data Type

Unsigned Integer

Default Value

1

Attributes

Read-Only, Single Value

Range

[0, 1]

Description

This indicates the I/O mode used to write logs.

0: buffered I/O

1: direct I/O

LOG_CREATE_METHOD

Data Type

Unsigned Integer

Default Value

• 0 (on HP-UX or AIX Environment)
• 1 (on Linux Environment)

Attributes

Read-Only, Single Value

Range

[0, 1]

Description

This property selects the system call used to create the log file.

0 : write() system call

1: fallocate() system call (Only available on Linux)

LOW_FLUSH_PCT (Unit: percentage)

Data Type

Unsigned Integer

Default Value

1

Attributes

Read-Write, Single Value

Attributes

[0, 100]

Description

If the length of the flush list becomes equal to or greater than the percentage of the total buffer size specified by this value, replacement flushing occurs. At this time, all update buffers in the flush list are flushed.

LOW_PREPARE_PCT (Unit: percentage)

Data Type

Unsigned Integer

Default Value

1

Attributes

Read-Write, Single Value

Range

[0, 100]

Description

When the flusher awakes from a waiting state, if the length of the Prepare list is less than or equal to the percentage of the total buffer size specified by this value, replacement flushing occurs. At this time, all update buffers in the flush list are flushed.

MATHEMATICS_TEMP_MEMORY_MAXIMUM (Unit: byte)

Data Type

Unsigned Long

Default Value

0

Attributes

Read-Write, Single Value

Range

[0, 2⁶⁴-1]

Description

This property limits the amount of memory in MATHEMATICS TEMP used by analytic functions on the system. (Analytic functions: listagg, percentile_cont, percentile_disk, ...) An error occurs if the amount of used memory is equal or greater than MATHEMATICS_TEMP_MEMORY_MAXIMUM. If the property value is 0, the memory usage is not checked.

MAX_FLUSHER_WAIT_SEC (Unit: second)

Data Type

Unsigned Integer

Default Value

10

Attributes

Read-Write, Single Value

Range

[1, 2³²-1]

Description

This property specifies the maximum number of seconds that the flusher waits. The flusher wait time can increase depending on the frequency with which a task is conducted, but cannot exceed this value.

MEM_INDEX_KEY_REDISTRIBUTION

Data Type

Unsigned Integer

Default Value

1

Attributes

Read-Only, Single Value

Range

[0, 1]

Description

The MEM_INDEX_KEY_REDISTRIBUTION property redistributes memory index keys.

This property is highly efficient to use for inserting numerous reverse index keys, a lot of leaf node splits, or when the index range is much larger than that of the database.

0: The memory index keys are not redistributed.

1: The memory index keys are redistributed (Default value).

The value of this property can be changed using the ALTER SYSTEM statement while Altibase is running.

MEM_INDEX_KEY_REDISTRIBUTION_STANDARD_RATE

Data Type

Unsigned Integer

Default Value

50

Attributes

Read-Only, Single Value

Range

[10, 90]

Description

The MEM_INDEX_KEY_REDISTRIBUTION_STANDARD_RATE property sets up the minimum values for ratio of unused space in order to redistribute the memory index keys. For example, if the value of this property is 30, the memory key indexes are redistributed when there is at least minimum 30% of unused space of leaf nodes

MEM_INDEX_KEY_REDISTRIBUTION property would not be working if it is set to 0.

The value of this property can be changed using the ALTER SYSTEM statement while Altibase is running.

MULTIPLEXING_CHECK_INTERVAL(Unit: microsecond)

Data Type

Unsigned Integer

Default Value

200000

Attributes

Read-Write, Single Value

Range

[100000, 10000000]

Description

This property indicates the interval at which sessions are checked, so that the thread manager service thread can be distributed. It is expressed in units of microseconds.

The thread manger periodically checks the status of threads, updates statistical data, and adds and deletes service threads.

MULTIPLEXING_MAX_THREAD_COUNT

Data Type

Unsigned Integer

Default Value

1024

Attributes

Read-Write, Single Value

Range

[1, 1024]

Description

This is the maximum number of multiplex threads.

If the capacity of existing threads is exceeded, new threads are automatically added. However, because performance can suffer if new threads are continually created, care must be taken to set this property appropriately.

Nevertheless, when queuing (QUEUE) is used, a number of threads exceeding the value specified by this property can be created.

MULTIPLEXING_THREAD_COUNT

Data Type

Unsigned Integer

Default Value

The number of logical cores

Attributes

Read-Only, Single Value

Range

[1, 1024]

Description

This is the minimum number of shared service threads that Altibase keeps running. The default is the number of logical cores.

This parameter cannot be changed after the server has been started.

NORMALFORM_MAXIMUM

Data Type

Unsigned Integer

Default Value

2048

Range

Read-Write, Single Value

Range

[1, 2³²-1]

Description

This property specifies the maximum number of normal form nodes when normalizing a condition clause.

When the predicates in a WHERE statement of a SELECT query are complicated by the use of logical operators (AND, OR), Altibase normalizes the predicates so that the table(s) can be searched more quickly.

There are two normalization methods: Conjunctive Normal Form (CNF) and Disjunctive Normal Form (DNF). If the use of either of these normal forms results in the number of nodes specified here being exceeded, no attempt to perform normalization using that normal form is made.

If both of the normal forms exceed the number specified here, execution proceeds Not Normal Form(NNF) without the condition clause being normalized. Using the NNF method, the condition is not normalized and the index can not be used.

On the other hand, if the value specified here is set to a larger number to avoid the NNF, the process of normalizing the complicated condition clause can use vast amounts of memory, thus the normalizing process itself becomes so expensive

Therefore, it is important to avoid the excessive use of logical operators when writing condition clauses, and to write condition clauses in normal forms.

Similarly, this rule also applies to an ON predicate joined to an ON condition.

OPTIMIZER_AUTO_STATS

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Write, Single Value

Range

[0, 10]

Description

This property can automatically collects statistical information when there is no available statistical information the optimizer can use.

The number of collectable pages range from 1 to 10 as described below. If it is set to 0, the statistical information cannot be automatically collected.

This property can be changed by using the ALTER SESSION and SYSTEM statements while the system is running.

Value	Pages	Collection Status for gathering statistics
0(Default value)	0	OFF
1	32	ON
2	64	ON
3	128	ON
4	256	ON
5	512	ON
6	1024	ON
7	4096 (32MB)	ON
8	16384 (128MB)	ON
9	65536 (512MB)	ON
10	ALL	ON

OPTIMIZER_DELAYED_EXECUTION

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Write, Single Value

Range

[0, 1]

Description

This property performs execution of hierarchy, sorting, windowing, grouping, set, and distinction in fetch based upon execution plan graphs. The user can check the added DELAY plan under the top PROJECTION in the execution plan

This property reduces execution time and extends the first fetch time by delaying execution to be carried out in the first fetch when executing a query. The first fetch time remains the same, and the execution time can be viewed with the performance view V$STATEMENT.

0 : disable (Deactivated, Default value)

1 : enable (Activated)

This property can be modified with the ALTER SESSION during the Altibase operation.

OPTIMIZER_FEATURE_ENABLE

Data Type

String

Default Value

Altibase Server Version

Attributes

Read-Write, Single Value

Range

[6.1.1.0.6 | 6.1.1.0.7 | 6.3.1.0.1 | 6.5.1.0.0| 7.1.0.0.0]

Description

This property controls properties related to the query optimizer in a uniform manner.

Only the versions specified in the range of this property can be altered with the ALTER SYSTEM statement.

Example:

ALTER SYSTEM SET OPTIMIZER_FEATURE_ENABLE = '6.1.1.0.6';

The following table shows how values of properties related to the query optimizer are set according to the settings of this property

OPTIMIZER_FEATURE_ENABLE	6.1.1.0.6	6.1.1.0.7	6.3.1.0.1	6.5.1.0.0	7.1.0.0.0
QUERY_REWRITE_ENABLE	0 (off)	0 (off)	0 (off)	0 (off)	0 (off)
OPTIMIZER_UNNEST_SUBQUERY	0 (off)	0 (off)	1 (on)	1 (on)	1 (on)
OPTIMIZER_UNNEST_COMPLEX_SUBQUERY	0 (off)	0 (off)	1 (on)	1 (on)	1 (on)
OPTIMIZER_UNNEST_AGGREGATION_SUBQUERY	0 (off)	0 (off)	0 (off)	1 (on)	1 (on)
RESULT_CACHE_ENABLE	0 (off)	0 (off)	0 (off)	0 (off)	0 (off)
TOP_RESULT_CACHE_MODE	0 (off)	0 (off)	0 (off)	0 (off)	0 (off)
OPTIMIZER_AUTO_STATS	0 (off)	0 (off)	0 (off)	0 (off)	0 (off)
OPTIMIZER_PERFORMANCE_VIEW	0 (off)	0 (off)	0 (off)	0 (off)	1 (on)

OPTIMIZER_MODE

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Write, Single Value

Range

[0, 1]

Description

If this property is set to 0, cost-based optimization will be used to optimize query statements, whereas if it is set to 1, rule-based optimization will be used.

This property can be changed using the ALTER SYSTEM or ALTER SESSION statement while Altibase is running.

OPTIMIZER_PERFORMANCE_VIEW

Data Type

Unsigned Integer

Default Value

1

Attributes

Read-Only, Single Value

Range

[0, 1]

Description

This property configures whether or not to set a constraint on the memory. Internal enhancement can be expected when monitoring information of Altibase system

0: (Not apply) : There is no constraints on the memory.

1: {Apply, Default Value) : Memory constraints exist. If the value of EXECUTE_STMT_MEMORY_MAXIMUM exceeds, query execution fails.

OPTIMIZER_UNNEST_AGGREGATION_SUBQUERY

Data Type

Unsigned Integer

Default Value

1

Attributes

Read-Write, Single Value

Range

[0, 1]

Description

If a "nested subquery²,which only contains an aggregate function without the GROUP BY clause", is included in the SELECT statement, this property specifies whether or not the query optimizer is to unnest the subquery.

The value of this property can be modified using the ALTER SYSTEM statement while Altibase is running.

0: unnesting is disabled

1: unnesting is disabled

OPTIMIZER_UNNEST_COMPLEX_SUBQUERY

Data Type

Unsigned Integer

Default Value

1

Attributes

Read-Write, Single Value

Range

[0, 1]

Description

If a complex nested subquery is included in the SELECT statement, this property specifies whether or not the query optimizer is to unnest the subquery. Complexity denotes the existence of two or more tables within the FROM clause or the inclusion of other clauses other than the SELECT, FROM, WHERE clause.

The value of this property can be modified using the ALTER SYSTEM statement while Altibase is running.

0: unnesting is disabled.

1: unnesting is enabled.

OPTIMIZER_UNNEST_SUBQUERY

Data Type

Unsigned Integer

Default Value

1

Attributes

Read-Write, Single Value

Range

[0, 1]

Description

If a nested subquery is included in the SELECT statement, this property specifies whether or not the query optimizer is to unnest the subquery.

The value of this property can be modified using the ALTER SYSTEM statement while Altibase is running.

0: unnesting is disabled

1: unnesting is enabled

OUTER_JOIN_OPERATOR_TRANSFORM_ENABLE

Data Type

Unsigned Integer

Default Value

1

Attributes

Read-Only, Single Value

Range

[0, 1]

Description

This property allows the user to execute an optimized query plan by specifying that an outer join operation supported by the ANSI/ISO SQL standard is to be performed.

If this value is set to 0, an outer join operation supported by the ANSI/ISO SQL standard is performed. If the query statement has an Oracle outer join operator (+), an error occurs. The default value is 1 and uses the Oracle outer join operator (+).

0 : Disable

1 : Enable (Default value, uses the Oracle outer join operator)

The value of this property can be changed using the ALTER SYSTEM statement while Altibase is running.

PARALLEL_LOAD_FACTOR

Data Type

Unsigned Integer

Default Value

2N (N: the number of logical cores)

Attributes

Read-Only, Single Value

Range

[1, 512]

Description

This property controls the number of database refinement and index rebuilding threads that are created to refine the database or rebuild indexes when an Altibase server is restarted.

If this property is commented out, the default system behavior is to generate a number of parallel threads equal to the double of logical cores.

PARALLEL_QUERY_THREAD_MAX

Data Type

Unsigned Integer

Default Value

The number of logical cores

Attributes

Read-Write, Single Value

Range

[1, 1024]

Description

This property specifies the maximum number of worker threads that Altibase can create for the execution of parallel queries. On omission, the number of logical cores under the system is set as the default value for this property.

The value of this property can be changed using the ALTER SYSTEM statement while Altibase is running.

PARALLEL_QUERY_QUEUE_SIZE

Data Type

Unsigned Integer

Default Value

1024

Attributes

Read-Write, Single Value

Range

[4, 1048576]

Description

This property specifies the size of the queue which temporarily stores data on the PARALLEL-QUEUE (PRLQ) node when the Altibase server executes parallel queries.

The value of this property can be changed using the ALTER SYSTEM statement while Altibase is running.

PREPARE_STMT_MEMORY_MAXIMUM (Unit: byte)

Data Type

Unsigned Long

Default Value

100MB

Attributes

Read-Write, Single Value

Range

[1024*1024, 2⁶⁴-1]

Description

This property indicates the maximum amount of memory, in bytes, that can be used to prepare a query statement.

This property may be changed using the ALTER SYSTEM statement while Altibase is running.

QUERY_REWRITE_ENABLE

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Write, Single Value

Range

[0, 1]

Description

This property specifies whether or not to apply function-based indexes on query conversion by the Altibase server.

0: disable

1: enable

REFINE_PAGE_COUNT

Data Type

Unsigned Integer

Default Value

50

Attributes

Read-Only, Single Value

Range

[0, 2³²-1]

Description

One of the Altibase startup steps handles database refinement. When the Altibase server was shut down the previous time, some so-called "versioning records" created by transactions are not handled by the garbage collector, and thus unneeded records may exist in the database, and furthermore, other versioning records created by recovery processes when the server is started up may also exist. The database refining step is conducted so that these records can be reused.

Because this process can be time-consuming when many records are to be refined, it is conducted in parallel by multiple threads. This property specifies the number of pages handled by each thread.

RESULT_CACHE_ENABLE

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Write, Single Value

Range

[0, 1]

Description

This is a property that uses the result cache to save the execution plan of intermediate results.

0: Disabled (Default value) – Do not use the cache function

1: Enabled – Use the cache function

This property may be changed using the ALTER SYSTEM or ALTER SESSION statement while Altibase is running.

RESULT_CACHE_MEMORY_MAXIMUM (Unit: byte)

Data Type

Unsigned Long

Default Value

10M

Attributes

Read-Write, Single Value

Range

[4096, ULONG MAX]

Description

When using result cache and top result cache, it limits the amount of memory to store. If this size is exceeded, it is not stored in memory but released. This property is a constraint for one query and does not provide a system-wide constraint.

This property may be changed using the ALTER SYSTEM statement while Altibase is running.

SECONDARY_BUFFER_ENABLE

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Only, Single Value

Range

[0, 1]

Description

The secondary buffer feature is the flushing of data pages of a memory buffer to the secondary buffer of an SSD(Solid-state Drive) first, instead of flushing them immediately to disk. The data pages of a secondary buffer are flushed to disk by a separate thread within the Altibase server

In order to use the secondary buffer feature normally, the SECONDARY_BUFFER_ENABLE, SECONDARY_BUFFER_FILE_DIRECTORY and SECONDARY_BUFFER_SIZE properties must be specified.

This property specifies whether or not to enable secondary buffer.

0: Enables secondary buffer.

1: Disables secondary buffer.

SECONDARY_BUFFER_FILE_DIRECTORY

Data Type

String

Default Value

""

Attributes

Read-Only, Single Value

Range

None

Description

This property specifies the path of the directory where the secondary buffer file is to exist. The file name is fixed as sbuffer.sbf and cannot be changed. When this property is unspecified, secondary buffer is disabled, even if the SECONDARY_BUFFER_ENABLE property is set to 1.

For more detailed information about the secondary buffer feature, please refer to the description of the SECONDARY_BUFFER_ENABLE property.

SECONDARY_BUFFER_FLUSHER_CNT

Data Type

Unsigned Integer

Default Value

2

Attributes

Read-Only, Single Value

Range

[1, 16]

Description

This property specifies the number of flushers that flush data pages of the secondary buffer to the hard disk.

For more detailed information about the secondary buffer feature, please refer to the description of the SECONDARY_BUFFER_ENABLE property

SECONDARY_BUFFER_SIZE (Unit: byte)

Data Type

Unsigned Long

Default Value

0

Attributes

Read-Only, Single Value

Range

[0, 32GB]

Description

This property specifies the size, in bytes, of the secondary buffer file. When the value of this property is set to 0, secondary buffer is disabled, even if the SECONDARY_BUFFER_ENABLE property is set to 1.

For further information about the secondary buffer feature, please refer to the description of the SECONDARY_BUFFER_ENABLE property.

SECONDARY_BUFFER_TYPE

Data Type

Unsigned Integer

Default Value

2

Attributes

Read-Only, Single Value

Range

[0, 2]

Description

This property specifies the data page type which is to be stored in the secondary buffer. One of the following values can be specified:

0: Pages of all types

1: Dirty pages

2: Clean pages

For more detailed information about the secondary buffer feature, please refer to the description of the SECONDARY_BUFFER_ENABLE property.

SORT_AREA_SIZE (Unit: byte)

Data Type

Unsigned long

Default Value

1048576

Attributes

Read-Write, Single Value

Range

[512, 2⁶⁴-1]

Description

This property specifies the memory size, in bytes, of each temporary table that the server uses for sorting operations.

This property can be changed using the ALTER SYSTEM statement while the system is running.

SQL_PLAN_CACHE_BUCKET_CNT

Data Type

Unsigned Integer

Default Value

127

Attributes

Read-Only, Single Value

Range

[5, 4096]

Description

This property indicates the number of hash table buckets in a SQL plan cache.

SQL_PLAN_CACHE_HOT_REGION_LRU_RATIO (Unit: percentage)

Data Type

Unsigned Integer

Default Value

50

Attributes

Read-Write, Single Value

Range

[10, 100]

Description

This property indicates the percentage of a hot area in an LRU list in a SQL plan cache. A HOT area in an LRU list is a separate portion of an LRU list in a SQL plan cache in which plans that are referred to frequently are saved.

This property can be changed using the ALTER SYSTEM statement while the system is running.

SQL_PLAN_CACHE_PREPARED_EXECUTION_CONTEXT_CNT

Data Type

Unsigned Integer

Default Value

1

Attributes

Read-Write, Single Value

Range

[0, 1024]

Description

This property indicates the number of execution contexts that are initially created when plans are generated.

The initial number of execution contexts is specified before plans are created, however, this only determines the initial number. The number of execution contexts increases or decreases automatically as required during runtime.

Increasing this value can help realize better performance when only one plan is executed at a time, however, in other cases the plan size is merely increased, without realizing improved performance

SQL_PLAN_CACHE_SIZE (Unit: byte)

Data Type

Unsigned long

Default Value

64 M

Attributes

Read-Write, Single Value

Range

[0, 2⁶⁴-1]

Description

This property indicates the maximum size, in bytes, of the SQL plan cache. If set to 0, the cache can't be used. This property can be checked by viewing the value of MAX_CACHE_SIZE of V$SQL_PLAN_CACHE.

This property can be changed using the ALTER SYSTEM statement while the system is running.

STATEMENT_LIST_PARTIAL_SCAN_COUNT

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Write, Single Value

Range

[0, 2³²-1]

Description

This property indicates the maximum number of statements to return to the application in response to a SELECT query executed on V$STATEMENT, V$SQLTEXT, or V$PLANTEXT. If this property is set to 0, all rows pertaining to all statements are returned.

This property can be changed using the ALTER SYSTEM statement while the system is running.

TABLE_INITRANS

Data Type

Unsigned Integer

Default Value

2

Attributes

Read-Only, Single Value

Range

[0, 120]

Description

This property indicates the initial number of TTS (Touched Transaction Slots) to be maintained in a table page.

TABLE_LOCK_ENABLE

Data Type

Unsigned Integer

Default Value

1

Attributes

Read-Write, Single Value

Range

[0, 1]

Description

This property controls the lock level.

0: This does not support the table lock, but the record lock. Efficiency is improved when using it on a simple DML.

1: This supports the table and record lock.

If the value of this property is set to 0, the following restrictions are applied:

• DDL statements cannot be executed.
• CREATE DATABASE cannot be executed.
• Parallel SYNC cannot be used when performing replication.

This property can be changed using the ALTER SYSTEM statement.

TABLE_LOCK_MODE

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Only, Single Value

Range

[0, 1]

Description

This property controls the lock level of a table partition lock. This property is available for use only if the TABLE_LOCK_ENABLE is set to 1.

0: Table partition lock is supported. If the partition lock is used, the simultaneity is enhanced when using a query statement executing per a partition, such as ALTER TABLE…TRUNCATE PARTITION.

1: This does not support the table partition lock, it is identical with the lock on a non-partitioned table of a partitioned table. DML performance is much more improved due to the reduced lock count.

TABLE_MAXTRANS

Data Type

Unsigned Integer

Default Value

120

Attributes

Read-Only, Single Value

Range

[0, 120]

Description

This property indicates the maximum size of the TTS (Touched Transaction Slots) that is maintained for one table page.

TABLESPACE_LOCK_ENABLE

Data Type

Unsigned Integer

Default Value

1

Attributes

Read-Write, Single Value

Range

[0, 1]

Description

This property specifies the lock level for tablespaces.

1: Locks for table spaces are supported.com

0: Locks for tablespaces are unsupported. In this case, the processing speed is enhanced for the execution of simple DML statements; however, the following restrictions apply.

• DDL statements cannot be executed.
• The CREATE DATABASE statement cannot be executed.
• Parallel Synchronization for replication is not supported.

The value of this property can be changed using the ALTER SYSTEM or the ALTER SESSION statement while Altibase is running. However, it can only be changed in the absence of activated transactions

TEMP_STATS_WATCH_TIME

Data Type

Unsigned Int

Default Value

10

Attributes

Read-Write, Single Value

Range

[0, 2³²-1]

Description

This property specifies the standard time to be registered for statistical information. Among the operations using temporary tables, operations that take longer than the value specified for this property are registered for statistical information.

The value of this property can be changed using the ALTER SYSTEM statement while Altibase is running.

THREAD_CPU_AFFINITY

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Only, Single Value

Range

[0, 1]

Description

This property specifies whether or not to bind CPUs to service threads. If this is turned on, a service thread will only be assigned to the same CPU core during its execution. It will reduce costs for moving thread information from one CPU core to another CPU core.

• 0: Do not use CPU affinity feature
• 1: Use CPU affinity feature

However, logging bottlenecks can occur when using CPU affinity on HP-UX IA64. Thus, the Altibase server does not enable CPU affinity, even if the value of this property is set to 1 ( in the altibase.properties file ) on HP-UX IA64.

THREAD_REUSE_ENABLE

Data Type

Unsigned Integer

Default Value

1

Attributes

Read-Only, Single Value

Range

[0, 1]

Description

This property indicates whether or not to reuse threads used on internal Altibase server.

0: New threads

1: Reusing threads (default value)

When you reuse a thread, reduce the cost of creating threads because using the threads waiting in memory.

TIMED_STATISTICS

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Write, Single Value

Range

[0, 1]

Description

This property determines whether to measure the wait time for wait events and the time required for SQL operations. Using this property to specify that the time is to be measured can negatively impact performance.

0: Do not measure the time

1: Measure the time

TIMER_RUNNING_LEVEL

Data Type

Unsigned Integer

Default Value

The default value for this property differs depending on the platform as follows:

1 : all platforms not listed below
2 : IBM-AIX
3 : x86-linux, Amd64-linux

Attributes

Read-Only, Single Value

Range

[1, 3]

Description

This property specifies how to measure the wait time for wait events and the time required for SQL operation.

1: The time measurement thread measures the time at regular intervals specified in the property TIMER_THREAD_RESOLUTION.

2: The time is measured using the library functions provided with respective platforms.

3: This method is similar to #1, but the time is measured using the system clock. Therefore, this method doesn't hinder performance as much as the other methods.

If this property is set to 3 on OS platforms other than Linux and PA-RISC-HP-64, it may become impossible to start the Altibase server due to the inability to correctly check the system time. In this case, the Altibase server will leave a warning message in the altibase_boot.log file, reset the TIMER_RUNNING_LEVEL property to its default value, and restart. On platforms for which the default value is 1, the following message is written to the boot log:

[Warning] Because a TIMER_RUNNING_LEVEL of 3 is not supported on this platform, it has been set to the default(=1) for this platform.

TIMER_THREAD_RESOLUTION (Unit: microsecond)

Data Type

Unsigned Integer

Default Value

1000

Attributes

Read-Write, Single Value

Range

[50, 10000000]

Description

If TIMER_RUNNING_LEVEL is set to 1, this property specifies the time interval.

TOP_RESULT_CACHE_MODE

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Only, Single Value

Range

[0, 3]

Description

This property sets whether to save the execution plan of the final result using the top result cache.

0: Disabled (default value) – Do not use the top result cache function

1: MEMORY - cache the final result of queries using only memory tables

2: DISK – cache the final result of queries using only disk tables

3: ALL – cache the final result of queries regardless of the table type

The value of this property can be changed using the ALTER SYSTEM or ALTER SESSION statement while Altibase is running.

TOTAL_WA_SIZE (Unit: byte)

Data Type

Unsigned Long

Default Value

128MB

Attributes

Read-Write, Single Value

Range

[512KB, 2⁶⁴-1]

Description

This property specifies the maximum size of memory available for allocation for sorting or hashing operations.

The value of this property can be changed using the ALTER SYSTEM statement while Altibase is running. The response to a request for alteration is immediately returned, however, the altered value is not actually applied to the server until the temporary table in use is nonexistent.

INIT_TOTAL_WA_SIZE (Unit: bytes)

Data Type

Unsigned Long

Default Value

128MB

Attributes

Read-Write, Single Value

Range

[512KB, 2⁶⁴-1]

Description

Specifies the amount of memory to be allocated in advance for sorting or hashing.

If it is larger than TOTAL_WA_SIZE, only TOTAL_WA_SIZE is created.

The value of this property can be changed by using the ALTER SYSTEM statemen when Altibase is running

TOUCH_TIME_INTERVAL (Unit: second)

Data Type

Unsigned Integer

Default Value

3

Attributes

Read-Write, Single Value

Range

[0, 100]

Description

This property specifies the minimum time interval, in seconds, at which to increase the buffer access count. After the value specified in this property has passed since the last time the buffer was accessed, the access count is increased.

If this property is set to 3, which is the default value, the access count is not updated if a particular buffer is accessed again less than 3 seconds since it was previously accessed.

TRANSACTION_SEGMENT_COUNT (Unit: count)

Data Type

Unsigned Integer

Default Value

256

Attributes

Read-Write, Single Value

Range

[1, 512]

Description

This property specifies the number of transaction segments (Undo segments and TTS segments) created when the server is started.

This property can be changed using the ALTER SYSTEM statement while Altibase is running.

TRX_UPDATE_MAX_LOGSIZE (Unit: byte)

Data Type

Unsigned Integer

Default

10 MB

Attributes

Read-Write, Single Value

Range

[0, 2⁶⁴-1]

Description

If the size of logs created by a DML statement becomes greater than the number of bytes specified in this property, the corresponding transaction is aborted and an error is returned.

This property is used to prevent unusual increases in system load attributable to large volume batch tasks that result from the user's carelessness.

Because the log size has no limit if this property is set to 0, logs can be used without limit when records are updated. This property can be changed using the ALTER SYSTEM or ALTER SESSION statement when Altibase is running.

SERIAL_EXECUTE_MODE

Data Type

Unsigned Integer

Default Value

0

Attributes

Read-Write, Single value

Range

[0, 1]

Description

This property optimizes the filter in the SCAN PLAN to improve the performance of the filter. At this time, it is optimized only for simple filter, and whether it is optimized can be checked in PLAN TREE.

0: Not optimized

1: Optimized

---

1. "In-place update" means directly updating the value of a column in an original record without creating another version of the record. ↩

2. Nested Subquery: This is a subquery that is contained in the WHERE clause. A nested subquery mainly references columns from a main query in order to limit the result set. Converting a query that contains a nested subquery into an unnested join statement is called "Subquery Unnesting" ↩