RE: Nested loop very slow

Thanx Jonathan,

I was assuming that more reads could be done in 1 second. I'll try sorting the data based on the index to speed up the query.

-----Oorspronkelijk bericht-----
Van: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk] Verzonden: donderdag 23 augustus 2001 14:21 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: Nested loop very slow

We really need more details about the data and the query to give you the 'correct' answer. But let us assume that the 97,000 result rows joining one row in the large table to a related parent row in the smaller table.

If you index to find those 97,000 rows - with a perfect index, not a range scanned index - then the rows could all be in different blocks, which could result in 97,000 individual physical block reads.

At peak operation, you are unlikely to get more than one hundred reads per second, so that would equate to 100 rows per second - which required 970 seconds for 97,000 rows, which comes to: a little over 16 minutes.

Under those circumstances, your 14 minutes seems quite justifiable.

NB if you include an ORDER BY in your query, Oracle has to get all the rows before it can sort them (unless you have a convenient index path which allows a 'no-sort order by'), so you couldn't get your result in just a few seconds.

Jonathan Lewis

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases See http://www.jlcomp.demon.co.uk/book_rev.html

For latest news of public appearances
See http://www.jlcomp.demon.co.uk

Screen saver or Life saver: http://www.ud.com Use spare CPU to assist in cancer research.

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 23 August 2001 12:08

|Hi,
|
|I'm joining a table with 32000 rows with a very large table (3 Gb),
|resulting in 97000 records. When I execute the query without hints,
it uses
|a full table scan and a hash join, and returns a result in 4 minutes.
When I
|use a hint forcing a nested loop (and part of an index (range scan)),
the
|query takes 14 minutes to complete. The question is: why does a join
between
|an inner table with 32000 records and a large table using a range
scan on an
|index take so long ? I would expect Oracle to give a result within
seconds,
|a few minutes tops !
|
|The table has very few chained rows and I've rebuilt the index
recently.
|RDBMS is 8.1.6. Index and data are divided over many different disks.
|
|Any suggestions ? Except kicking the server ?
|
|Remco
|
|--
|Please see the official ORACLE-L FAQ: http://www.orafaq.com
|--
|Author: Daemen, Remco
| INET: R.Daemen_at_facent.nl
|
|Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
|San Diego, California -- Public Internet access / Mailing
Lists
|--------------------------------------------------------------------
|To REMOVE yourself from this mailing list, send an E-Mail message
|to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
|the message BODY, include a line containing: UNSUB ORACLE-L
|(or the name of mailing list you want to be removed from). You may
|also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Daemen, Remco
  INET: R.Daemen_at_facent.nl

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).