Eating An Elephant – How To Work With Huge Datasets In Oracle And SQL Server

Simplifying complex problems is difficult but when done, the rewards are definitely worth it. How I single-handedly managed to migrate 55 Billion rows in 4000+ tables: Last year, I created PowerPump a PowerShell based data copy server which continuously migrated 4000+ Oracle tables to SQL Server. It was no simple task given that I had … Continue reading Eating An Elephant – How To Work With Huge Datasets In Oracle And SQL Server

Oracle System & Session Wait Big Picture Analysis- Using RATIO_TO_REPORT Function

Numbers and meaning it comes to numbers, the easiest way to get a point across to majority of the people is to express something in terms of percentage. Let us consider an example where a query produces the “waits” Oracle experienced overall in terms of “Number of waits” and the “Time waited”. This produces: WAIT_CLASS … Continue reading Oracle System & Session Wait Big Picture Analysis- Using RATIO_TO_REPORT Function

Oracle ROWNUM vs. SQL Server TOP – Differences & How To Do Pagination In Both

Sometimes we carry knowledge around that can hurt when doing similar things especially when dealing with the different programming languages and constructs. Today, let us see how Oracle’s ROWNUM is different from (as in, almost unrelated) SQL Server’s TOP. SQL Server - TOP: Basics Let us take the TOP function in SQL Server. It is … Continue reading Oracle ROWNUM vs. SQL Server TOP – Differences & How To Do Pagination In Both

Oracle/SQL Server – RANK, DENSE_RANK and ROW_NUMBER Analytic Functions – Basic Examples

Note: The illustrations on this post are done with Oracle. However, by making minor changes, you should be able to run it on SQL Server and the concepts remain the same. Please refer this post for changes needed. RANK, DENSE_RANK and ROW_NUMBER are three powerful analytic functions that every database developer should be thoroughly familiar … Continue reading Oracle/SQL Server – RANK, DENSE_RANK and ROW_NUMBER Analytic Functions – Basic Examples

Oracle – LAG & LEAD – Usage Examples + Interesting Use-case

LAG and LEAD are a couple of analytic functions that come in handy in some interesting situations. I am not really sure why I wrote this post but at least it gives you a a few queries to play with LEAD and LAG. I am not completely happy with how this post turned out. Nothing … Continue reading Oracle – LAG & LEAD – Usage Examples + Interesting Use-case