If you can't find the product or version you're looking for, visit support.ataccama.com/downloads

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.

Default Oracle hints

<<vldb-commands>
    <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?