Database Hints

The MDM engine provides the ability to use hints in database engine queries applied during processing. Hints provide more stability and speed to the data returned when the database is queried. Hints are enabled by default for the Oracle Database platform.

For help with enabling SQL hints and their customization in the VLDB persistence, see Persistence Layer.

Any changes to the statements have to be saved in a dedicated configuration file (for example, vldb-commands.xml).

The following is a sample with the default structure and commands applicable for Oracle. The changes should be applied only to the queries inside the <template> elements.

Change the queries with caution, relying on the expert knowledge of your database engine.

Commands

FIND command has two variants (small and large data), the threshold is the runtime parameter nme.vldb.smallDataThreshold (default is 200 000).
IN_LIST commands can have more variants. Variants are considered in order of appearance and chosen if the condition is met (element expectedDataSize).
- Element expectedDataSize can have the following attributes:
  - size - Either SMALL or LARGE, the threshold is the same as in FIND command via nme.vldb.smallDataThreshold.
  - lessThan - A number. Requests in inlist table must be less than the given number.
  - greaterThan - A number. Requests in inlist table must be greater than the given number.
- If size is defined, nothing else can be defined.
- If size is not defined, at least one of lessThan, greaterThan must be defined.
- If the whole element expectedDataSize is not defined, a variant is always used.
IN_LIST command UNMATCH does not have any variants (as it selects all records not in inlist). It is sufficient to provide a template.
VLDB persistence is able to run custom SQL during inlist after keys are inserted into INL_S or INL_L table and before running JOIN of inlist and data table. Symbolic names {inlist}, {data}, and {keyName} can be used.

Symbolic names used in templates in {} for FIND commands:

columns - List of columns to be selected.
data - Table to get data from.
xdata - Corresponding X table.
where - Where condition (expands to "WHERE col1 = ? …").
order - Order by sorting (expands to "ORDER BY col1 DESC, …").

More symbolic names used in templates in {} for IN_LISTS commands:

inlist - Name of inlist table with values to join with data table.
keyName - Name of column in data table used in Join.
inwhere - Special variant of where (expands to " AND col1 = ? …") because it is replaced in SQL command that contains WHERE query already.

For PostgreSQL databases, starting from version 14.5.1, these elements are no longer supported and need to be removed from the configuration file:

inlistBeforeCommand
l.id_list = ?

Default Oracle hints

<vldb-commands>
    <inlistBeforeCommand>
        <variants>
            <commandVariant>
                <expectedDataSize greaterThan="15" />
                <template>
                    INSERT INTO mdc_table_assert(id, val) VALUES(1, '{data}-{keyName}-{inlist}')
                </template>
            </commandVariant>
        </variants>
    </inlistBeforeCommand>

    <inlistOrderedCommand>
        <variants>
            <commandVariant>
                <expectedDataSize size="SMALL" />
                <template>
                    SELECT /*+ ORDERED USE_NL(l d x) FULL(l) INDEX(d) INDEX(x) */ l.seq_num, d.xctid, x.xdtid, {columns}
                    FROM
                    (
                        {inlist} l INNER JOIN {data} d
                            ON l.id_list = ? AND d.{keyName}=l.key_val
                    )
                    LEFT JOIN {xdata} x ON d.xid = x.xid
                        {where}
                        ORDER BY l.seq_num
                </template>
            </commandVariant>
            <commandVariant>
                <expectedDataSize size="LARGE" />
                <template>
                    SELECT /*+ORDERED USE_HASH(l d x) SWAP_JOIN_INPUTS(l) SWAP_JOIN_INPUTS(x) FULL(d) FULL(x) FULL(l) */ l.seq_num, d.xctid, x.xdtid, {columns}
                    FROM
                    (
                        {inlist} l INNER JOIN {data} d
                            ON l.id_list = ? AND d.{keyName}=l.key_val
                    )
                    LEFT JOIN {xdata} x ON d.xid = x.xid
                        {where}
                        ORDER BY l.seq_num
                </template>
            </commandVariant>
        </variants>
    </inlistOrderedCommand>

    <inlistUnorderedCommand>
        <variants>
            <commandVariant>
                <expectedDataSize size="SMALL" />
                <template>
                    SELECT /*+ ORDERED USE_NL(l d x) FULL(l) INDEX(d) INDEX(x) */ d.xctid, x.xdtid, {columns}
                        FROM
                        (
                            {inlist} l INNER JOIN {data} d
                                on l.id_list = ? and d.{keyName}=l.key_val
                        )
                        LEFT JOIN {xdata} x on d.xid = x.xid
                            {where}
                </template>
            </commandVariant>
            <commandVariant>
                <expectedDataSize size="LARGE" />
                <template>
                    SELECT /*+ORDERED USE_HASH(l d x) SWAP_JOIN_INPUTS(l) SWAP_JOIN_INPUTS(x) FULL(d) FULL(x) FULL(l) */ d.xctid, x.xdtid, {columns}
                        FROM
                        (
                            {inlist} l INNER JOIN {data} d
                                on l.id_list = ? and d.{keyName}=l.key_val
                        )
                        LEFT JOIN {xdata} x on d.xid = x.xid
                            {where}
                </template>
            </commandVariant>
        </variants>
    </inlistUnorderedCommand>

    <inlistUnmatchedCommand>
        <template>
            SELECT /*+ORDERED USE_HASH(l d x) SWAP_JOIN_INPUTS(l) SWAP_JOIN_INPUTS(x) FULL(d) FULL(x) FULL(l) */ d.xctid, x.xdtid, {columns}
            FROM
            (
                {inlist} l
                    RIGHT JOIN {data} d
                    ON d.{keyName} = l.key_val and l.id_list = ?
            )
            LEFT JOIN {xdata} x on d.xid = x.xid
                WHERE l.key_val is null
                {inwhere}
        </template>
    </inlistUnmatchedCommand>

    <findCommand>
        <smallDataVariant>
            SELECT /*+ORDERED USE_NL(x d) INDEX(x) */ d.xctid, x.xdtid, {columns}
            FROM {data} d
                LEFT JOIN {xdata} x on d.xid = x.xid
                {where}
        </smallDataVariant>
        <largeDataVariant>
            SELECT /*+ORDERED USE_HASH(x d) SWAP_JOIN_INPUTS(x) FULL(d) FULL(x) */ d.xctid, x.xdtid, {columns}
            FROM {xdata} x
                RIGHT JOIN {data} d on d.xid = x.xid
                {where}
        </largeDataVariant>
    </findCommand>
</vldb-commands>

Was this page useful?