Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2001

    Unanswered: VOLATILE option for tables

    So, the way I understand the VOLATILE option on tables, it forces the optimizer to pick up indexes on the tables regradless of statistics and their presence.

    Use of Indexes is never a bad thing in access plans. So, why would I not want to enable VOLATILE for all the tables? Are there any disadvantages to enabling VOLATILE for tables?


  2. #2
    Join Date
    Apr 2002
    Using Volatile doesn't Force the optimizer to use Indexes. It will influence the Optimizer in choosing Indexes in its Access Path, if suitable indexes exist. The optimizer tends to act as though the table in question has many more rows on it. However if there are no suitably defined indexes TableScans and Sorts will still occur.

    You probably wouldn't want to use volatile on smaller tables, say 4000 or less records. Very often it is less expensive for DB2 to do a TableScan of small tables rather than use both index and table for access.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts