RE: What's wrong with this query

From: Koivu, Lisa <lisa.koivu_at_efairfield.com>
Date: Mon, 10 Sep 2001 11:48:37 -0700
Message-ID: <F001.003887EC.20010910113747@fatcity.com>

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:AYu_at_sos.state.tx.us]
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: 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 - 13:48:37 CDT