David Fitzjarrell

While investigating a question on returning unmatched rows between two tables I set up an example where both tables had indexes, which could be used to speed up the query and hopefully return the result set in less time than required for a full table scan. To my surprise when the index was used on […]

In Release 11.2, Oracle has provided three improvements to the earlier attempts at controlling parallel execution. These improvements make this feature more manageable, more scalable, and less likely to saturate server resources, such as memory and CPU, than earlier releases of the database. The first of those improvements that we will discuss is parallel statement […]

In a forum I frequent a question was raised regarding an index not being used. The poster is using Oracle 11.1.0.7, which limits the index access paths Oracle can use; a concatenated index is created on the table (which has columns x,y,z,t,q and w, for lack of better names) on columns x, y, and z. […]

Collections are an interesting lot. They can be one of the most useful tools in the Oracle arsenal, yet they can also be very frustrating to implement. For those unfamiliar with them, a collection/varray is defined as “an ordered group of elements, all of the same type. Each element has a unique subscript that determines […]

Exadata is a different system for a DBA to administer. Some tasks in this environment, such as running the exachk script, require root O/S privileges. This script can be run by the system administrator, and this will be the case if you are managing Exadata as a DBA. However, a new role has emerged relative […]

In an earlier article I covered Hybrid Columnar Compression, an option specific to Exadata and systems using Exadata storage. Those aren’t the only compression options; two others, BASIC and OLTP, are available outside of the Advanced Compression licensing and can be used on non-Exadata storage. Let’s go over the HCC options again and compare those […]

From time to time I find a situation where a developer believes he or she is better at enforcing referential integrity than the database. They code intricate sections generating unique IDs based on the maximum value of the id column. They then use this ‘unique’ key to also enforce referential integrity in child tables. Unfortunately […]

Since its introduction in September, 2008, Exadata has fast become both a familiar term and a familiar sight in the IT/database realm. The system has undergone several changes in its short history, from storage solution to complete database appliance. What IS Exadata? Exadata is a machine, composed of matched and tuned components providing enhancements available […]

A question was recently asked by a colleague regarding unique indexes and constraints. He was complaining that the unique index did not create the associated constraint by default. Unlike the situation when a unique constraint is created (the associated unique index is created if no other index is specified) there really isn’t any reason for […]

Monitoring Smart Flash Cache on the database servers is very limited as only one metric is available from V$SYSSTAT that records flash cache activity — “cell flash cache read hits”. On the other hand monitoring the Smart Flash Cache from the storage servers provides more detail and diagnostic data, although there is no way to […]

Hybrid Columnar Compression is a compression option available only for tables residing on Exadata storage, which besides Exadata includes Axiom, the SPARC SuperCluster and the Sun ZFS Storage Appliance. If you try this on any other system you will receive the following informative message: SQL> alter table emp move compress for query high; alter table […]

Occasionally it may be necessary to ‘flip’ a table on its side, so to speak, meaning to convert rows to columns in order to display data in a more understandable format. Depending on the release of Oracle there are several techniques that can be used to accomplish this task. Most of these, however, require a […]