Prime example showing benefits of Analytical Functions

On the topic of analytical functions, I will be the first to say that they are not my strength. However that said, reading one article after another I am increasingly aware of what I am missing.

I stumbled upon an excellent article demonstrating significant performance gains across the board from timing to resource utilization, all due to the use of analytical functions.

I encourage you take some time and review the posting.

Analytic Functions: A Savior by ASIF MOMEN

IETester: Develop/test your site in IE 6, IE 7 and IE 8 RC1


by Emiel Paasschens

"When developing or testing your web application, you often have to support different browsers and versions. On Window machines testing for different versions of Firefox, Opera and Chrome is not a real problem as these browsers all allow you to install different versions on one machine. Only Internet Explorer (IE), is always a bit more difficult, because it’s integrated in the OS (Windows) of the machine. This mostly ends up installing multiple IE versions on multiple (virtual) machines, which is not a nice way of working when developing. Can we do this different and easier? Yes, we can!

The solution is quite easy and even easier then having multiple version of Firefox, Opera or Chrome on your machine.
How? Just try IETester. This is a free web browser that emulates IE5.5, IE 6, 7 and 8 RC1 in one browser. You can even open multiple IE 6, IE 7 and/or IE 8 tabs at the same time!"

Virtual Columns in 11g


"Oracle has supported stored expressions for many years, in views and function-based indexes. Most commonly, views enable us to store and modularise computations and expressions based on their underlying tables' columns. In more recent versions (since around the 8i timeframe), we have been able to index expressions using function-based indexes. Now, with the release of 11g, Oracle enables us to store expressions directly in the base tables themselves as virtual columns.

As we will see in this article, virtual columns are more flexible than any of their prior alternatives. We will examine their basic usage and also consider some of the performance aspects of the new feature."

Count number of character occurences in a string

Sourced from := [email protected]

A question was posed on the listserver regarding the 'how' of identifying how many vowels are in a particular string of text. Not certain as to the purpose of same, however intresting nonetheless. For those on 10g+, use of the REGEXP% syntax comes into play.

v_str varchar2(200):='RAJEEV HERE from lucknow' ;
vn_vowel number;
SELECT length(v_str)- length(REGEXP_REPLACE(v_str,'[a,e,i,o,u,A,E,I,O,U]',''))
into vn_vowel FROM DUAL;

For those on versions prior to 10g, this method was proposed that provided the same result.

select length(COLUMN_NAME) - length( translate(lower(COLUMN_NAME),'zaeiou','z')) from TABLE_NAME ;

Using Analytic Functions in Oracle

Provided by Chaitanya Susarla <chaitanyasusarla at>

This article is just to show some quick yet simple examples of how to use analytic functions in ORACLE. Though It doesn't cover all the analytical functions available, I am just focussing on trivial functions we use in daily sql queries and to understand the way analytics work for them.

All the following examples are tested on ORACLE Version

What are analytic functions?

(From Oracle documentation of 10g Release2(10.2))


Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.

Do not worry If you don't understand the defintion from Oracle documentation. For any first timers, it is tough to grasp it quickly. After going through the following examples, probably you will have idea of what they are, so that you can revisit the definition and/or URL once again to get complete understanding of how they work and what they are meant for.

Now let us start.....

Pages: 1 · 2 · 3 · 4 · 5 · 6 · 7 · 8 · 9

Identify missing dates in a table

You have a 100,000 row table with lots of data over the course of a year. You know that ideally you should have a number of transactions for every day of the year. However, you are not sure if you have any gaps of time. How can you find the gaps of time ?

Lots of ways to slice it, but here is how I would do it ...

Pages: 1 · 2 · 3

Father of MySQL leaves Sun and starts up MariaDB Project

I recently read an article that the Author of MySQL, who was working for Sun, left the company due to a disagreement of where Sun was leading MySQL. I know this site is primarily geared towards Oracle, however it is DB related for certain, and interesting news to say the least.

He has spawned off a parallel DB called MariaDB. According to the site, the intent is to keep development in parallel to MySQL but extend the functionality.

From indications, all the standard MySQL syntax and APIs will continue to work. Not sure how this might impact the use of MySQL or possible inclusion of MariaDB in the future for my ISP, but I guess we will see.

For the departure article

Time to Move On

For info on MariaDB

MariaDB Project

Differences between MySQLDB and MariaDB

Maria vs MySQL