Re: cache table vs. indexed table, and multiblock reads

Hi,

To answer your first question, as you say, CACHE means if the table is accessed by FTS, then load into the MRU end of the Buffer instead of the LRU end of the Buffer which is the default behavior with FTS. You should use a CACHED table when the data fits into only a couple of data blocks, is accessed frequently (like a lookup table) and where accessing the table is performed via a FULL TABLE SCAN. Sometimes, an index is necessary to ensure uniqueness, and this can cause extra logical I/O for accessing small tables, which is why you can specify /*+ FULL */ as the hint and have the tabled CACHED. Indexing the entire table, or creating Index Only Tables (IOT's), is generally used when the data you are wanting is being accessed by PRIMARY KEY and the table is larger in size, where a FTS will take longer than accessing the data via an Index.

In short, I think it is size orientated. If you have a table that is really small (less than 3 or 4 blocks) then try CACHE, but if the table is larger, and access the table via an Index structure will access less data blocks, then go for indexing. Ideally, you would want to keep the index size down to only a couple of data blocks (3 or 4), which for most platforms could support in the order of 400 to 500 records (assuming a small 7 character key).

I'll leave the Multiblock Read Count for others, but my 0.02 says, it can be important with RAID configurations. i.e. If you have 5 disks in a RAID configuration, each disk holding 16K stripe size, so the total width of the data over the 5 disks is 5 * 16K = 80K, so the optimal setting would be to read 80K so if you have a 4096 Oracle block size, then you would want to access either 20 or 40 O/S data blocks depending upon the block size of 1K or 512Bytes.

Rgds
Andrew

Sweth Chandramouli wrote:

> two quick questions:
>
> 1) when would it make sense to use a cache table versus
> a fully indexed one? as i understand it, the main advantage of
> a cache table is that it disables buffer aging, so that small
> lookup tables that are read via full-table-scans will be read
> into the buffer cache like regular data, instead of being placed
> at the "old" end of the stack, so that it will be overwritten
> immediately. fully indexing a table, on the other hand, prevents
> the full table scan from ever happening, but is mostly useful for
> the same sorts of tables. how should i pick between the two?
>
> 2) i've often been told that, if your os file system
> caching algorithm supports block read-ahead (e.g. solaris), it
> is advantageous to use increase the number of oracle blocks
> that are read at once via the multiblock_read paramater; i've
> never been able to get a good answer as to what value to set
> that paramater to, however. i recently picked up the oracle
> press advanced tuning guide for some more insight into this;
> all that book says, however, is to tune the multiblock reads
> to the optimal value for your operating system. does anyone
> know of any better rules that "set it to the best value", which
> seems to be what the book says? (i'm interested in solaris
> in particular, but would rather understand how to figure out
> the "optimal value" for any os if i understand how that os
> caches file reads.)
>
> tia,
> sweth.
>
> --
> Sweth Chandramouli
> IS Coordinator, The George Washington University
> <sweth_at_gwu.edu> / (202) 994 - 8521 (V) / (202) 994 - 0458 (F)
> *
Received on Wed May 05 1999 - 18:51:57 CDT