Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Why does using two indexes slow down my query?

Re: Why does using two indexes slow down my query?

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Wed, 10 Jun 1998 15:32:56 +0200
Message-ID: <6lm1qj$agv$1@hermes.is.co.za>


patrickk_at_mindspring.com wrote in message <6ljb91$e3t$1_at_nnrp1.dejanews.com>...

>I'm having a problem with Oracle that I have not seen in other DBMSs.
<snipped>
>My past expereince with DBMSs (Informix,
>RedBrick, SQLServer) has been that the more narrow I make my constraints,
>the faster the query returns values...I have estimated statistics using
>analyze.I have tried all the optimizer goals.Any help is greatly
>appreciated.

The other responses offer very good advice. My posting is my 2'c worth about why I think you have a problem.

I disagree about the part that "narrowing constraints" provide faster query response. It's not the amount of data that is returned by a query that determines the speed/performance of the query, but the 1) amount of data to process and 2) the number of functions/criteria to apply.

In your case, it's number (2) that applies - you have added an additional criteria making the database processing more complex. Thus I can unfortunately not accept your statement that using 2 indexes are faster than using single index in other database systems like SQL-Server or Informix.

We have a fairly large table of over 150 million rows with a couple if indexes. If you specify a single index value with a select on this table (e.g. all rows for a specific person), you get a response within seconds. Add additional criteria to it like a date range, and the speed reduces drastically, even though a lot less rows are returned. Add a criteria that can be satisfied by another index could increase the processing complexity even more.

The "nice thing" (if it can be called that) with large data volumes are that you quickly notice even the smallest and tiniest of overheads or additional processing that's required by the database engine, which you will -never- see when working with smaller databases.

The "normal" index processing method (don't know how this is done in Oracle) is as follows (from my old Adabas days ;-). The record numbers that's satisfied by one index is sorted and match/merged with the record number of the 2nd index. The result thus contains the record numbers of the rows that satisfy both index values. This may work fine with smallish data volumes and where the distribution of record numbers per index is low. This probably can become a problem with larger data and index sets. It's probably faster reading the rows for index 1 and then applying the 2nd criteria to that row. With this method data can be returned immediately (i.e. as soon as you find a row that matches the 2nd criteria, give it to the client). The first method I described requires the sort/merge/match process to complete first, before any physical data access can be done and rows returned to the user.

Another thing to keep in mind is parallel processing. When using normal index access, Oracle does not perform parallel table access - the index is processed and the rows read from disk with a single process. When using a full table scan, Oracle can fire up a number of parallel processes to process all the rows in the table looking for rows that satisfies the criteria. Depending on the size and parallel settings, this is often much faster. Remember that there's always latency on an I/O call - having I/O done with multiple processes on a large table can result in a dramatic increase in performance when compared to a single process doing the I/O. I've had Oracle queries which spend 99% of their time waiting for I/O because they were not run in parallel. Had to learn the hard way. :-)

regards,
Billy Received on Wed Jun 10 1998 - 08:32:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US