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... |
