Let’s All Join In

While investigating a question on returing 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 the table with the missing record the query returned no rows. Not understanding this behavior (as I KNEW there was (more...)

Hide And Seek (After A Fashion)

Indexes can be very useful tools to improve query performance, but it can be difficult to test indexes in an environment that exactly mirrors production. Although not the preferred method, Oracle offers in 11.2 and later releases the option of invisible indexes. What are invisible indexes? An invisible index can’t be ‘seen’ by any session by default, so it can be created and remain unavailable until testing is scheduled. This can be confusing since (more...)

You Take Too Long

Sometimes statements take longer than you’d like to complete. This can happen for a number of reasons including data changes, table growth, stale statistics and the occasional optimizer bug you haven’t encountered before. When it’s a SQL query that is misbehaving it’s pretty easy to find the cause and, in many cases, fix it. When it’s PL/SQL it isn’t as easy to do. There are views available from Oracle to assist in this endeavor and, (more...)

Space Exploration

Managing free space in a tablespace seems to be an easy task, what with the views DBA_DATA_FILES, DBA_TEMP_FILES and DBA_FREE_SPACE avaliable. Those views can provide accurate information for datafiles not set to autoextend, however for autoextensible datafiles they can paint a bleaker picture because they’re based on the current size of the file, not the autoextend limit that file could reach. Let’s look at how those views, without taking autoextend into consideration, can report a (more...)

You’re Not In The Group

In yet another Oracle forum (yes, I’m in a number of them) the following question was posed:

I'm reading oracle 9i sql and there is an example of a subquery which I'm trying to write as a group by query.

 select empno, ename, sal
 from emp
 where sal =
 (select min(sal) from emp);

 This works but I don't understand why I can't write this as a group by expression

 select empno, ename, min(sal)
 from emp

“Skip” That

In a forum I frequent a question was raised regarding an index not being used. The poster is using Oracle, 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. Let’s look at what will do with such a situation then explain what (more...)

To DMA, Or Not To DMA

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 (more...)

“Compressing” Matters

Compression is often mentioned in reverent tones as the cure for database space problems, and properly used it can be just that. However simply throwning compression at a table without careful consideration of what can be accomplished or what behavior to expect afterwards can leave you scratching your head in (more...)

Sorting Out Constraints

An interesting question was posed on the Oracle-L list a while back; a member was having difficulty getting a constraint created using an existing non-unique index as Oracle would consistently consume all of the TEMP space and cause the process to terminate for lack of resources. The conventional wisdom was (more...)

We Have No Constraints

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 (more...)