Re: Indexes in memory not on disk

In <322953576wnr_at_artemis.demon.co.uk> tim_at_artemis.demon.co.uk (tim london) writes:

>
>In article: <jean-francois.denis.6.000DB071_at_s1.bru1.sni.be>
 jean-francois.denis_at_s1.bru1.sni.be (Jean-Francois Denis) writes:
>> Xref: demon comp.databases.oracle:15297
>> Path:

demon.co.uk!demon!peernews.demon.co.uk!btnet!bt!pipex!uunet!news.tele.fi !charon.siemens.be!snibru!biggreen.cb.sni.be!jean-francois.denis
>> From: jean-francois.denis_at_s1.bru1.sni.be (Jean-Francois Denis)
>> Newsgroups: comp.databases.oracle
>> Subject: Indexes in memory not on disk
>> Date: Thu, 26 Jan 1995 13:41:16
>> Organization: Siemens Nixdorf Information Systems
>> Lines: 16
>> Distribution: world
>> Message-ID: <jean-francois.denis.6.000DB071_at_s1.bru1.sni.be>
>> NNTP-Posting-Host: biggreen.cb.sni.be
>> X-Newsreader: Trumpet for Windows [Version 1.0 Rev Final Beta #8]
>>
>> Hello,
>>
>> Having performance problems, we are trying to find a solution to keep
 the
>> indexes in memory only. We would like to avoid disk i/o for the
 indexes.
>>
>> Of course, we could keep them in memory at application level without
 using
>> Oracle for the indexes (no primary key) but using Oracle just for the
 data.
>>
>> Is there any possibility to keep the indexes in memory (Oracle SGA)
 and to
>> specify to Oracle that they do not have to be written on disk. Since,
 the
>> memory has a limited size, this index should be 'dropped' on a
 regular basis,
>> I suppose.
>>
>> Thanks for your help. (We are working on a Unix machine, developping
 in C)
>>
>> Jean-Francois DENIS (jean-francois.denis_at_s1.bru1.sni.be)
>>
>>
>--
>
> There is no way of telling Oracle to lock an index into memory.
>
> I would suspect that you have insufficient main memory for your
 application.
> Try increasing DB_BLOCK_BUFFERS, but don't induce O/S paging or
> swapping.
>
> E-mail me if I can be of further assistance.
>
>---
>
> Tim L
>
>

There is another approach to the performance problem described by Jean-Francois DENIS. Install a Solid State Disk. This is memory with an interface like a standard SCSI disk. There will still be I/O but the access time to SSD is about 300 times faster than to a rotating disk. SSDs are available in many capacities and could be expanded later if the indexes grow.

This gets around the problem of trying to lock an index into memory. Contact me if more information is wanted.

John Jory (jjjj_at_ix.netcom.com)
Imperial Technology
Tel 310 536-0018 Fax 310 536-0124 Received on Tue Jan 31 1995 - 16:25:43 CET