Who's online

There are currently 0 users and 37 guests online.

Recent comments


SQL Quiz – How To Multiply across Rows

A colleague came to me a couple of days ago with a SQL problem. He had something like this:


NAME          INPUT
------------- -----
GROUP_1       5
GROUP_2       3
GROUP_3       4
GROUP_4       7
GROUP_5       3

What he wanted to do was multiply all the inputs across the groups, to get the total number of possible permutations. ie 5*3*4*7*3. The product of all the INPUTS. This has to be in straight SQL. Easy! You just need to… Ahhh… Hmmm…

No, I just could not think of a way to do it that my colleague could use.

Dropped Tables, Hiding Extents and Slow DBA_FREE_SPACE Queries

My last post was on slow dictionary queries caused by having many, many thousands of extents in your database. This post is about a special case of this problem, which I encountered recently. It was on an Exadata box – Exadata is not particularly relevant to the issue, but I’m curious to see if mentioning Exadata will make this post more popular :-)

I was querying the used and free space on a very small database on the client’s X2-2 1/4 rack Exadata machine. The query was really slow, taking about 30 seconds. This is a FAST box, what is going on?

I quickly realised that the problem was specific to one tablespace:

Oracle Query Optimizer Vanishing Acts

February 3, 2012 A couple of days ago I noticed an interesting thread in the Usenet group that described a problem of vanishing tables.  The title of the thread certainly caught my attention, and I was a bit disappointed when I found that the there was little to no magic involved in the vanishing act.  The situation reported [...]

Friday Philosophy – The Answer To Everything

For those of us acquainted with the philosophical works of Douglas Adams we know that the the answer to everything is 42.

mdw1123> select all knowledge from everything
  2  /


This above is a real SQL statement (version, just in case you wanted to know :-) ).

Non-Specific Index Hints

January 24, 2012 (Modified January 25, 2012) As I read the “Troubleshooting Oracle Performance” book for the second time a couple of months ago, I made note on page 385 that it was possible to specify table columns in an index hint, rather than specifying specific index names (or just specifying the table name).  This might be [...]

Dropped Partitions do not go in the Recycle Bin

If you alter table TEST drop partition Q1, does it go in the recycle bin?

That is the question I was asked today. “Of course it….” Pause. More pause.

No, I did not know. I knew I’d seen partitions in the recyclebin on Oracle but I thought there was no option to state PURGE when you dropped a partition.

So, a quick test was needed.

First I tried a drop of a partition I knew I could live without {NB this is on version, I am 99.8% sure this is the same on 10}

Finding the Next Primary Key Value, a Pre-fixed Solution

January 20, 2012 A request for assistance came in from an ERP mailing list.  The original poster (OP) is running an unspecified version of Oracle Database 9i, and is in need of a solution to generate new part numbers with prefixed characters that describe the type of part, followed by a sequential number that is [...]

What Number Immediately Follows 3,999? Oracle Database Refuses to Answer

January 16, 2012 I put together a test case to demonstrate how the physical reads autotrace statistic could exceed the consistent gets autotrace statistic if a single-pass or multi-pass workarea execution were performed during the execution of the SQL statement.  If you are interested, you can see the test case in this recent OTN thread.  [...]

Should SQL have a compulsory ORDER BY clause?

How many times have you heard someone say, “The rows always came back in the correct order, but since the *event* it’s now wrong. The *event* may be an upgrade or some maintenance task on the table etc.

Oracle clearly say,

“Use the ORDER BY clause to order rows returned by the statement. Without an ORDER BY clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.”

This also applies for the GROUP BY clause, that haunted people during their 10g upgrades.

“The GROUP BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause.”

Both these statements can be found in the docs for the SELECT statements.

What is the Meaning of the %CPU Column in an Explain Plan? 2

January 1, 2012 (Back to the Previous Post in the Series) Nearly two years ago I posted the following execution plan in a blog article and asked whether or not there was anything strange about the %CPU column: -------------------------------------------------------------------------------------------------------------- | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  [...]