Whitepapers and notes about Oracle by Gints Plivna

See also my most popular blog articles.

New: SQL join types.
SQL join types classified and described, explained relationships among them (inner, outer, self, natural, equi, nonequi etc.) with 44 examples tested on Oracle, SQL Server and MySQL.

New: SQL set operators.
SQL set operators - Union, Except (Minus), Intersect with visual pictures and examples tested on Oracle, SQL Server and MySQL.

How to choose database.
Choosing database doesn't mean only calculating license expenses, there are many more criteria for that. One can find here these crriteria along with big list of available databases.

The curse of gapless sequences.
Gapless sequences are real scalability busters and potential performance bottlenecks. Better try to avoid them however there is at least one case when you can do almost nothing - legislation.

Using Analytic Functions in Reports.
Effective access path to data sources can lead to very big performance improvements. Analytic functions and query factoring clause give this possibility. Four examples based on real reports.
Actually article was written ~2 years ago but was never published.

Overview of long running operations in Oracle (entries in v$session_longops).
Quite common questions in various forums and newsgroups are - "My query is running for hours, why it isn't shown in long running operations?" or "I see hash join of only some thousand blocks, why it is running for several hours?" You should be able to find some possible answers for such kind of questions in this article.

Do you have data waste or data base?
Most important problems that changes data base to data waste along with negative consequences.

Effective search in a normalized application.
Effective searching is one of the most common challenges for normalized databases. This article gives detailed description how to solve this problem using materialized views even in very changeable environment.

Data migration from old to new application: an experience.
There are several valuable articles around the internet that provides general overview and strategy for data migration, but I couldn’t find an article that described more technical information about data migration. This article tries to fill this gap and only shortly describes chosen strategy and provides more technical details that are specific for data migration from a legacy application to the new one.

Activating Oracle trace.
Basic things about Oracle trace. How to activate it, what means most often used cryptic abbreviations etc.
Alternative ways how to activate oracle trace.

Naming conventions of tables, columns, indexes, keys, sequences in Oracle.
My view of database object naming conventions. Wherever I participated (and had enough control) I'v tried to enforce them.

Oracle Server Class Diagram.
This diagram was created for educational purposes for subject Object Oriented Modelling. It contains 4 parts - Processes, Memory, Physical storage and Logical Storage. It was created for Oracle 8, so don't look for any new stuff. And of course even in those times it wasn't complete.

Copyright notice:
These papers and notes are provided as-is and without warranty. It is your liability to use or not to use them. You may freely use every paper and note this page contains both for commercial and non-commercial purposes. You may even make derivative works. The only requirement is to preseve original author and site.
In legal terms all that is explained here: Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 2.5 License.

If you have something to add, comment or ask write me a message (replace at with @).