monline

People love to write queries that use functions in conditions, but this can easily lead to full table scans.

I suspect I've found a bug (quite serious ... at least to me) in the PL/SQL implementation of CHR().

Long ago I wanted to measure durations at the microsecond level, but Oracle 8i lacked support for this ... or at least in PL/SQL. I digged into the subject and found that the only way to do it was to use some external code/library. The easiest way is to use Java, since Java classes (and even the source) can be stored inside the DB.

There's a thread on AskTom about temporary tablespaces. It's very long, but it's worth to read it through (or at least scan it) if you've got some time. It contains a lot of info about temp. tablespaces.

Most people (using Oracle DBs) sooner or later face the question: are NULL values indexed in the DB or do queries use full tables scans if you include an "IS NULL" condition in them ... even if the column being evaluated is indexed?

Oracle has its own solution for the problem in the package named utl_recomp (you can find it in %ORACLE_HOME%/rdbms/admin/utlrp.sql), however it's only available from 9i and up. Oracle 8i users are left out in the cold ... as was I a long time ago.
I wrote my own recompile code based on what I found on the topic by others (unfortunately I do not remember the sources , otherwise I'd give credit to them) and some of my own researches.

The point is: if you move data from a higher version DB to a lower version DB, then you have to use for both export and import the utilities of the lower version DB. If you are moving data "up" between versions, then export with the EXP utility of the source DB and import with the IMP of the destination DB.

I'm doing such data moving quite rarely (once in one or two years) and always had to rediscover the know-how over and over again. Hopefully I'll remember it now that I made this note in my blog.

The info came originally from AskTom.

The easiest method is to use the COPY command in SQLPLUS. Tom Kyte has a nice example on this.

I found a nice presentation with the title "High speed PL/SQL" by Rodger Lepinsky. It talks about how one should write code to optimize for performance. It's a nice intro for beginners who already know the language and have one or two projects behind them, but have not yet dived into optimizations deeply. I'd say this doc is the "Absolute minimum a PL/SQL developer should know about optimization".