Tuning: Min and Max in same query

Please read this article.

Karen explains why having min and max in the same query impacts performance and how to tackle the same.

NULL and NOT IN (Oracle)

Here is a very interesting scenario.

I have an employee table called EMP.

And I have a DEPT table.

Now, I write a query to find out all the employees who are part of any department. (DEPT_ID is properly updated and found in DEPT table)

Now, I want to know if any employee is not part of any department.

What??? No results? I know that employee with EMP_ID 9 doesn’t belong to any department. Where did he go?

Is Oracle drunk? Is it giving me wrong results? Wait let me check with another query.

Read the rest of this entry »

Oracle analytic functions

I recently purchased O’Reilly’s SQL cookbook. Its good. I came to know about the Oracle Window functions (analytic functions).

Some examples are
1. row_number() over()
2. dense_rank() over()
3. max() over()
4. Sum() over()
5. Lead() over()
6. Lag() over()

See here for a detailed explanation.

Check this example

Now, you have the trade_val column. But you want a running count of the same. How do you achieve it? See the query below. I have used the sum() over() function to get the sum. But hey! Its wrong. Its giving me the entire total and not the running total.

Yes, since I didnt specify anything in the over() section, it gave an entire total. So now I say order by stk_nam_key, date_key so that the running count is done as per the original order. See results below.

But hey! Again there is something different I need. I need the running count for each stk_nam_key and not for the entire table. See in the upper query, I have indicated with red arrow. When the stk_nam_key changed to 2, it gave me the running count. But I want to start fresh for every new grouping.

So what do I do? I say over(partition by stk_nam_key order by stk_nam_key,date_key). Check the results now.

Dont get confused by the first value for the new grouping 3. The date_key for both 3’s are same and hence the sum is same for both.

Transposing rows to columns

Today, I saw a question in an orkut SQL forum where the guy wanted to transpose rows to columns.

I replied to it.

Let me explain about it.

See this snapshot. I do a query of my test2 table. There are 2 rows. And the 4 weekly data has to be extracted into single column.

See, there are 4 columns that need to be turned into 1 column in 4 rows. So we need to do a cartesian join 4 times. So I use this table below.

See the cartesian join done and the results. See that each ord_no appears 4 times.

Now, using decode, I am getting only what I need.

Pagination

Have you tried any query like this?
select * from time_dim where rownum between 1 and 5
What will be the answer?

Okay, what will be the answer for this one?
select * from time_dim where rownum between 2 and 5

Surprised? Rownum will return any row only if it starts with 1. Everything else will not return anything.

Okay, I guess you knew this part already. Then try this query.
select * from time_dim where rownum between 1 and 5 order by time_key asc

Do you think you will get the first 5 ranks ordered by time_key? Try it. You didnt. You got some 5 records ordered by time_key, not necessarily the top 5.

Why? Because rownum is a pseudo column that is assigned to rows that are fetched. The first 5 records that got fetched were returned with order by on time_key.

Then how do you make sure that you get the first 5 only?

For that you need something called pagination. Try this query
select * from
(
select rownum as rank, a.* from
(
select * from time_dim order by time_key
) a
) b
where rank=8

Here you passed ordered the data from the query in a sub-query. Then passed it to the outer one with the rownum and gave it an alias. Then you used it in the outer query to get that rank.

It is necessary to first order the data and then pass it to an outer query to use the rownum. If you use rownum from the same query where you ordered, it will not be ordered correctly.

Fetch data that fall in a rank range

This query returns all the rows that fall in a certain rank range. Please note that the second condition is rnum and not rownum. If you use rownum, nothing will be returned.

select * from
( select a.*, rownum rnum from
(select * from expense_fct where expense is not null order by expense desc) a
where rownum <= &upperbound )
where rnum >= &lowerbound