Articles

Even with my long term exposure and involvement, I am not the Oracle devotee I would like to be, so finding the time to write up useful techniques and code tips can be very difficult. With my experience and knowledge I feel I should be, but there are plenty of articles available on the websites of Oracle evangelists that will take your understanding a step further than any manual or text book can.

I am a practical-minded hands-on developer with a holistic approach to solving problems. My articles are more orientated around the approach to solving Oracle development problems, than expanding your understanding of the behaviour of Oracle or revealing obscure features.

A claim to fame of mine is my Sudoku solver written in SQL and PL/SQL which is algorithm based rather than brute force. It is getting a bit dated now as it was originally written against Oracle 8i in 2004. There are now new features in Oracle which make this exercise a bit easier, such as refactored queries (WITH clause) and analytic functions.

I hope you enjoy my articles and feel free to provide any feedback.


Dynamic SQL: A performance tuning guide

Dynamic SQL in the wrong hands can have a drastic affect on the performance of your database. This article discusses remedial and preventative techniques to ensure that your dynamic SQL performs. More...


Bulk Data Loading: The available approaches

Database solutions such as data warehouses and data conversion/migration projects can require very large amounts of data to be moved in a highly efficient and robust manner. What solutions are there available? There are plenty of ETL tools on the market, but often these are expensive, complex, and time consuming to implement and configure. Developing a simple PL/SQL based solution may be all you need, but what is the best approach to satisfy your specific requirements? More...


Report Writing: Using Oracle XML, XSL, and HTML

As an Oracle PL/SQL developer you may have been approached to write reports against the database, but have been deterred or hindered by the reporting tools available. You may have found these tools overly complicated for your needs, expensive, client based, with complex server based services, requiring a high degree of training, learning, and administration. More...


Data Driven Processing: XML Metadata

With Oracle’s rich support for XML in the database it is now very hard to not exploit its use in your database application designs. XML is now becoming the most popular format for holding configuration metadata for bespoke systems and even the native format for many desktop applications. This article describes how to develop a generic PL/SQL package to read the hierarchical structure of XML metadata and map it to a hierarchical structure of a PL/SQL nested table record type driven by its own XML mapping rules. It is directed at PL/SQL developers looking to use XML as a metadata format for their data driven applications. More...


Denormalised Design: Prime Numbers and SQL

Many may think that prime numbers are for the mathematicians to play with, and bit of an obscure combination to use with SQL. However, they can be very useful for implementing a compressed/denormalised data solution that can also overcome set theory operations difficult to achieve in SQL. More...


Denormalised Design: Hierarchies

More...


Data Generation: Techniques in SQL

Software applications need to be tested and this may mean being dependent on a lot more data than can be entered through natural means. The quality and authenticity of generated data relies on the sophistication and effort put into the routines. This article describes some techniques to ease the coding. More...


Database Design: Missing or Bad Statistics

A common cause of poor SQL performance in an application is missing/stale CBO statistics on a table. The typical aim is to have statistics gathered on all tables belonging to an application; however there are situations where statistics are not possible or are inappropriate. This article covers the scenarios where this occurs and proposes methods to ensure that they are applied effectively. More...


Database Design: Block Contention

A common bottleneck on busy OLTP applications is contention on the buffer cache. If too many sessions attempt to read the same blocks from the buffer cache, then they can queue up
waiting for low level latches to be released before they can read the blocks from the cache. More...


Database Design: Block Size Design

A database designer has a number of factors to consider when determining a tablespace strategy for a database. A big influence on this is the block size of a tablespace, and if not the default size, how they will work with the various caches. More...


Oracle PL/SQL Sudoku Solver

There comes a time when the monotony of work needs a little refreshing diversion. Some of us like to relax (or frustrate) the time a way with a Sudoku puzzle which is fast becoming pandemic craze. More...


webmaster@db-innovations.co.uk : Copyright © 2005 - 2010 Database Innovations Ltd : Last modified: 27/08/10