sql 11g

Oracle11g SQL Lab Guide

OBIEE11g Building Logical Dimension Hierarchy

OBIEE11g Building Logical Dimension Hierarchy

OBIEE Docs

Calculation Manager

Usage Tracking inOBIEE

How to Install OBIEE Usage Tracking < 20 min from ArtOf BI on Vimeo.

Meta Data creation in OBIEE

Useful for OBIEE  Bigners
  

Metadata Creation In OBIEE

View more presentations from iWare Logic Technologies Pvt. Ltd.

Data Warehouse concepts

Data warehouse concepts

View more documents from obieefans

Basic in OBIEE

Building Oracle BIEE (OBIEE) Reports, Dashboards

View more presentations from iWare Logic Technologies Pvt. Ltd.

Ronum

                                             The Magic of ROWNUM             

The “ROWNUM greater than” query never fails to have an eye-popping effect  the first time anyone sees it. If you haven’t worked with ROWNUM much before, be prepared!

. What is ROWNUM?

ROWNUM is a pseudocolumn, assigning a number to every row returned by a query. The numbers follow the sequence 1, 2, 3…N, where N is the total number of rows in the selected set. This is useful when you want to do some filtering based on the number of rows, such as:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SQL> -- Rownum to limit result set SQL> -- to three rows only SQL> select empno   2       , ename   3       , sal   4       , rownum   5  from emp   6  where rownum < 4;      EMPNO ENAME        SAL     ROWNUM ---------- ---------- ----- ----------       7369 SMITH        800          1       7499 ALLEN       1600          2       7521 WARD        1250          3

So out of those three rows if I want to select only rownum = 2, this should work. Ri

1 2 3 4 5 6 7 8

-- Query attempt to select row -- with rownum = 2 select empno      , ename      , sal      , rownum from emp where rownum = 2;

Run it on SQL.

1 2 3 4 5 6 7 8 9 10

SQL> -- Query attempt to select rows SQL> -- with rownum = 2 SQL> select empno   2       , ename   3       , sal   4       , rownum   5  from emp   6  where rownum = 2; no rows selected

What just happened?

Why did adding rownum = 2 return no resultsHow ROWNUM Works

Here is the secret. Rownum values are not preassigned, they are determined on the fly, as the rows are output. The common misconception is that every row in the table has a permanent ROWNUM. In truth, rows in a table are not ordered or numbered – you cannot ask for row#5 from the table, there is no such thing.

The pseudocode for a query using rownum is:

rownum = 1
for x in ( select * from query)
loop
    if ( x satisfies the predicate )
    then 
          output the row
          rownum = rownum + 1
    end if;
end loop;

The first selected row is always assigned rownum = 1, and is tested against the predicate. When the test is "< 4", rownum = 1 passes the test and the rownum is set to 2, and so the loop continues. The first 3 rows pass the test and get printed out, till rownum becomes 4 and fails the test.

When the test is "= 2", the first row itself does not pass the test (since it is rownum = 1). The increment never happens and no rows get printed.

All of which explains why the WHERE condition can only filter on what rownum is less than, not what it is great than.

Summary

ROWNUM is a pseudocolumn that assigns a number to every row returned by a SQL query. It can be of great use in filtering data based on the number of rows returned by the query.

ROWNUM gets its value as the query is executed, not before, and gets incremented only after the query passes the WHERE clause. Therefore, your WHERE condition can filter data based on "rownum < 2/3/4/." but not "rownum > 2/3/4.". The second filter will invariably return no rows selected.

                     DUAL Table

DUAL is a special one-row, one-column table in Oracle’s data dictionary.

what use is DUAL?

DUAL comes in handy when you want to select just one row through a query. Oracle SQL structure requires you to have a FROM  clause, but some queries don’t need a table – if you want to know the current system date, for example, or the answer for (3+1)*5. DUAL is useful for queries you’d write for such cases:

select user from dual;

select (3+1)*5 from dual;

But why DUAL? Won’t any table with a rownum < 2 filter work equally well?

Yes, why not simply:

select user from scott.emp where rownum < 2;

This query gives you the same result but DUAL has an edge over any other table in the query – the Oracle optimizer recognizes DUAL as a special table and prepares the best execution plan for it.

What will happen if I insert more rows or delete from DUAL?

The Oracle optimizer trusts DUAL to have exactly one row – no more, no less. If you mess up its data you can lead to unpredictable behavior in the database. Don’t try it!

If DUAL is supposed to have only one row, why is it called DUAL? Why not SINGLE?!

According to this old article on the history of Oracle’s DUAL table, DUAL was originally not meant to be seen itself but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. In that context, the name DUAL seemed fine.