-
Performance tuning
I am little bit confused with oracle behavior about a query execution plan. My table is having 1000000 records. Table name is TEST_NORMAL. One column name is SAL number(6).
There are 6001 distinct salaries into this table. So this column has been indexed by Bitmap Index.
I wrote two different queries To finding third highest sal.
Query 1:-
select a.sal from test_normal a where
3=(select count(distinct b.sal) from test_normal b where b.sal>=a.sal)
Execution Plan of Query-1
COST CARDINALITY BYTES
SELECT STATEMENT, GOAL = CHOOSE 404 10000 30000
FILTER
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX FAST FULL SCAN SCOTT NORMAL_SAL_DX
SORT GROUP BY 1 3
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX RANGE SCAN SCOTT NORMAL_SAL_DX
Query -1 Result time is 576 secs.
Query 2:-
select tn.sal,tn.ename
from (select sal,ename,dense_rank() over (order by sal desc) "DRANK" from test_normal) tn
where tn.DRANK = 3
Execution Plan of Query-2
COST CARDINALITY BYTES
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
Query -1 Result time is 3.5 secs.
It is quite visible that there is very high cost, cardinality and bytes into Query-2 , but still this query is much faster than query-1. Why?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|