(To understand the title, see this Wikipedia entry)
The title could also be: “Do as I say, don’t do as I do”, because I want to remind you of an error that I regularly commit in my demonstrations. Here’s an example:
SQL> create table t (n number); Table created
Have you spotted the error yet ? Perhaps this will help:
Here’s a little note that I drafted (according to its date stamp) in January 2013 and then forgot to post. (Which adds a little irony to the title.)
Here’s an object lesson in (a) looking at what’s in front of you, and (b) how hard it is to remember all the details.
I ran a script today [ED: i.e. some time early Jan 2013] that I’ve have no problems with in earlier versions of Oracle, but today I was running it against 18.104.22.168 for the first time, and hit a problem with autotrace:
The idea for this blog post started a few weeks ago when i had to troubleshoot some Oracle database / SQL performance issues at client site. The SQL itself included several views and so placing hints (for testing purpose) into the views was not possible, especially as the views were used widely and not only by the SQL with the performance issue. In consequence this blog post is about the difference between embedded global and local hints and how to use them.
One of the articles I wrote for redgate’s AllthingsOracle site some time ago included a listing of the data distribution for some client data which I had camouflaged. A recent comment on the article asked how I had generated the data – of course the answer was that I hadn’t generated it, but I had done something to take advantage of its existence without revealing the actual values. This article is just a little note showing what I did; it’s not intended as an elegant and stylish display of perfectly optimised SQL, it’s an example of a quick and dirty one-off hack that wasn’t (in my case) a disaster to run.
I’ve based the demonstration on the view all_objects. We start with a simple query showing the distribution of the values of column object_type:
What prompted me to write my previous note about subquerying was an upgrade to 12c, and a check that a few critical queries would not do something nasty on the upgrade. As ever it’s always interesting how many little oddities you can discover while looking closely as some little detail of how the optimizer works. Here’s an oddity that came up in the course of my
playing around investigation in 22.214.171.124 – first some sample data:
I’ve written about optimizer defects with descending indexes before now but a problem came up on the OTN database forum a few days ago that made me decide to look very closely at an example where the arithmetic was clearly defective. The problem revolves around a table with two indexes, one on a date column (TH_UPDATE_TIMESTAMP) and the other a compound index which starts with the same column in descending order (TH_UPDATE_TIMESTAMP DESC, TH_TXN_CODE). The optimizer was picking the “descending” index in cases where it was clearly the wrong index (even after the statistics had been refreshed and all the usual errors relating to date-based indexes had been discounted). Here’s an execution plan from the system which shows that there’s something wrong with the optimizer:
Which piece of code will be faster (clue – the table in question has no indexes):
Option 1 – pure SQL
update join1 set data = data||'#' where key_1=500 and key_2=23851 and key_3=57012 and key_4=521 and key_6=1 and key_7=23352 ;
Option 2 – a silly PL/SQL row by row approach:
A question came up on the OTN database forum recently asking if you could have a partitioned index on a non-partitioned table.
(Aside: I’m not sure whether it would be quicker to read the manuals or try the experiment – either would probably be quicker than posing the question to the forum. As so often happens in these RTFM questions the OP didn’t bother to acknowledge any of the responses)
A recent OTN post demonstrated a very important point about looking at execution plans – especially when you don’t use the right data types. The question was:
We’ve this query which throws invalid number
SELECT * FROM table A WHERE A.corporate_id IN (59375,54387) AND TRUNC(created_dt) BETWEEN '19-DEC-14' AND '25-DEC-14';
However it works fine if we use not in instead of in
SELECT * FROM table A WHERE A.corporate_id NOT IN (59375,54387) AND TRUNC(created_dt) BETWEEN '19-DEC-14' AND '25-DEC-14';
From time to time someone publishes a query on the OTN database forum and asks how to make it go faster, and you look at it and think it’s a nice example to explain a couple of principles because it’s short, easy to understand, obvious what sort of things might be wrong, and easy to fix. Then, after you’ve made a couple of suggestions and explained a couple of ideas the provider simply fades into the distance and doesn’t tell you any more about the query, or whether they’ve taken advantage of your advice, or found some other way to address the problem.
Such a query, with its execution plan, appeared a couple of weeks ago: