| 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: Query problem
Try running the first query and specify that the plan not use an index or
speficy a full scan ( you could do this in MS SQL server), not sure about
oracle.
Would be interesting to know.
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:
Subject: Query problem
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
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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 Fri Dec 08 2000 - 08:03:08 CST
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |