Getting Started With Oracle Week: Joins

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…
Read More

Getting Started With Oracle: Working With Numbers

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…
Read More

Getting Started With Oracle: Date Math

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.…
Read More

Identity Columns in Oracle

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…
Read More

Managing Oracle Performance with Statistics

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…
Read More

The Basics of Oracle Statistics

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…
Read More

Oracle Flashback: Undeleting Data

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…
Read More

Oracle Wait Events

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…
Read More

Oracle HA & DR Basics [With Video]

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…
Read More