Performance tuning

My Comments:-

1) If we remove bitmap Index the performance becomes more poor.
2) If we take funda of rownum, that again fails in case of duplicate SAL.

Here my idea is that Oracle has released analytical functions for such reasons and in such cases where our query is ranking table itself records we always go for these functions for better performance. What you all say?

nope sort operation is a sort irrespctive of you use analytical functions or not.. post the plan for case 2

Plan for Case -2 (Dense Rank)
SELECT STATEMENT, GOAL = CHOOSE 7138 1000000 43000000
VIEW SCOTT 7138 1000000 43000000
WINDOW SORT PUSHED RANK 7138 1000000 33000000
TABLE ACCESS FULL SCOTT TEST_NORMAL 943 1000000 33000000

Here I am also attaching trance file of level 8.
The query 1 was not finishing ... so attaching incomplete trace file.

Quote:

---

My Comments:-

1) If we remove bitmap Index the performance becomes more poor.
2) If we take funda of rownum, that again fails in case of duplicate SAL.

---

i meant this case 2...

Ok,
This plan is after removing the bitmap index.
------------------------------------------------------
SELECT STATEMENT, GOAL = CHOOSE 88218608 50000000000 1500000000000
MERGE JOIN 88218608 50000000000 1500000000000
VIEW SYS VW_SQ_1 88214895 50000000000 1000000000000
FILTER
SORT GROUP BY 88214895 50000000000 650000000000
MERGE JOIN OUTER 6503 50000000000 650000000000
SORT JOIN 3713 1000000 10000000
TABLE ACCESS FULL SCOTT TEST_NORMAL 943 1000000 10000000
SORT JOIN 2790 1000000 3000000
TABLE ACCESS FULL SCOTT TEST_NORMAL 943 1000000 3000000
SORT JOIN 3713 1000000 10000000
TABLE ACCESS FULL SCOTT TEST_NORMAL 943 1000000 10000000
--------------------------------------------------------------
I believe that rownum concept will not work out, that understood..

Quote:

---

I believe that rownum concept will not work out, that understood

---

and why is that now?

Code:

---


  select sal from
    (select sal, rownum sal_rank
        (select distinct sal from test_normal)
    order by sal desc)
  where sal_rank = 3;

---

Rgds
Abhay.

No, This will not work out my dear If I want thrid highest Sal.
But Yes, if I need to find out third lowest sal.
I am attaching inner query results, That has more clearance.

This Query will give the exact result.
------------------------------------
select min(sal) from
(select sal, rownum sal_rank
from (select distinct sal from test_normal)
order by sal desc) where rownum < 4

Quote:

---

Originally Posted by chitwan_humad

No, This will not work out my dear If I want thrid highest Sal.
But Yes, if I need to find out third lowest sal.
I am attaching inner query results, That has more clearance.

---

Well yes, the order by clause should come inner most then it would work.

but what i was looking is at the performance angle not the query result.. you should obiviously do some homework for any correction..

rgds
abhay.