Just so we’re clear, I use SQL Server. I like SQL Server. But, this doesn’t mean I have anything against Oracle. It’s fine. It’s good. But, I know very little about it. However, throughout my career I’ve found myself needing to understand it better. Either because I’m trying to train Oracle people to better use SQL Server and I need to be able to speak a little of their language to facilitate translation. Or, because I’m defending SQL Server on some technical point that the Oracle people don’t completely understand. Or, because I’ve said something stupid about Oracle in my ignorance.
Now, you know how busy you are, and I know how busy I am, so I doubt either of us has the time we really need to learn Oracle much. So, what do you do? Well, Red Gate Software, who straddles the worlds between Oracle & SQL Server like the Bifrost between Midgard & Asgard, has started a series of conversations between two people who know something about each platform, Jonathan Lewis (blog) and me.
We had our first conversation talking about clustered indexes. We covered how they work in both platforms (not that differently) and they’re used and abused. Interestingly enough, according to Jonathan, clustered indexes just aren’t used that much within Oracle, despite the fact that they really do behave mostly the same way as they do within SQL Server, where we use them on most every table (or at least so I maintain you should). It was a great discussion (NOTE: not a fight, no one was nasty or mean, we talked).
We’re going to have another discussion. We’re going to be talking about temporary tables. Again, I don’t know much about Oracle, so please, this is not an attack, but apparently they don’t have the same concept of temporary tables as we do in SQL Server. We’re going to cover a lot of the myths and misperceptions surrounding temp tables on both Oracle and SQL Server, how they work and how they affect performance. I learned a lot during the last conversation and I don’t doubt I’ll learn a lot during this one. If you’re interested, please go to this web page and register.
And, I’d be remiss if I didn’t mention again, if you like learning about performance in SQL Server that you should consider attending the PASS Summit 2012. If you register now, you save $500, which is just enough to pay for my pre-conference seminar, Query Performance Tuning: Start to Finish. I’ll be covering all aspects of performance tuning from gathering metrics to understand which queries are running slow, to reading execution plans to understand why, to addressing the issues to fix the performance and make your queries hum. Please consider taking part. It’ll be a lot of fun and I’ll try like crazy to make it useful.
I use temp tables in Oracle for reporting all the time. The work great but I appreciate SQL Server cleaning up after their use.
And we’re already learning. I wasn’t aware that you had to clean them up manually in Oracle.
Typically in Oracle I don’t clean them up because my stored procedures have to write to them on demand. As you know, in SQL Server we can create these temp tables in the proc and sure you can do it in Oracle too and I tend to write my queries in dynamic SQL because I write them to work for different lines of business and using dynamic SQL avoids compilation problems. Is SQL Server I can drop , create, alter, etc the temp table all I like in my procedure because it is “captiveâ€. Not so in Oracle. That table belongs to everyone but the data in it is segregated by user session. As soon as the user disconnects the data is gone. I can’t drop that table if anyone is using it. Of course that make sense but in Oracle I don’t have the total control I do in SQL Server. Things may be different in newer versions of Oracle but I’m not aware of it.
Ah..Grant, I wish we’d have one of your tuning presentations closer. I’m sure it will be fun.
Anyway, can’t wait to see your debate! Let the challenge begin!
I’ll sure try to get over to Europe for a few presentations. I’ve done some in the UK, which I realize isn’t exactly the same, but it’s closer than the US.
“clustered indexes just aren’t used that much within Oracle, despite the fact that they really do behave mostly the same way as they do within SQL Server”
Is Jonathan talking about Index Organised Tables in Oracle in the above context ?
The Oracle Cluster Index is completely and totally different to a SQL Server Clustered Index:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:586423377841
Probably… still pretty ignorant about Oracle.
Come over to the dark side young Luke.
Here is a really good place to start:
http://allthingsoracle.com/free-ebook-expert-oracle-database-architecture/?goback=.gde_146455_member_109782483
I’m a big fan of Tom Kyte.
Amusingly, I watched that webinar and took notes. Jonathan said that index organized tables are almost the same as SQL Server cluster indexing. Hope that’s helpful.
I hate to disagree with Jonathan, but I think Stephanie is right. I *think* the equivalent of the sqlserver clustered index in Oracle is actually the Index Organized Table (IOT).
The Index Organized Table is widely used (although the Heap Organized Table is the default and more common). Clustered indexes in Oracle are something else, and aren’t much used.
Ever feel like you’re taking crazy pills?
@Alan
No relax, it’s just Groundhog Day (again)!