Home » RDBMS Server » Performance Tuning » Index Efficiency
| Index Efficiency [message #65484] |
Tue, 05 October 2004 06:52  |
Moftah
Messages: 1
Registered: October 2004
|
Junior Member |
|
|
Hi, every body ,
I am new to oracle and my general idea is, index always improve performance or at least will not hurt it but please take a look at these query below. Performance of second query is degraded, when index is created.
==
(SELECT f_or(c0,d0),c1,c2,c3
FROM ta3000,ta30000
WHERE c1=d1 and c2=d2 and c3=d3 and f_or(c0,d0)>0) " +
" UNION ALL " +
" (SELECT f_or(0,d0),d1,d2,d3
FROM ta30000
WHERE (d1,d2,d3)
NOT IN (SELECT c1,c2,c3 FROM ta3000) and f_or(0,d0)>0) " +
" UNION ALL " +
" (SELECT f_or(c0,0),c1,c2,c3
FROM ta3000
WHERE (c1,c2,c3)
NOT IN (SELECT d1,d2,d3 FROM ta30000)and f_or(c0,0)>0) ");
==
This query evaluated in Avg. of 80ms.
Creating an index on ta3000(c1,c2,c3), enhance the performance so the the query evaluated in Avg. of 16ms.
--
NOW, when I issue query like this:
SELECT Max(k0),k1,k2,k3
FROM (
SELECT f_or(c0,d0) as k0, k1 c1, k2 c2, k3 c3
FROM ta3000,ta30000
WHERE c1=d1 and c2=d2 and c3=d3 and f_or(c0,d0)>0) " +
" UNION ALL " +
" (SELECT f_or(0,d0) as k0, d1 k1, d2 k2, k3 d3
FROM ta30000
WHERE (d1,d2,d3)
NOT IN (SELECT c1,c2,c3 FROM ta3000) and f_or(0,d0)>0) " +
" UNION ALL " +
" (SELECT f_or(c0,0) as k0, k1 c1, k2 c2, k3 c3
FROM ta3000
WHERE (c1,c2,c3)
NOT IN (SELECT d1,d2,d3 FROM ta30000)and f_or(c0,0)>0
)
Group by k1, k2, k3
it is evaluated in Avg. time of 6129ms, in case of index unavailability.
When creating an index on ta3000 (c1,c2,c3), I expected that the performance will enhance, or at least the optimizer will ignore the index if it hurts the performance, BUT by creating such index the query need 70625ms in other words the index hurts the performance.
Can any body tell me WHY?
thanx in advance,
Zobi,
Note: the nested query in the second query= the first one.
f_o is a stored plsql function
|
|
|
|
| Re: Index Efficiency [message #65513 is a reply to message #65484] |
Mon, 11 October 2004 05:02  |
farky
Messages: 9
Registered: August 2004
|
Junior Member |
|
|
Hi!
Did you analize your tables?
If yes, use the HASH_AJ hint in the not in clause:
(SELECT f_or(c0,0) as k0, k1 c1, k2 c2, k3 c3
FROM ta3000
WHERE (c1,c2,c3)
NOT IN (SELECT /*+ HASH_AJ*/ d1,d2,d3 FROM ta30000)and f_or(c0,0)>0
)
The index is not always good in a colum(s). You must analyze the selectivity. Your selectivity is good, where it is near 1.
Suppose that you have a table with 10000 records, with 9000 distinct value. The selectivity is 9000/10000 = 0.9 it is pretty good. But if you have only 500 distinct value, the selectivity is 500/10000 = 0,05. It is wrong, and the full table scan is better.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 03:36:39 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
