table scan vs idx scan

From: prasad maganti <prasadm_g_at_yahoo.com>
Date: Sat, 21 Jul 2001 08:07:34 -0700
Message-ID: <F001.00350B62.20010721081522@fatcity.com>

Hi dbas

we have a situation here. can anybody tell me why this is happening
:

we have 2 tables with identical rows(almost) with same columns even indexed in same manner.

tab1 51000 rows
tab2 51500 rows

tab1 indexed with idx1 on col1,col2,col3 tab2 indexed with idx2 on col1,col2,col3

TAB1:

---

just i am selecting as
select col1,col2,col3 from tab1

this is using index scan and taking
90 seconds to get the data
(Explain plan )

TAB2:

---

i am select as above
select col1,col2,col3 from tab2

this is using fulltable scan and taking 18 minutes to get data.
(Explain plan)

we tried in all possible ways for tab2 ,like

analyzed objects (both idx,table (compute statistics)) changed optimizer modes and tried,
giving hints to use index ,
rebuilt of index,
after all we dropped the idx2 and recreated it.

but the case is same. still it is taking >18 minutes.

even when i provided hints it is not searching for that
index, doing the same old full table scan.

why oracle doesn't follow hints in some cases.

and what should i do to reduce the time to fetch the data.

thnx in adv

prasad

---

Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: prasad maganti
  INET: prasadm_g_at_yahoo.com

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 Sat Jul 21 2001 - 10:07:34 CDT