… which, for those in the know, means something to do with SQL Server. (The closest physical feature in Oracle is the index-organized table, the closest sounding name for an Oracle feature is the index cluster - which, just to add to the confusion, must have a specific index that is called the cluster index).
Redgate has arranged for an online debate between an Oracle specialist (me) and a SQL Server specialist (Grant Fritchey) to talk about the strengths and weaknesses of the two mechanisms, discuss why Oracle users seem to be biased towards one implementation and SQL Server users towards the other, and then see where the conversation takes us. This will be followed by a Q&A session.
The framing clause brings the "window" to window functions. Through it you define a window, or frame of reference if you prefer, that slides along a result set generating values such as running totals and moving averages. The framing clause is ideal when you can arrange a business question such that an answer comes from applying an aggregate function to a range of rows that slides or stretches smoothly as focus moves from one row to the next.
New functions introduced in SQL Server 2008 Release 2 let you assign rankings to rows in a result set. These functions are ROW_NUMBER, RANK, and DENSE_RANK. Look to them anytime you are faced with a business question involving words or phrases such as "topmost" or "bottommost", "top N" or "bottom N", or that is otherwise answerable by ranking the rows in a result set according to some criteria that you can apply to one or more columns of data.
Articles #1 and #2 in my series on window functions introduce their core functionality by showing how to create unordered and then ordered windows of rows over which aggregate and analytic functions are applied. The articles drive from business cases, helping you recognize when and how to apply window functions to generate business results. In this third article, I step back and take a detailed look at the underlying mechanics of these functions and how they are executed.
SQL is traditionally one-dimensional. This is in the sense that scalar expressions within SELECT and other statements have been limited to values in the current row only. You had access to the horizontal, but not the vertical. SQL Server 2012's support of LAG and LEAD and other so-called analytic functions open up the vertical dimension, giving access to all values in a column. LAG and LEAD and related functions are especially useful in working with data across time.
SQL Server 2012 – the "Denali" release – brings improved support for window functions as defined in the SQL standard. This is the first in a series of articles on using these functions. They help elegantly solve a great many, formerly difficult query problems. One of the most common use cases is to mix or compare summary and detail data in the same row.
If you are a director, manager or project manager who works with DBAs, you probably have had the nagging suspicion at one time or another that a DBA’s assertions regarding his or her practices lack an empirical or scientific basis, or are simply deflections intended to pass the buck.
Manager: Mr. DBA, the application is really slow. Do you have any idea what’s wrong?
DBA: Oracle is very complex. It could be any of 100 different possible causes. I will begin checking each. Anyhow, what makes you think it is the database?
In our Remote DBA practice, we frequently perform comprehensive system reviews for our customers on their database services. Among the things we always check for are non-default settings for the database software. We want to validate that any non-default setting is set that way for a good reason, and that any setting that is default really should be that way.
In Oracle, this is easy. The
gv$parameter view has a column,
ISDEFAULT, that allows a simple SQL query to show which settings are set to non-default values.
It’s not so easy in SQL Server. There is a view, master.sys.configurations, but it doesn’t have a way to tell if the setting is default or modified or anything.
Interesting question on human mistakes was posted on the DBA Managers Forum discussions today.
As human beings, we are sometimes make mistakes. How do you make sure that your employees won’t make mistakes and cause downtime/data loss/etc on your critical production systems?
I don’t think we can avoid this technically, probably working procedures is the solution.
I’d like to hear your thoughts.
I typed my thoughts and as I was finishing, I thought that it makes sense to post it on the blog too so here we go…
The keys to prevent mistakes are low stress levels, clear communications and established processes. Not a complete list but I think these are the top things to reduce the number of mistakes we make managing data infrastructure or for that matter working in any critical environment be it IT administration, aviation engineering or medical surgery field. It’s also a matter of personality fit – depending on your balance between mistakes tolerance and agility required, you will favor hiring one individual or another.
Regardless of how much you try, there are still going to be human errors and you have to account for them in the infrastructure design and processes. The real disasters happen when many things align like several failure combined with few human mistakes. The challenge is to find the right balance between efforts invested in making no mistakes and efforts invested into making your environment errors-proof to the point when risk or human mistake is acceptable to the business.
Those are the general ideas.
Just a few examples of the practical solutions to prevent mistakes when it comes to Oracle DBA:
Some of the items to limit impact of the mistakes:
Both lists can go on very long. Old article authored by Paul Vallee is very relevant top this topic — The Seven Deadly Habits of a DBA…and how to cure them.
Feel free to post your thoughts and example. How do you approach human mistakes in managing production data infrastructure?