Oracle queries sometimes are awfully slow, why??
Posted on 2003-11-06
I have a table with 3 million rows. The table has an id column and 16 fp columns. All columns are indexed. The types of the fp columns are INTEGER.
I perform the following query:
select count(*) from MYTABLE where BITAND(fp1,2144163094) = 2144163094 AND BITAND(fp2,1689182963) = 1689182963 AND BITAND(fp3,180991820) = 180991820 AND ... ... ... AND BITAND(fp,-2023498656) = -2023498656;
So I use BITAND on all fp (INTEGER type) colums.
This query takes 17 second to perform. Is it possible? Is the Oracle's BITAND function too slow or the integer type of the oracle? Should I use RAW type?
I have a P4 2.4G , 2 GB RAM, Win XP, Oracle 220.127.116.11 .
Anyway the 'select count(*) from MYTABLE' takes 17 second as well. Maybe some Oracle tuning should help.(?)