Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted December 28, 2015

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

By David Fitzjarrell

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:\Users\Documents\Oracle_stuff\examples>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:\Users\Documents\Oracle_stuff\examples>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:\Users\Documents\Oracle_stuff\examples>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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM