Select Page

VOLATILE: A Useful Little Keyword

Craig Mullins | | March 2, 2017

VOLATILE keyword

Just a short blog entry today to remind everyone about the VOLATILE keyword. This keyword was added in DB2 Version 8 and it can be specified on a table using CREATE TABLE and/or ALTER TABLE statements.

OK, so what will VOLATILE do? Basically, this keyword is used to indicate that the volume of data in the table is volatile and is likely to fluctuate. One common scenario where VOLATILE will be helpful is for tables that are emptied nightly and then repopulated the next day, such as an input queue.

When you specify the VOLATILE keyword on a table, BIND will favor using indexed access paths, even if the table was empty when RUNSTATS was run.

ERP environments (e.g. SAP, Peoplesoft) with thousands of tables typically have some tables that meet these criteria. Even worse, it is not uncommon for DBAs to have no idea of the actual content or use for many of those thousands of tables generated by the ERP installation. Some are not used based on which modules of the ERP system you implement, but the tables get created anyway. Many DBAs simply maintain all of the tables provided with the ERP system, whether they are used or not, including running image copies and gathering RUNSTATS for them… and many are empty tables.

Collecting statistics on an empty table populates the catalog with stats indicating that the table contains no data. And, of course, when access paths are generated using those statistics DB2 will probably favor a scan because the table is small (how much smaller can you get than empty?) But some of those tables are volatile, going from empty to perhaps hundreds of thousands of rows during processing.

Of course, if the table is actually empty (or contains only a small amount of data), and VOLATILE is specified, DB2 will use an index if one exists, which can degrade performance a bit. But that is a smaller price to pay than scanning thousands of rows, isn’t it?

So the answer is to use the VOLATILE keyword for these type of tables… your users will be glad you did.

This post was originally published on Craig Mullins’ blog: http://db2portal.blogspot.com/2009/01/volatile-useful-little-keyword.html

 

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018
code

Recover a Table from an RMAN Backup in an Oracle 12c

This blog post will is to show a table restore for one table in a container database

Megan Elphingstone | February 2, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS