Your hint is wrong. You have to tell it what index to use. example /*+ index (table_alias index_name) */
Remember hints, if they are wrong, will just be ignored.
Your execution plan seems OK. It's returning an awful lot of rows ...
Lisa Koivu
Oracle Doggie Administrator
Fairfield Resorts, Inc.
954-935-4117
-----Original Message-----
From: Anne Yu [SMTP:[EMAIL PROTECTED]]
Sent: Monday, September 10, 2001 3:15 PM
To: Multiple recipients of list ORACLE-L
Subject: What's wrong with this query
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).
