Oracle’s APPEND Hint – The Key Secret To Fast BULK Loading With NOLOGGING!

Oracle’s logical storage structures:

To developers who work with Oracle, the concept of tablespace is familiar.  We create tables and indexes in tablespaces taking appropriate care not to place all of our data into one tablespace.

Let us take a quick look at the logical structure of an Oracle database

  • A database has one or more tablespaces (objects like tables and indexes are created inside tablespaces)
  • Tablespaces are made up of one or more segments
  • Segments are made of extents
  • Extents are made of database blocks

To developers who load data in bulk the /*+ APPEND */ hint is well known. It takes the form

INSERT /*+ APPEND */ INTO mytable
SELECT * FROM lots_of_data_view;

What does APPEND hint do and how does it work?

For a moment, think of the Oracle’s internal logical structures to be analogous to the OS file system. On a brand new laptop/PC, only a tiny bit of the initial portion of the disk is used. The rest of the space is free as contiguous space.

However, as time goes on, files are created, updated and removed. A lots of holes form in the visual representation of the disk usage. This is frequently termed – fragmentation. We defragment the disk by running utilities that move tiny bits of free space into one large blob while making files themselves be in contiguous blocks. In Oracle, the defragmentation is automatic (called coalescing and it happens automatically and transparently).

How is that related to APPEND? Good question. When the space is fragmented, and when an insert or update is done, Oracle goes about using free space within the fragmented sections where available and coalesces where necessary within blocks to make space for the insert/update.

As the documentation states, specifically, coalescing is done when

  • An INSERT or UPDATE statement attempts to use a block that contains sufficient free space to contain a new row piece.
  • The free space is fragmented so that the row piece cannot be inserted in a contiguous section of the block.

After coalescing, the amount of free space is identical to the amount before the operation, but the space is now contiguous

Append does what is called a direct-path insert. Whereas conventional insert goes through the database buffer cache (memory) and also generates undo and redo along the way, direct-path inserts bypass both (sort of). They do this by allocating additional space above the high-water mark and writing directly to the data files. The only undo generated is for the index maintenance & system structures and no redo is generated. The database should permit NOLOGGING and the table should be in NOLOGGING mode.

Summary of benefits

To summarize, it seems to have quite a bit of advantages

  • Fast – because
    1. Reads of partial blocks are not done thereby reducing reads (yes INSERTS generate reads too)
    2. Writes are done directly above the high water mark
    3. No coalescing of free space is done
  • Fast even with indexes – Indexes are built separately and merged in for the statement
  • Very little undo (related to index maintenance & system structures)
  • No redo
  • The newly inserted data is contiguous. Future queries against that data will be fast.

When is the APPEND hint ignored?

Please read my post on “When APPEND hint is ignored” for more details that what is here.

Here are some instances where APPEND will be quietly ignored (while you think that it is doing a direct-path insert)

  • Table has a referential integrity constraint
  • Table has a trigger
  • Table is in LOGGING mode
  • Database does not permit NOLOGGING
  • …and finally a good one – Oracle will only “try” to respect the hint. It does not have to honor it.

Any disadvantages?

These may not be disadvantages but are certainly issues to consider

  • All NOLOGGING operations will be lost without a database backup to support
  • Within current session, table cannot be queried until the APPEND operation has been committed
  • Outside sessions cannot query the data that was inserted above the high-water mark until committed. This is expected.
  • Free space that is available is completely ignored in favor of new contiguous space

A picture is worth a thousand words!

The following image shows relationship between logical structures of Oracle

**Images from itnerante and Oracle

oracle_logical_structures

A database block is the lowest level entity and it looks as shown below. Usually, it is 8k in size but the block size itself is configurable.

database_row

After a lot of activity happens (inserts/updates/deletes), the block could look like this:

database_block

When using the APPEND hint, the free space like the above will not be considered even it can satisfy the INSERT’s space needs.

References & further reading:

http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_modes.htm#i1008815
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1211797200346279484
http://docs.oracle.com/cd/E14072_01/server.112/e10713/logical.htm

Advertisements

3 thoughts on “Oracle’s APPEND Hint – The Key Secret To Fast BULK Loading With NOLOGGING!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s