| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Newbie: indexing long raw table
A copy of this was sent to "DSmith" <dsmith2005_at_yahoo.com>
(if that email address didn't require changing)
On Wed, 27 Jan 1999 17:49:38 -0500, you wrote:
>All,
>
>I have a long raw table on an O 8.0.4 DB, that is used to store various
>images, it is stored in the "data" tablespace. There is a primary key index
>on the "img_name" column that is stored in "indx" tablespace. The
>application code performs selects on the table based on the primary key of
>"img_name".
>
>We have begun to load several thousand records at ~ 50K bytes each and I've
>noticed that whenever I do a count(*) OR count(name) it appears to invoke a
>full-table scan. Is this normal? If not, what can I do to fix it?
>
if you analyze the table, it'll use the index on the primary key for the count(*) (RBO won't, CBO will if you analyze).
short of that,
select count(*) from IMAGE_TABLE where img_name > chr(0);
should make the RBO use the index (performs an index range scan to count)...
>It seems that only the index should be scanned to determine the record
>count, particularly when using the indexed column "img_name".
>
>I've also noticed that SYSTEM tablespace has grown dramatically (as reported
>by Storage Manager) to a high-water mark of over 3GB during the loading
>process. Any thoughts (the only users that have SYSTEM as their default
>tablespace are SYS, SYSTEM, and SNMP)?
>
did you check to see if the table was in system? loading a table won't make the system tablespace grow so wildly -- it might grow a tiny bit if one of the data dictionary tables extends during space management, but by not 2 or 3 gig.
>Any help would be greatly appreciated.
>
>--David Smith
>dsmith_at_hpti.com
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jan 27 1999 - 19:46:22 CST
 |
 |