Whenever I run a training course, one of the most common questions (or a variant of it) is "what should I store in attributes?" or "when should I use attributes?"  There tends to be a tendency to store as much information as the workflow process might need in individual item attributes – if we were writing this in PL/SQL then there would be no hesitation to fetch the entire record from a table and store it either in a PL/SQL table or even in individual variables.

This is something that really should be resisted at all costs in a workflow process, unless there is a valid reason for storing it there.  Every attribute that you create has to be stored separately within the database, and unless you are creating the attributes dynamically on the fly, then even if you don’t populate the attribute, then there will be a record in the item attribute values table.  This is particularly important if you will be instantiating a large number of processes, since each process has a separate record for each attribute within the table.  Additionally, if you need to fetch the value back out of the database, then this needs to call the Get API once for each attribute.  If you store (for example) ten descriptive flexfield values from a table in separate workflow attributes, this requires the following database access just to store them

• One select operation to retrieve from the base table
• Ten separate update operations to store them in the workflow

If the next activity requires five of these attributes, then you will need to call the API five times, which performs five select operations.

Alternatively, you could store the primary key within the workflow, which can then be used to run further select statements later.  This will perform better than denormalizing the data into workflow attributes in every situation where more than one flexfield is required.  The best approach is to have the primary key to the table stored within the item key, which means that your code will always have the key to the table.  Failing this you should store the key in an attribute, and retrieve data values from the base table by fetching the key from workflow first, then going directly to the table.  One of the best places to view this is within the seeded Order Management workflow, which use the header ID or the line ID as the item key for the workflow processes.  In this case, you have the primary key to the table and then can access the base table to retrieve the data directly from the order header or order line table.

So here’s my guidelines on what you should store in workflow attributes

• If you are using notifications which require message attributes, then these must be stored as item attributes.
• If you need to store the data for audit purposes, or the underlying base table is likely to change and you need to know the original values, then you should store the values in item attributes.
• If you have no primary key or easy method of deriving the data from one or more item attributes, then you could store all the required data in attributes.
• If you don’t need the data for processing or for audit purposes, then don’t include it as an attribute.
• If you are sending notifications which are using PL/SQL or PL/SQL CLOB documents, then you will need an item attribute of type Document.
• If you need to store data for audit purposes, then you should store it as an item attribute.

When you are creating attributes, you should always bear in mind the potential number of instances of the workflow will be stored in the database at the same time.  Bear in mind that there isn’t too much that can be done to tune the tables or queries that the APIs run – this is standard code that if you change you won’t have any further support from Oracle, so the amount of data that is in the table should really be kept to a minimum.  Also, if possible, get the customer tied into a sensible purging policy as early as possible – a customer I worked with in the past wanted to retain the data for 7 years!  At their anticipated sales volumes, that meant that there will be somewhere in excess of 450 million records in the one table…

This entry was posted on Thursday, August 28th, 2008 at 9:40 pm and is filed under Oracle, Technical. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.