Re: fastest method for reading tables

Waqar Hasan wrote:
>
> I have an application program that needs to read data
> by the fastest method possible.
>
> Is OCI the fastest available interface?

Yes.
Not much more than Pro*, but faster anyway. Make sure you use the array interface.

> What parameters need to be tuned to get the application
> to go faster?

Heaps. Start by sizing the SGA correctly. Plenty of info on books. Make sure that your db block size is reasonable for your application (8k for just about everything, larger or much larger for data warehousing). Size memory correctly, make sure there is very little paging. Use indexes if not doing full table scans. Choose selective and effective multiple column indexes. Use db_file_multiblock_read_count to speed up full table scans. Size temporary tables for the GROUP BY and ORDER BY clauses, make sure you got more than one temp table if doing a lot of these. Use the fastest disks you can get, not more than three per controller. Use RAID 1 (striping) hardware, if not available do it via OS. SPREAD THE LOAD. etc,etc, could go on for hours.

> Is there any other interface that might go faster than OCI?
>

Nope. None available to the public. Talk with ORACLE, they might let you do a direct sqllib link and give you the doco to do that, but it won't be portable accross versions and you'll have to pay them lots!

> The application makes queries that retrieve *all* rows of a table
> (but only a subset of the columns).
>
> The application does not care if the data is slightly out of date.
>

Investigate the funny transactional mode introduced with V7 to speed up queries that do not have concurrent updates. Retrieve ONLY the columns you want. If you want to apply SQL functions to the columns to derive other results, investigate the possibility of doing it in your program instead of via SQL. While the ORACLE functions are very,very fast, nothing beats a well tuned program for that type of thing. Also, if doing lookups, investigate the possibility of caching the lookup rows in your program's address space, instead of doing it via SQL. There is a "sweet spot" on this, beyond which it doesn't matter, but that varies with the OS and memory available, so trial!

HTH

-- 
Nuno Souto
nsouto_at_NOSPAMacay.com.au