Statement cache directive

Use the statement cache directive to prevent the statement from being stored in the statement cache. The AVOID_STMT_CACHE optimizer directive forces the optimizer to reoptimize the statement every time that the statement is run.

Read syntax diagramSkip visual syntax diagram
Statement cache directive


Element Description Restrictions Syntax
comments Text documenting the directive Must appear between comment symbols Character string

The AVOID_STMT_CACHE directive is useful for those statements whose query plans can change significantly depending on the values of the placeholders that are passed to the database server when the statements are run. Using a cached query plan for those sorts of statements might result in poor performance.

For example, the following statement is not stored in the statement cache:

    c.customer_num, c.lname, o.order_date
    FROM customer c, order o
    WHERE c.customer_num = o.customer_num;

Copyright© 2018 HCL Technologies Limited