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:[EMAIL PROTECTED]]
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 <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
|
|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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
