Re: Query problem

Hannah,

I tried that, but it is still not doing a full table scan,

Thanks

Sukumar Kurup

• Hannah.M.Doran_at_sb.com wrote:
  >
  >
  >
  > Ok. In Oracle , you can use the FULL optimizer hint
  > in order to force a table
  > scan. Not sure how to specify for joins.
  >
  >
  > select /*Full(table_name)*/* from table_name;
  >
  >
  > Hannah
  >
  >
  >
  >
  >
  > Sukumar Kurup <sukuora_at_yahoo.com> on 12/07/2000
  > 10:34:54 PM
  >
  > Please respond to ORACLE-L_at_fatcity.com
  >
  > To: Multiple recipients of list ORACLE-L
  > <ORACLE-L_at_fatcity.com>
  > cc:
  >
  >
  >
  >
  > Hi List,
  >
  > The following query gives out results after allmost
  > 5
  > minutes :
  >
  > select a.table_name,a.index_name,b.bytes
  > from dba_indexes a, dba_segments b
  > where a.table_name=b.segment_name
  > and a.table_name='<table_name>'
  > and a.owner='<owner_name>' ;
  >
  > However, with the last line modified to read as
  >
  > and rtrim(a.owner)='<owner_name>
  >
  > the result comes immediately.
  >
  > I tried this on different servers, and the results
  > are
  > similar.
  >
  > I did an explain plan on both the queries, and the
  > notable difference was that while the first query
  > did
  > an index scan on OBJ$ table (one of the table used
  > in dba_indexes view), whereas in the second query,
  > it
  > did a full table scan (due to the rtrim function).
  >
  > Could the presence of index on obj$ table be the
  > reason for such a difference in the query timings ?
  > can Index actually slow down a query to such an
  > extent
  > ?
  > Or is there some other issue ?
  >
  > Thanks in advance, for any enlightment,
  >
  > Sukumar Kurup
  >
  >
  >
  >
  > __________________________________________________
  > Do You Yahoo!?
  > Yahoo! Shopping - Thousands of Stores. Millions of
  > Products.
  > http://shopping.yahoo.com/
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author: Sukumar Kurup
  > INET: sukuora_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).
  >
  >
  >
  >
  >
  >
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author:
  > INET: Hannah.M.Doran_at_sb.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).
  

  ---