Length of the DataType Matters.
For example if you define a column with VARCHAR2(4000) (just the maximum limit) then you may outflow you array as given in the example below.
Varchar2(n) where n is "right sized" | Varchar2(4000) for everything (just in case) |
---|---|
Assume 10 columns, average width is 40 characters (some are 80, some are 10...). | Assume 10 columns, average, minimum, maximum width is 4000. |
400 bytes per row on a fetch. | 40,000 bytes per row on a fetch. |
Assume array fetch of 100 rows, so array fetch buffer or 40,000 bytes. | Assume array fetch of 100 rows, so array fetch buffer or 4,000,000 bytes. |
Assume 25 open cursors, so 1,000,000 bytes of array fetch buffers. | Assume 25 open cursors, so 100,000,000 bytes. |
Assume connection pool with 30 connections — 30MB. | Assume connection pool with 30 connections — 3GB. |
NOT Null columns should be preferred over Null able columns. The reason is if you have an Index on a Null able column then it would not be used by the SQL as the optimizer thinks that it would not find any values in some of the columns so prefer a full scan.
As a workaround for columns with NULL data types the Index create SQL should look like:
Create INDEX ABC ON TAB1 (COLUMN1, 0);
This will make sure that in case the Column1 is null the optimizer will consider the value as 0 and leads to index scan as compared to Full scans.
Usage of Leading Hints helps in choosing what Table should be the first table in the join order.
/*+ LEADING(Table1 Table2) */
When writing comments within SQL statements make sure that the comments are not added at the beginning because DB2 will not be able to parse it. You can instead put the Comments at the end and it will work. For Oracle this is not an issue.
Additional information on optimizing SQL in your OUAF applications can be found in the Oracle Utilities Application Framework - Technical Best Practices whitepaper available on the My Oracle Support (MOS) Knowledge Base (article 560367.1).