I was a little surprised by the reaction I got to my previous post on this subject. A number of people commented about the problems with automation and many pointed to this very appropriate comic on the subject.
There are one of two conclusions I can draw from this.
I’m really hoping the answer is option 1, because I think it would be really sad if being a DBA has degenerated to the point where people spend their whole life doing tasks that could be easily scripted.
To create an index on a table (with no existing indexes) Oracle has to start by doing a tablescan.
What’s the difference between the tablescan it uses for a B-tree index and the tablescan it uses for a bitmap index ? Why ?
I’m in the process of taking on some of the MySQL databases in my company. The first ones are MySQL 4.1 running on Windows, so we are upgrading them to MySQL 5.6 on Oracle Linux. As with many of our systems, these will be running on VMware virtual machines.
Since the current installations are so old, we are planning on dumping out the data and creating fresh installations on the new systems. Based on the advice I got from Ronald Bradford and Sheeri Cabral, we are also taking this opportunity to switch to InnoDB and utf8, rather than MyISAM and latin1 that are currently used.
Training Days 2014 for RMOUG starts in just TWO days. Tim and I have picked up Jonathan Lewis and Pete Sharman this weekend, the flurry of speakers will be arriving in the next couple days. With the fantastic help of Team YCC, we have an incredible conference planned for everyone!
It doesn’t matter which bit of Oracle technology you want to use, eventually someone, somewhere, runs into the special case where something nasty happens. Here’s an edge case for people using (index) clusters – Oracle Bug 17866999 ora-1499 for cluster following rman convert
It comes from a conversation on Oracle-L where Jack van Zanen reported a problem of inconsistent results after migrating data between platforms using rman to converts some tablespaces containing index clusters. This is the starting post where he shows a query that is clearly getting the wrong answer (select where channel_number = 503 obviously shouldn’t return data with channel_number 501).
Basically I’m a lazy person with a short attention span, so when I’ve done something once, I get kind-of bored of doing it again and again. As a result, automation is a perfect solution to me. Figure out how to do something, script it so I can repeat it easily, then move on! *
Sitting in the lounge waiting to be called for my flight I was musing on the 12c feature of having multiple indexes defined on the same ordered column set when a thought crossed my mind and I decided to run a little test that looked like this:
For those of you using Oracle Linux with UEK3, here are a couple of important blog posts that may have passed you by.
A recent “Hot topics” email from Oracle support listed the following bug as one which had recently been updated:
17727676 OPTIMIZER HINT IGNORED WHEN USING INVISIBLE INDEXES
Since the optimizer is one of my pet topics I thought I’d take a quick look at what it said – and found this heart-warming introduction;
Hdr: 17727676 188.8.131.52.0 RDBMS 184.108.40.206.0 QRY OPTIMIZER PRODID-5 PORTID-226 Abstract: OPTIMIZER HINT IGNORED WHEN USING INVISIBLE INDEXES *** 11/03/13 03:46 am *** PROBLEM: -------- Based on a blog article from the international recognized Oracle Expert Jonathan Lewis ...
If it’s on MoS surely it’s just got to be true! (Yes, I know I’ve said the opposite in the past – but it’s definitely right some of the time)
I was involved in a thread on Oracle-L recently started with the question: “How many LIOs is too many LIOs”. Rather than rewrite the whole story, I’ve supplied a list of links to the contributions I made, in order – the final “answer” is actually the answer to a different question – but travels an interesting path to get there.#