Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Tom Kyte

Thanks for the question, waseem.

Asked: October 21, 2009 - 11:21 pm UTC

Last updated: October 22, 2009 - 1:17 pm UTC

Version: 11.2

Viewed 1000+ times

You Asked

dear Sir,

a)In our web based OLTP application there will be aroud 1000 tables some of these are transcational and some are reference data. We need to keep detail logging of it. If a portion of record is changed by one user and other portion by other user then we should have change track by individual user. Please suggest which option we use for logging.
1. Put logging in text file.
2. Put loggin in proper history tables.

have a fear that storing this logging information in tables will reduce performance.

If you suggest loggin in history tables then should these be populated through triggers or stored procedures?


b)Where we store large objects including pictures/audio etc. In databae or file system.

c)We are planning to persist db tables through Hibernate in order to speed up development. What you suggest should we do it or use stored procedures?

d) In some table we have composite key what you suggest should we use composite key as primary key or single key as primary key

e) is we use natural key (like zip code) as primary key or we use primary key generated from sequence

and Tom said...

If your audit trail is something you need to use, you would of course put it into the database - in a table - so you can

back it up with the other data
restore it with the other data
secure it
query it
use it


... have a fear that storing this logging information in tables will reduce performance. ...

IF YOU HAVE A REQUIREMENT TO LOG IT, it cannot reduce performance, it is part of your specification.

will it go faster without logging it? YES
will it be right if you do not log it? NO

therefore, logging it is as important as the transaction that causes the logging to happen in the first place


PLEASE - stop thinking of an audit trail as something less than the rest of your system. Audit trails are crucial bits of your system

Do you want to see slow? Go ahead, log to a file. You know what you'll need to do in order to do that safely? SERIALIZE ALL TRANSACTIONS (only one guy at a time can write to that file) - and since you need to support rollback, you'll be writing a TON of file code.

Writing to a file for an audit trail would be among the top ten biggest mistakes you can make.




I hate triggers, I would suggest transactional API's written in PLSQL that are used to perform your transactions (not just simple silly table API's but full blown transactional API's).

b) do you need these objects tomorrow? If so, they belong in a database. If you don't care if they exist or not, put them where ever you like.

c) oh good luck with that. Speed up development in order to slow down production is the likely outcome. I don't really care how fast you churn out code if it doesn't scale or perform.

Did you know that most end users consider performance to be a feature? And most developers seem to consider it something for the DBA to deal with...


I would send the developers to some database training first - and slow down development if you want something good at the end.

d) i am a fan of natural keys when they exist and surrogates when they do not. I don't care if the key is more than one attribute, if it is the natural key, use it.

e) see (d)

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.