# June, 2010

1

June 25, 2010  9:13 AM

## Analytical way – To find Nth maximum value from a table

Profile: Lakshmi Venkatesh

To find NTH maximum value from a table, we may need to query the same table twice to get the desired output. This will lead to table scans / index scans for the same table twice. The same can be achieved via analytical functions by querying the table once. Have enclosed the execution plan and have...

June 25, 2010  9:12 AM

## Analytical way – Retrieve the Nth row from a table

Profile: Lakshmi Venkatesh

To retrieve Nth row from a table using the usual way it might require to query the same table thrice (can write it in different way also). But, with Analytical function the same can be made to hit only once. Using this kind of analytical functions is particularly useful when the query needs to be...

June 25, 2010  9:12 AM

## Analytical way – Retrieve say 5 to 8 rows from table

Profile: Lakshmi Venkatesh

Non-Analytical way SELECT a.* FROM (select e.*,ROWNUM rnm FROM scott.emp e) a WHERE rnm BETWEEN 3 AND 8; SQL> SELECT a.* FROM 2 (select e.*,ROWNUM rnm FROM scott.emp e) a 3 WHERE rnm BETWEEN 3 AND 8; EMPNO ENAME JOB MGR HIREDATE SAL...

June 25, 2010  9:11 AM

## Analytical way – TOP N rows from a table

Profile: Lakshmi Venkatesh

Non Analytical way SELECT * FROM scott.emp inside WHERE 5 >= (SELECT COUNT (DISTINCT outside.sal) FROM scott.emp outside WHERE outside.sal >= inside.sal) ORDER BY inside.sal DESC; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ----------...

June 25, 2010  9:10 AM

## Analytical way – TO FIND RUNNING TOTAL

Profile: Lakshmi Venkatesh

To find the running total using the non-analytical way minimum the same table needs to be joined twice. Using Analytical functions the same can be achieved with single table hit. In a single query running total can be computed in no time. NON ANALYTICAL WAY...

June 25, 2010  9:10 AM

## Analytical way – TO FIND LAST N ROWS AND DISPLAY ROWNUMBER IN A TABLE

Profile: Lakshmi Venkatesh

To find the last N and display the row number. With the usual way to pick last set of records from any table minimum 3 joins are required. Using analytical functions the same can be reduced to single table hit to retrieve the last N rows and display the row numbers for the same. This is...

June 25, 2010  6:42 AM

## Migrating from 9i to 11g – Collections Enhancements

Profile: Lakshmi Venkatesh

1 Indicies of New feature in Oracle 10g. In FORALL collections.FIRST .. collections.LAST it is not possible to use the indices sequntially if the collection is sparse. But, the same can be handled in Oracle 10g by using FORALL IN INDICES OF...

June 25, 2010  6:19 AM

## Migrating from 9i to 11g – Adaptive cursor sharing

Profile: Lakshmi Venkatesh

Feature available from : Oracle 11g Release 1 Cursor_Sharing enables to notice similar SQL statements that are already parsed and available in SQL area. When the query is issued for the first time, the same is stored in the SQL area. Later, on issuing same / similar SQL...

June 25, 2010  6:06 AM

## Implement ROLLING Partition using INTERVAL partition (11g)

Profile: Lakshmi Venkatesh

1