| 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: table scan vs idx scan
hi walthour
thnq for responding. i ran explain plan after every change.
it is made sure, that oracle is doing full table scan.
prasad
--- Jon Walthour <jonw_at_fuse.net> wrote:
> Prasad:
>
> Have you run an explain plan on the the query on
> tab2 to see what Oracle is
> doing?
>
> Jon Walthour
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L"
> <ORACLE-L_at_fatcity.com>
> Sent: Saturday, July 21, 2001 12:15 PM
>
>
> > 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
> >
>
-- 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 - 21:12:01 CDT
 |
 |