Anne,
Upon first glance I wondered if it's because you are not specifing the Index
to use for b.
Possibly, you don't have a clause in your where statement that filters the
number of records you're
trying to get back from ucc_master_amendment m.
In the statement: d.document_number = m.document_number but what
does m.document_number equal?
Can you see what I'm getting at here? Sorry 8:45am, still a little sleepy
;-)
Regards,
Kev.
"hit any user to continue"
__________________
Kevin Thomas
Technical Analyst
Deregulation Services
Calanais Ltd.
(2nd Floor East - Weirs Building)
Tel: 0141 568 2377
Fax: 0141 568 2366
http://www.calanais.com
-----Original Message-----
Sent: 10 September 2001 20:15
To: Multiple recipients of list ORACLE-L
Hey list, Can anyone tell me what's wrong with this query?
many thanks,
select /*+ INDEX(b)*/ distinct d.batch_number
, d.document_number
, d.entry_user_id
, d.document_type_id
, d.document_processed_date
, b.batch_media_id, d.return_Method_Id
from submitter_batch b , document d
, ucc_master_amendment m
where d.batch_number = b.batch_number
and d.document_number = m.document_number
and d.imaged = 0
and b.batch_media_id = 4
and d.document_status_id = 4
Submitter_batch - 97853 rows
Document - 8043272 rows (fk_d_batchnumber index on
batch_number)
Ucc_master_adment - 0 rows (pk_uma_dn index on document_number)
Here is the explain plan:
explain plan expected rows object name
____________________________________________________________________
select statement 164662478
sort (unique) 164662478
nested loops 164662478
nested loops 2008079
table access(full) 46193 submitted
batch
table access(by idx) 2008079 document
index(range scan) 2008079
fk_d_batchnumber non-unique
index(unique scan) 82
pk_uma_dn unique
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Anne Yu
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: Thomas, Kevin
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).
