Function-based Index on LOB Columns and Oracle’s SQL*Loader

Oracle 11.2.0.3 can still throw a curveball when it comes to LOB columns, notably with the SQL*Loader utility. Under the right conditions SQL*Loader can fail to load a table, throwing an ORA-08102. Let’s look at the issue to see what configuration can create that result.

LOB columns are interesting all by themselves due to the storage and redo mechanisms employed; add a function-based index to the mix and it could be a recipe for failure. The following example illustrates what can go wrong. First, a table is created that contains a LOB column:


SQL> --
SQL> -- Create test table
SQL> --
SQL> -- Include LOB column
SQL> --
SQL> create table lob_idx_tst(
  2  id number,
  3  lobdesc    varchar2(40),
  4  lobdata    clob,
  5  ldate      date);

Table created.

SQL>
SQL> --
SQL> -- Create Function-based index
SQL> -- on LOB column
SQL> --
SQL> create index lob_dat_idx
  2  on lob_idx_tst(id, dbms_lob.getlength(lobdata));

Index created.

SQL>

Now the fun begins: use SQL*Loader to load the table in conventional mode:


C:UsersDocumentsOracle_stuffexamples>sqlldr bing/###########@smedley lob_idx_tst.ctl

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Dec 20 21:07:35 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 

SQL*Loader-462: error inserting LOB into column LDATA, row 2, table
LOB_IDX_TST
ORA-8102: index key not found, obj# , file , block  ()
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

Interestingly using a lower version of SQL*Loader into the same table in the same database succeeds:


C:UsersDocumentsOracle_stuffexamples>sqlldr bing/###########@smedley lob_idx_tst.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on Sun Dec 20 21:09:58 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 9

As another interesting ‘wrinkle’ using direct mode with the 11.2.0.3 utility also succeeds:


C:UsersDocumentsOracle_stuffexamples>sqlldr bing/###########@smedley lob_idx_tst.ctl direct=true

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Dec 20 21:17:35 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 


Commit point reached - logical record count 9.

And if the function-based index is dropped the load runs without error:


SQL> drop index lob_dat_idx;

Index dropped.

SQL> $sqlldr bing/###########@smedley lob_idx_tst.ctl

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Dec 20 21:22:47 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 

Commit point reached - logical record count 9

This is a perplexing issue that is apparently restricted to Oracle 11.2.0.3; no earlier or later versions exhibit this behavior. Oracle Support has no information other than the workaround to use DIRECT=TRUE or, obviously, drop the function-based index. At the very least this isn’t a ‘show stopper’ as a workaround does exist.

Bugs can and do rear their ugly heads when software is modified to provide a new release; some may be regression bugs (where previously fixed behavior reappears) but this doesn’t appear to be in that category. What is especially puzzling is that only one code path is affected; conventional-path loads are the only ones throwing the ORA-08102 error. Given that direct-path loads can generate large amounts of empty space (as can direct load inserts using the /*+ APPEND */ hint) it comes down to a choice between dropping a function-based index before executing a data load and incurring the cost of recreating that index after the load is complete or using DIRECT=TRUE and possibly putting considerable amounts of free space into a table. Time or space, that is the choice to be made with 11.2.0.3 in this situation. Of course another possibility is to upgrade the database, presuming third-party software vendors support Oracle releases greater than 11.2.0.3, which shouldn’t be an issue for current vendors. The upgrade problem lies in older software, from vendors who have stopped supporting Oracle in their latest releases or vendors who have gone out of business and can provide no updates or support for releases newer than the existing software can support. Lest you think that doesn’t happen I know of at least two major enterprises still using software that can’t ‘talk’ to any Oracle release newer than 8i.

SQL*Loader is an excellent utility which can reduce the time it takes to load large volumes of data from flat-file sources. This appears to be the only major stumbling block in Oracle 11.2.0.3 for that utility. Knowing there are workarounds other than a database upgrade can make it easier to deal with. The key is to understand that choosing wisely may mean having to choose again.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles