Oracle’s Append Hint – Faster INSERTs But Mind the Logging!

In yesterday’s post, I posed a question: What Hint Do You See Over or Misused?

Gary (Twitter) responded with

With APPEND the rows are written directly to the datafiles and are not logged. If the DBA doesn’t know that unlogged changes are being made then whatever recovery plans they have may be useless when it comes to restoring from a backup.

In theory they can set FORCE LOGGING on individual tablespaces so they can’t be sabotaged by a rogue developer. That’s not the default so it relies on the DBA being prepared.

Ok, so this freaked me out a little bit. Mostly because I was upset at myself for not being more familiar with the Direct-Path INSERT technology that Oracle has offered for YEARS. As a matter of fact, I used to load data frequently for customers using SQL*Loader and IMP, so I had a pretty good idea how Direct-Path data loads work. Here’s a quick summary of how it works for INSERTs:

• Data is inserted at the end of the table
• Data bypasses the buffer cache
• Referential Integrity is ignored

I got this from reading the Oracle Docs – good ole Oracle Docs 🙂
Now, if you scroll down a bit, you can read about how the user can also disable ‘LOGGING’ for the data load. And surprise, surprise – this can also aid performance. If Oracle doesn’t have to track the REDO information for its logs, then sure it’s going to run faster. Of course if your system crashes before your next backup, you’re going to need to insert this data again – assuming you can.

Ok, now let’s get back to the APPEND hint. Yeah, developers may be using it to make application inserts run faster. They don’t care about the storage consequences, that’s a DBA’s job. Their application doesn’t even HAVE foreign keys in the database, so they don’t care about that either. So they read about this HINT online somewhere and decide to implement it to speed up their application.

Months later something bad happens, and a ton of customer data is lost.

The DBA is incensed because they come to find out the application was loading data without being logged.

Ok, I have to admit, there are some pretty big holes here. One, let’s hope the application DBA is INTIMATELY familiar with their application code, especially when it comes to how the database transactions work. And B, no self-respecting DBA would create a table or tablespace with NOLOGGING as an attribute, right?

So what happens when someone runs an INSERT with the /* +APPEND */ hint?

Let’s find out!

Create a tablespace with LOGGING
Create a tablespace with NOLOGGING.
Create a table – copy of SH.CUSTOMERS to LOGGING tablespace
Create a table – copy of SH.CUSTOMERS to NOLOGGING tablespace

Take a few records and INSERT with the /*+ APPEND*/ hint to both tables.

Check the redo logs – we should find records for the first table but not the second.

You can set the LOGGING property at the Segment (fancy name for Table or Partition), but if you don’t, it inherits from the TABLESPACE it resides in.

I Freaked Out for No Reason

If you were following me on Twitter this morning you may have noticed a few tweets where I was freaking out. It turns out I was seeing unexpected results because I was runnign inserts against the NOLOGING tables and the records weren’t’ getting logged as expected. I just thought I was writing to the LOGGING tables. I didn’t notice the mistake until I pasted the code into WordPress and noticed the code was wrong. My Replace All in my IDE didn’t replace all what I wanted it to 🙂

Jump To the Results

Here’s my code:
[sql]
–tablespace with LOGGING
CREATE TABLESPACE LOGGING_Y DATAFILE
‘C:ORACLEPRODUCT10.2.0ORADATAORCLLOGGING_Y01.DBF’ SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE 1024M
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

–tablespace with NOLOGGING
CREATE TABLESPACE LOGGING_NO DATAFILE
‘C:ORACLEPRODUCT10.2.0ORADATAORCLLOGGING_N01.DBF’ SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE 1024M
NOLOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

–TABLE IN LOGGING TABLESPACE
create table sh.customers_no_logging
tablespace logging_y
as select * from sh.customers;

–Table in NOLOGGING TABLESPACE
create table sh.customers_no_logging_double
tablespace logging_no
as select * from sh.customers;

— run some additional inserts to the logging table

Insert /*+ Append*/ INTO SH.CUSTOMERS_LOGGING
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
CUST_VALID)
Values
(49671, ‘Abigail’, ‘Ruddy’, ‘M’, 1976,
‘married’, ’27 North Sagadahoc Boulevard’, ‘60332’, ‘Ede’, 51442,
‘Gelderland’, 52610, 52770, ‘519-236-6123’, ‘G: 130,000 – 149,999’,
1500, ‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’),
‘I’);

Insert/*+ APPEND */ into SH.CUSTOMERS_LOGGING
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID, CUST_STATE_PROVINCE,
CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT,
CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM, CUST_VALID)
Values
(3228, ‘Abigail’, ‘Ruddy’, ‘M’, 1964,
’37 West Geneva Street’, ‘55406’, ‘Hoofddorp’, 51669, ‘Noord-Holland’,
52683, 52770, ‘327-194-5008’, ‘G: 130,000 – 149,999’, 7000,
‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’), ‘A’);
Insert /*+ APPEND */ into SH.CUSTOMERS_LOGGING
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
CUST_VALID)
Values
(6783, ‘Abigail’, ‘Ruddy’, ‘M’, 1942,
‘single’, ’47 Toa Alta Road’, ‘34077’, ‘Schimmert’, 52297,
‘Limburg’, 52646, 52770, ‘288-613-9676’, ‘G: 130,000 – 149,999’,
11000, ‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’),
‘I’);
Insert /*+ APPEND */ into SH.CUSTOMERS_LOGGING
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
CUST_VALID)
Values
(10338, ‘Abigail’, ‘Ruddy’, ‘M’, 1977,
‘married’, ’47 South Kanabec Road’, ‘72996’, ‘Scheveningen’, 52296,
‘Zuid-Holland’, 52771, 52770, ‘222-269-1259’, ‘G: 130,000 – 149,999’,
1500, ‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’),
‘I’);
Insert /*+ APPEND */ into SH.CUSTOMERS_LOGGING
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID, CUST_STATE_PROVINCE,
CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT,
CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM, CUST_VALID)
Values
(13894, ‘Abigail’, ‘Ruddy’, ‘M’, 1949,
’57 North 3rd Drive’, ‘67644’, ‘Joinville’, 51698, ‘Santa Catarina’,
52734, 52775, ‘675-133-2226’, ‘G: 130,000 – 149,999’, 9000,
‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’), ‘I’);
Insert /*+ APPEND */ into SH.CUSTOMERS_LOGGING
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
CUST_VALID)
Values
(17449, ‘Abigail’, ‘Ruddy’, ‘M’, 1950,
‘single’, ’67 East Mcintosh Avenue’, ‘83786’, ‘Nagoya’, 51971,
‘Aichi’, 52543, 52782, ‘183-207-2933’, ‘G: 130,000 – 149,999’,
9000, ‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’),
‘A’);
Insert /*+ APPEND */ into SH.CUSTOMERS_LOGGING
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
CUST_VALID)
Values
(21005, ‘Abigail’, ‘Ruddy’, ‘M’, 1946,
‘married’, ’77 Bradford Avenue’, ‘52773’, ‘Santos’, 52293,
‘Sao Paulo’, 52735, 52775, ‘129-379-7148’, ‘G: 130,000 – 149,999’,
3000, ‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’),
‘I’);
Insert /*+ APPEND */ into SH.CUSTOMERS_LOGGING
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID, CUST_STATE_PROVINCE,
CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT,
CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM, CUST_VALID)
Values
(24561, ‘Abigail’, ‘Ruddy’, ‘M’, 1978,
’77 North Packard Avenue’, ‘37400’, ‘Yokohama’, 52526, ‘Kanagawa’,
52634, 52782, ‘689-236-7611’, ‘G: 130,000 – 149,999’, 7000,
‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’), ‘A’);
Insert /*+ APPEND */ into SH.CUSTOMERS_LOGGING
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
CUST_VALID)
Values
(28116, ‘Abigail’, ‘Ruddy’, ‘M’, 1949,
‘single’, ’87 West Coshocton Avenue’, ‘71349’, ‘Haarlem’, 51644,
‘Noord-Holland’, 52683, 52770, ‘208-194-6025’, ‘G: 130,000 – 149,999’,
11000, ‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’),
‘I’);
Insert /*+ APPEND */ into SH.CUSTOMERS_LOGGING
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
CUST_VALID)
Values
(31671, ‘Abigail’, ‘Ruddy’, ‘M’, 1951,
‘married’, ’97 Sagadahoc Avenue’, ‘55056’, ‘Bolton’, 51205,
‘England – Greater Manchester’, 52590, 52789, ‘391-613-8000’, ‘G: 130,000 – 149,999’,
1500, ‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’),
‘I’);
Insert /*+ APPEND */ into SH.CUSTOMERS_LOGGING
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID, CUST_STATE_PROVINCE,
CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT,
CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM, CUST_VALID)
Values
(35227, ‘Abigail’, ‘Ruddy’, ‘M’, 1948,
’97 South Geneva Avenue’, ‘81402’, ‘Lelystad’, 51793, ‘Flevopolder’,
52599, 52770, ‘587-269-1680’, ‘G: 130,000 – 149,999’, 9000,
‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’), ‘I’);
COMMIT;

–now run some inserts to the nonlogging table
Insert /*+ Append*/ INTO SH.CUSTOMERS_NO_LOGGING_DOUBLE
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
CUST_VALID)
Values
(49671, ‘Abigail’, ‘Ruddy’, ‘M’, 1976,
‘married’, ’27 North Sagadahoc Boulevard’, ‘60332’, ‘Ede’, 51442,
‘Gelderland’, 52610, 52770, ‘519-236-6123’, ‘G: 130,000 – 149,999’,
1500, ‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’),
‘I’);

Insert/*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID, CUST_STATE_PROVINCE,
CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT,
CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM, CUST_VALID)
Values
(3228, ‘Abigail’, ‘Ruddy’, ‘M’, 1964,
’37 West Geneva Street’, ‘55406’, ‘Hoofddorp’, 51669, ‘Noord-Holland’,
52683, 52770, ‘327-194-5008’, ‘G: 130,000 – 149,999’, 7000,
‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’), ‘A’);
Insert /*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
CUST_VALID)
Values
(6783, ‘Abigail’, ‘Ruddy’, ‘M’, 1942,
‘single’, ’47 Toa Alta Road’, ‘34077’, ‘Schimmert’, 52297,
‘Limburg’, 52646, 52770, ‘288-613-9676’, ‘G: 130,000 – 149,999’,
11000, ‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’),
‘I’);
Insert /*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
CUST_VALID)
Values
(10338, ‘Abigail’, ‘Ruddy’, ‘M’, 1977,
‘married’, ’47 South Kanabec Road’, ‘72996’, ‘Scheveningen’, 52296,
‘Zuid-Holland’, 52771, 52770, ‘222-269-1259’, ‘G: 130,000 – 149,999’,
1500, ‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’),
‘I’);
Insert /*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID, CUST_STATE_PROVINCE,
CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT,
CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM, CUST_VALID)
Values
(13894, ‘Abigail’, ‘Ruddy’, ‘M’, 1949,
’57 North 3rd Drive’, ‘67644’, ‘Joinville’, 51698, ‘Santa Catarina’,
52734, 52775, ‘675-133-2226’, ‘G: 130,000 – 149,999’, 9000,
‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’), ‘I’);
Insert /*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
CUST_VALID)
Values
(17449, ‘Abigail’, ‘Ruddy’, ‘M’, 1950,
‘single’, ’67 East Mcintosh Avenue’, ‘83786’, ‘Nagoya’, 51971,
‘Aichi’, 52543, 52782, ‘183-207-2933’, ‘G: 130,000 – 149,999’,
9000, ‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’),
‘A’);
Insert /*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
CUST_VALID)
Values
(21005, ‘Abigail’, ‘Ruddy’, ‘M’, 1946,
‘married’, ’77 Bradford Avenue’, ‘52773’, ‘Santos’, 52293,
‘Sao Paulo’, 52735, 52775, ‘129-379-7148’, ‘G: 130,000 – 149,999’,
3000, ‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’),
‘I’);
Insert /*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID, CUST_STATE_PROVINCE,
CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT,
CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM, CUST_VALID)
Values
(24561, ‘Abigail’, ‘Ruddy’, ‘M’, 1978,
’77 North Packard Avenue’, ‘37400’, ‘Yokohama’, 52526, ‘Kanagawa’,
52634, 52782, ‘689-236-7611’, ‘G: 130,000 – 149,999’, 7000,
‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’), ‘A’);
Insert /*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
CUST_VALID)
Values
(28116, ‘Abigail’, ‘Ruddy’, ‘M’, 1949,
‘single’, ’87 West Coshocton Avenue’, ‘71349’, ‘Haarlem’, 51644,
‘Noord-Holland’, 52683, 52770, ‘208-194-6025’, ‘G: 130,000 – 149,999’,
11000, ‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’),
‘I’);
Insert /*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
CUST_VALID)
Values
(31671, ‘Abigail’, ‘Ruddy’, ‘M’, 1951,
‘married’, ’97 Sagadahoc Avenue’, ‘55056’, ‘Bolton’, 51205,
‘England – Greater Manchester’, 52590, 52789, ‘391-613-8000’, ‘G: 130,000 – 149,999’,
1500, ‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’),
‘I’);
Insert /*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
(CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID, CUST_STATE_PROVINCE,
CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT,
CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM, CUST_VALID)
Values
(35227, ‘Abigail’, ‘Ruddy’, ‘M’, 1948,
’97 South Geneva Avenue’, ‘81402’, ‘Lelystad’, 51793, ‘Flevopolder’,
52599, 52770, ‘587-269-1680’, ‘G: 130,000 – 149,999’, 9000,
‘[email protected]’, ‘Customer total’, 52772, TO_DATE(’01/01/1998 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’), ‘I’);
COMMIT;
[/sql]

At this point I had enough going on to at least start checking what was being logged. I fully expect to see the first batch of inserts and non of the second batch in the logs.

If you haven’t used DBMS_LOGMINER before, please go read this first.

I mined all 3 REDO LOGS – I’m lazy.

I now query the v$V$LOGMNR_CONTENTS view to see what’s in the logs.
[sql]
SELECT Operation, Table_name, Scn
, Timestamp, Seg_owner, Table_space
, Username, Session_info, Rbablk
, Data_objd#, Sql_redo
FROM V$logmnr_contents
WHERE Table_name LIKE ‘CUSTOMERS%’
ORDER BY 1
[/sql]

And the results!

I only have one record in the logs for the _NO_LOGGING_DOUBLE table, and that was a test I ran without the APPEND hint to use as a control. It’s interesting to note that Oracle doesn’t include the APPEND hint in the SQL_REDO portion of the record. Well, kind of interesting I suppose. This data would get reconstructed as desired based on our scenario, so it goes back in the database and recovery runs outside of INSERT or SQL*Loader so direct load method really has no context here.

Summary – Control Logging At the Object or Tablespace Level!

If you have LOGGING set at the object level, the user has to try really, really hard to add data to your database that wouldn’t be recoverable. Of course you may still have problems with orphaned records where foreign key constraints were bypassed, but that’s a different story.

Also, don’t freak out on Twitter before you check your code for mistakes. Just because it runs without error doesn’t meant it’s free of mistakes.

Jeff’s Freak Out