Performance impact of using SQL semantics
You'll need to give some thought to performance when you are using the new SQL semantics for LOBs functionality. Remember that the "L" in LOB stands for "large," and that "large" can be as much as four gigabytes. Consequently, you may encounter some serious performance issues if you indiscriminately treat LOBs the same as any other type of variable or column. Have a look at the following query, which attempts to identify all waterfalls for which a visit might require a trip across the Mackinac Bridge:
SELECT falls_nameFROM waterfallsWHERE INSTR(UPPER(falls_directions),'MACKINAC BRIDGE') <> 0;Think about what Oracle must do to resolve this query. For every row in the waterfalls table, it must take the falls_directions column, uppercase it, and place those results into a temporary CLOB (residing in your temporary tablespace). Then it must apply the INSTR function to that temporary LOB to search for the string `MACKINAC BRIDGE'. In our examples, the directions have been fairly short. Imagine, however, that falls_directions were truly a large LOB, and that the average column size were one gigabyte. Think of the drain on your temporary tablespace as Oracle allocated the necessary room for the temporary LOBs created when uppercasing the directions. Then think of all the time required to make a copy of each CLOB in order to uppercase it, the time required to allocate and deallocate space for temporary CLOBs in your temporary tablespace, and the time required for the INSTR function to search character-by-character through an average of 1 GB per CLOB. Such a query would surely bring the wrath of your DBA down upon you.
| Oracle Text and SQL Semantics If you need to execute queries that look at uppercase versions of CLOB values and you need to do so efficiently, Oracle Text may hold the solution. For example, you might reasonably expect to write a query such as the following some day: SELECT falls_nameFROM waterfallsWHERE INSTR(UPPER(falls_directions), 'MACKINAC BRIDGE') <> 0; If falls_directions is a CLOB column, this query may not be all that efficient. However, if you are using Oracle Text, you can define a case-insensitive Oracle Text index on that CLOB column, and then use the CONTAINS predicate to efficiently evaluate the query: SELECT falls_nameFROM waterfallsWHERE CONTAINS(falls_directions,'mackinac bridge') > 0; For more information on CONTAINS and case-insensitive indexes using Oracle Text, see the Oracle Text Application Developer's Guide. |
Because of all the performance ramifications of applying SQL semantics to LOBs, Oracle's documentation suggests that you limit such applications to LOBs that are 100 KB or less in size. We ourselves don't have a specific size recommendation to pass on to you; you should consider each case in terms of your particular circumstances and how badly you need to accomplish a given task. We encourage you, however, to always give thought to the performance implications of using SQL semantics for LOBs, and possibly to run some tests to experience these implications, so that you can make a reasonable decision based on your circumstances.