Getting Started With Oracle Week: Aggregating

Oracle
11 Comments
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,…
Read More

Getting Started With Oracle Week: Joins

Oracle
4 Comments
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

Oracle
11 Comments
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: Working With Strings

Oracle
9 Comments
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,…
Read More

Getting Started With Oracle: Date Math

Oracle
16 Comments
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

Oracle
6 Comments
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

Which Tables are Being Used in Oracle?

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

Managing Oracle Performance with Statistics

Oracle
4 Comments
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

Oracle
2 Comments
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

Oracle
3 Comments
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

Oracle
7 Comments
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
0
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