Lead your team forward
OCT 24 / 9AM ET Register nowDatabase 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 parameternme.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 (elementexpectedDataSize
).-
Element
expectedDataSize
can have the following attributes:-
size
- EitherSMALL
orLARGE
, the threshold is the same as inFIND
command vianme.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
commandUNMATCH
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
orINL_L
table and before runningJOIN
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 containsWHERE
query already.
<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?