[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Oracle queries sometimes are awfully slow, why??

Posted on 2003-11-06
Medium Priority
Last Modified: 2008-01-09
Hi all,

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 .

Anyway the 'select count(*) from MYTABLE' takes 17 second as well. Maybe some Oracle tuning should help.(?)
Question by:fifty_
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2

Expert Comment

ID: 9694989
Have you tried to run an explain plan on your query?  What was the outcome?
LVL 23

Accepted Solution

seazodiac earned 172 total points
ID: 9695115
ok, try these steps:

1. make sure your query is very selective (i.e., the rows in the results should be less than 5% of the total rows 3millions)
2. there is another caveat with BITAND function. BITAND returns a binary integer, so to use it in SQL you will need to convert it to a numeric.
That means when you are using BITAND function in where clause like this:
BITAND(fp2,1689182963) = 1689182963, oracle has to do some implicit data conversion(from binary integer to a numeric). to make it more efficient, in your where condition, explicity convert it by using this : BITAND(fp2,1689182963) + 0 = 1689182963,

So for you to speed up the query, you should Build a FUNCTION-BASED index on all the fp columns like this:

CREATE INDEX fp1_mytable_ix ON mytable (BITAND(fp1) +0);

of course, in case you don't know already, you need to make sure the following things are done before you use function-based index:

You must have the system privelege query rewrite to create function based indexes on tables in your own schema.
You must have the system privelege global query rewrite to create function based indexes on tables in other schemas
For the optimizer to use function based indexes, the following session or system variables must be set:

LVL 15

Expert Comment

ID: 9695399
Seazodiac, I don't understand what you just said: If BITAND returns a binary_integer which Oracle will implicitly convert, how does changing to BITAND(...)+0 make any difference?  Surely Oracle will still do the implicit conversion either before or after it adds the 0?  In any case, why would an explicit conversion (e.g. using TO_NUMBER) be faster than an implicit one?
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

LVL 23

Expert Comment

ID: 9695622
I think it does get better speed when you do things explicitly.Especially, this explicit conversion is  embedded in the function creation. that's why Oracle encourage you to do things explicitly.

one example:

oracle does recommend the use of " select * from emp where hiredate < to_date('10-OCT-03', 'DD-MON-YY')" , NOT " select * from emp where hiredate < '10-OCT-03'"
LVL 15

Expert Comment

ID: 9695816
Yes, but that is because you are more likely to get errors if you are not explicit.  to_date('10-OCT-03') would be just as bad.  Whether implicitly or explicitly that conversion IS going to be done and will take however long it takes.

OK, there is possibly a small PARSE penalty for implicit conversions: Oracle has to decide how to treat the values.  But parsing overhead is insignificant unless you are actually parsing 1000s of times.

I feel a benchmark test coming on.  Maybe tomorrow...
LVL 23

Expert Comment

ID: 9695871

Andrew, I can confirm that because I meant to explain exactly the same thing (penalty in the parse stage) last time, but I don't want to turn it into a long-winded story.


Expert Comment

ID: 9696452
I do not think the implied vs explicit conversion is the issue here but makes for a nice diversion.  As stated you need to look at the selectivity of the data, probably a FTS is more efficient.  Is this query going to be run often?  I would not be adding all the indices if not.  You also have to consider the DML implications on adding these indices on a heavy DML table.  Consider your requirements and implications thereof.


Assisted Solution

grim_toaster earned 164 total points
ID: 9699696
As a quick point on the parsing issue, wouldn't it be more efficient to only convert the passed in value to an Integer in the equals thing if you're worried about it?  As in:
BITAND(fp1, 1689182963) = CAST(1689182963 AS INTEGER)

Also, do you really need the bitand (I may be completely mistaken here)?  Couldn't you just use
fp1 = 1689182963
And then put a single index joining on all of the columns?  Therefore doing a single index scan instead of what would appear to be a FTS (calling a function on a column, without a function-based index, would force it to the full table scan).

Author Comment

ID: 9699721
Maybe the BITAND function is a little bit slow.

Is it possible to use more cache for this SELECT statement ? It would be much faster if the fp columns were in the cache.
LVL 15

Assisted Solution

andrewst earned 164 total points
ID: 9700144
I think this is the most significant point:

"Anyway the 'select count(*) from MYTABLE' takes 17 second as well"

So we know that the query with all the BITANDs is doing a full table scan, but at least it is not taking any longer due to the BITANDs.  So:

How many of the 3 million rows does the BITAND version return?  If it is a very small proportion, an index would probably help.  This could be on just one of the columns or many.  It would need to be a function-based index due to the BITANDs - unless, as grim_toaster suggest, you don't actually need the BITANDs.

On the other hand, if it returns a higher proportion of the rows there may be no benefit to using an index.  In that case you are left with the fact that you must use a full table scan, and that currently takes 17 seconds.  Can that be reduced?  Not by query tuning, for sure.  The first thing to check would be whether the data is stored efficiently.  For example, if at one time there were 100 million rows and 97 million had been deleted, the full table scan would read all those empty blocks as well as the blocks that contain the 3 million rows.  

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question