Oracle Database SQL Expert (1Z0-047)…

I can see this post degenerating into a rant, so I would like to preemptively appologize to anyone involved in the production of this exam. I’m guessing it’s a real pain to develop these exams, especially when some ass like me starts moaning about them. Added to that, I’m guessing the word “Expert” means slightly different things to different people…

I’ve been barking on recently that in my opinion, the most important skill required by any PL/SQL developer is SQL, with knowledge of PL/SQL itself coming in second place. Having recently taken the “Oracle Database 11g: Advanced PL/SQL (1Z0-146)” exam (mentioned here), I thought it was a little hypocritical not to sit the “Oracle Database SQL Expert (1Z0-047)” exam as well, so this morning I did just that.

Here are some of my thoughts on the exam, in no particular order of importance:

  • Regular Expressions: I think it is important that people understand what regular expressions can do and when it is appropriate to use them, but I don’t think it is necessary to test people on the meta-characters themselves. That’s what the docs are for.
  • Analytic Functions: No sign of them in my questions from the pool. Surely analytic functions are more important than regular expression meta-characters.
  • The majority of the exhibits were pointless. It seems like they were placed there to waste the time of people with bad exam technique, rather than to assist in answering the question. This was especially true of the schema diagrams, which I only referred to once when the datatype of one of the columns was important.
  • Several of the questions could be answered without reading the question at all, as the incorrect answers jumped out at you because they contained blatantly incorrect statements.
  • Several of the questions included the “ANY” and “ALL” comparison conditions, which are barely mentioned in the documentation (here)*. I guess these are only included in Oracle because the are part of ANSI SQL. I can’t remember ever using them in Oracle or seeing them being used by others. I have come across them in MySQL so I knew what they were for, which was fortunate.
  • There were lots of questions that included DML against inline views rather than directly against tables. It got to the point where I felt like, “If it’s got braces in it I’m going to tick it”.

I very quickly turned into a grumpy old man and started to rush through the exam, spending most of my time thinking about writing this blog post, rather than the exam itself. 🙂

In the end I got 96%, which I guess means I got 3 questions wrong out of the 70. Serves me right for rushing it so I could come home and bitch about it. 🙂

So I am now an “Oracle Database: SQL Certified Expert” as well as a grumpy old shite…

Cheers

Tim…

* Updated thanks to Pierre’s comment.

How to kill mySQL? Keep adding features…

I was reading this article the other day and it reminded me of a conversation I had at ODTUG about mySQL. I know nothing about Oracle’s plans for mySQL and I’m only a casual user of mySQL (for about 6 years), so don’t consider this an inside scoop, it’s just some idle whittering…

If Oracle truly wanted to kill mySQL how should they do it? My answer was keep adding features. Why?

  • Adding features would keep everyone off their back because they would be seen as actively supporting mySQL. This would allow them to blamelessly kill mySQL… Mwahaha (in a DR Evil style).
  • Ease of Installation: mySQL is really easy to get hold of because it comes with a couple of clicks from pretty well every Linux distro. As the feature set and flexibility of mySQL increases, it’s not unlikely that the installation process could/would become more complicated. If it were complicated enough to not allow a 1 click install during OS installation, then the ease of installation is gone. So keep adding features until you lose the ease of installation.
  • Speed: For basic workloads mySQL is very fast. I’m guessing the vast majority of mySQL installations have a very simple workload profile, like the zillions of blogs, forums and wikis out there. Remember, there is a difference between the number of operations and the complexity of those operations. As you add features you are increasing the amount of code in the engine, which invariably slows it down, but more importantly you are also increasing the expectation of the users. As users start throwing increasingly complex workloads at the engine, it gets proportionally harder to keep delivering top performance.
  • Ease of Use: mySQL is considered a very easy engine to use. I could argue that point, but lets run with it. So we add a bunch of features and people start to use them. Oh dear. Stops looking quite so simple to dip your toe in…
  • Cheap: mySQL is free (sort of). I don’t see this as a major issue. Look at the companies who spend the real money. They just want something that does the job and are prepared to pay for it. Oracle has never been about the low hanging fruit. Fast forward a few years and I doubt we will see any DBs in SMBs. They will all be managed services by Oracle, Amazon, Google etc. For those where “free” is an issue, adding features can complicate the product to the point where support becomes a necessity. It’s no longer free then…

That’s what I would do. 🙂

Cheers

Tim…

mySQL: OR, UNION and UNION ALL

I was doing some SQL tuning on mySQL today and I learned a few interesting things.

Let’s take an example of a simple query where we have an OR of two mutually exclusive conditions, each on indexed columns.

SELECT *
FROM   t1
WHERE  user_id = 1
OR     location_id = 2;

In mySQL there is a feature called an Index Merge that, as the name suggests, runs a scan of the two indexes and merges the results together. Kinda neat.

I guess it’s not disimilar to what Oracle does with bitmap conversions, but it looks a little less scary in the middle of a massive plan than something like this. :).

-----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  |    20 |   260 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | T1               |    20 |   260 |     6   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |                  |       |       |            |          |
|   3 |    BITMAP OR                     |                  |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | T1_LOCATION_ID_I |       |       |     1   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|                  |       |       |            |          |
|*  7 |      INDEX RANGE SCAN            | T1_USER_ID_I     |       |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

As it turned out, the query I was tuning was too complicated to take advantage of an index merge, so I tried converting it to use a UNION ALL. Remember, the conditions are mutually exclusive, so there was no need to worry about duplicates. Taking the example of the previous query, it would look something like this.

SELECT *
FROM   t1
WHERE  user_id = 1
UNION ALL
SELECT *
FROM   t1
WHERE  location_id = 2;

The real query I was tuning performed much better with a UNION ALL, but I thought I better check to see if mySQL had any gotchas associated with UNION and UNION ALL. It turns out that both UNION and UNION ALL can create internal temporary tables to get the job done. I suppose it’s not that different to Oracle doing sorts in memory or pushing them out to disk, but it did kinda freak me out when I first read it. 🙂

Although I could never see myself devoting that much time to another engine, it is quite interesting seeing how they get the job done.

Cheers

Tim…

Don’t be afraid to ask…

I didn’t come from a computer science background, so when I started working with computers I spent a lot of time asking questions. I knew nothing, so there was nothing to lose by asking others for directions. As my knowledge progressed I found myself less able to ask questions of others because of pride. I didn’t want people to know I didn’t know the answer. It took a few years before I was confident enough to say, “I don’t know”, and ask others for their opinions again. Looking back at it you have to laugh…

I was reminded of this issue recently when someone asked me some questions about storage best practices. The first thing I suggested was to speak with the storage vendor and ask what recommendations and whitepapers they have. Googling aside, this seemed like a pretty reasonable starting point to me. Their response was essentially, “I don’t want to look stupid in front of the vendor”. Sound familiar?

I’ve been there and know exactly how it feels. Hell, it’s still happening (here). Swallow your pride and start asking questions again. You’ll be surprised how well people respond when you do. Sometimes because you are stroking their ego, but mostly because they are genuinely interested in the technology and like to chat about it. We are geeks and geeks love to talk about geeky stuff.

Of course, remember to look for information from credible sources and always test what you’re told. If you can’t prove it, it isn’t real. 🙂

Cheers

Tim…

PS. As a result of the storage discussion I knocked up a quick overview of a couple of performance measuring tools from Oracle (here).

The Twilight Saga: Eclipse…

I went to the cinema with one of my friends, who is about 29 weeks pregnant. Needless to say it was her decision to watch Eclipse. It is being promoted as the “best so far” and the most “boyfriend friendly so far”, which are both true statements, but don’t let that fool you. It is complete toilet.

Pros:

  • The actress playing Victoria has been replaced by Bryce Dallas Howard. That’s not to say the previous actress was bad, but that I think BDH is hot.
  • The wolves are very cool. In one scene the dopey lead girl is standing next to Jacob when he’s in wolf form and he’s as tall as her. My immediate reaction was to think of Mouse from The Dresden Files. I sat there for a few minutes thinking how cool it would be to have a massive supernatural dog. It made the film a little more bearable.
  • There were some fight scenes, but they were a lot shorter and more anti-climactic than the trailer would have you believe.
  • I got the impression that Taylor Lautner was constantly trying not to laugh. I guess when your role in the film is “Hot guy that never wears clothes” it’s kinda hard to take it seriously. It made the film less annoying to think that perhaps the cast realize the film is total crap too.

Cons:

  • It’s crap.
  • Robert Pattinson can only do two expressions. For the majority of the time he does an expression that I like to call “coma”. When it comes to scenes that require some emotional content he switches seamlessly to “constipated”. I think he’s got a long career in hospital dramas playing coma patients or in constipation remedy adverts on TV.
  • Kristen Stewart is one down from Robert, as she only has one expression, which I will call “botox”. I suppose really it is a lack of expression. Perhaps this is the best acting in history, as I don’t think any of the greats could portray “expressionless” so consistently over 3 films. I think she should focus on “constipated” then she can join Robert in his future career.
  • Although this film is definitely the best of the lot, it hasn’t had much competition. It is bad.
  • Did I mention it is crap?

Interestingly enough, my pregnant mate thought the film was OK, but preferred Predators. 🙂

Cheers

Tim…