Oracle

This is not a deep dive If you’re looking for lots of internals and explanations of what happens behind the scenes, don’t read past here. I almost made a READPAST joke. It’s that kind of day. This is just a basic overview of creating some indexes and gathering statistics. Why? Because someone just paid about…

We’re not so different, you and I In any database platform, you’ll have to deal with NULLs. They’re basically inescapable, even if you own an island. So let’s compare some of the ways they’re handled between Oracle and SQL Server. Twofer If you take a look at the two queries below, there are a couple…

I probably should have written this one first Most of these are exactly the same as in SQL Server. There are a whole bunch of interesting analytic functions, most of which should look pretty familiar to anyone who has spent time querying SQL Server. Most, if not all, can be extended to be window functions,…

Oh, THAT relational data Thankfully, most major platforms (mostly) follow the ANSI Standard when it comes to joins. However, not all things are created equal. Oracle didn’t have CROSS and OUTER APPLY until 12c, and I’d reckon they’re only implemented to make porting over from MS easier. It also introduced the LATERAL join at the…

Bake your own cake Pre-cooked example databases are cool for a lot of things. The first being that everyone can have access to them, so they can follow along with your demos without building tables or inserting a bunch of data. If you mess something up, it’s easy to restore a copy. The main problem…

Math is math is math I haven’t found any tremendous differences working with numbers between Oracle and SQL Server. Both offer pretty standard functions to calculate your calculations. Oracle has MOD(), SQL Server uses %. This is likely something you’ll want to be aware of if you’re working cross-platform, but nothing earth shattering. One really…

The almighty string It’s so good for holding all sorts of things. Chicken roulade, beef roulade, salmon roulade. It’s also the way you should store phone numbers. If I could go back in time to when I first started working with SQL, that’s what I’d tell myself. Stop. Just, please, for the love of Codd,…

As soon as you store a date value Someone is going to want to do something with it. Bucket it into a 30/60/90 day window, figure out how many times something happened between it and current, filter on it, or send you creepy anonymous cards on your birthday full of doll hair and coupons for lotion.…

Contrary to popular belief You will not burst into eternal flames if you’re a SQL Server guy or gal, and you happen to be within 100 feet of an Oracle database. You might feel lost and confused for a while, but you probably felt the same way when you opened up SSMS for the first…

Many databases have the ability to create automatically incremented numbers. In SQL Server, we can use an IDENTITY property on a column to get autoincrementing numbers. But what can do we in Oracle? Sequences Sequences work in all recent versions and editions of Oracle. The default way to do this is pretty simple: Transact-SQL CREATE…

When we’re making changes to a database, it’s important to know which tables and indexes are in use. If we don’t need an index, maybe we can drop it. If nobody is using a table, do we still need it? We can find out which tables and indexes are in use through two different mechanisms.…

Just because it’s plain text, that doesn’t mean it has to be ugly. Join Jeremiah Peschka and learn the basics of formatting Oracle SQL*Plus output.  Kendra says: that ability to eliminate repeating values is pretty rad!

Oracle maintains statistics about data stored on disk. The statistics and histograms help guide the optimizer during query plan compilation. These statistics are usually helpful. Sometimes the statistics are not helpful. As data changes, the statistics collected by Oracle may become less accurate. If I’ve created a histogram on the column transaction_date, my histogram won’t…

Databases uses statistics about data to determine the best way to query tables. Should the database seek or scan? How many rows will be returned from tables that are being joined? Oracle is no exception, but the way Oracle treats statistics is different from SQL Server. Oracle Statistics Collection Just like SQL Server, Oracle can…

Or, “Oops, I didn’t mean to do that.” We’ve all had to deal with an accidental deletion or even a dropped table. Sometimes you leave off a where clause, sometimes you drop a table and find out that the change request listed the wrong table. Oracle uses a feature called Flashback to give database professionals…

Knowing what a query has been waiting on helps you figure out where you need to tune that system. Oracle’s wait interface provides a significant amount of information to help a DBA decide where to put tuning resources. There are multiple ways to see what Oracle has been waiting on: Right now: v$session_event For all time: v$system_event…

Oracle has different availability and recovery options from SQL Server. Being aware of what these options are and how you can use them will go a long way toward keeping your Oracle boxes just as safe as your SQL Server boxes. Here’s my 17-minute video explaining it, or you can keep reading and get your…