When preparing for the the IOUG Collaborate 12 deep dive on deploying Oracle Databases for high Availability, I wanted to provide some feedback on what hardware components are failing most frequently and which ones are less frequently. I believe I have reasonably good idea about that but I thought that providing some more objective data would be better. I couldn’t find and results of a more scientific research so I decided to organize a poll. This blog post shows the results and I promised to share it with several groups.
… 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.