| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: What's wrong with this query
Thanks Lisa, I got this explain plan from the OEM. What is the 'expected rows' ? Cardinality factor ? This query (below) should only return 1 or 0 row , not 164662478 rows. A consultant company created this application for us. I have over 37 queries like this one. Some expected rows are (8,298,736,866,720), some are(1417777E+1), some performs Merge join Cartesian. None of these queries take more then 1 second to run but they paused the database from time to time. Any ideas?
Million thanks,
By the way, I am readinng your email everyday, feel like you're one of my friends.
Sent: Monday, September 10, 2001 2:38 PM To: Multiple recipients of list ORACLE-L
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-----
Sent: Monday, September 10, 2001 3:15 PM
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
--
--
Author: Anne Yu
INET: AYu_at_sos.state.tx.us
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: Anne Yu
INET: AYu_at_sos.state.tx.us
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).
Received on Mon Sep 10 2001 - 14:34:08 CDT
 |
 |