If your Adaptive Server is configured for large I/Os in the default data cache or in named data caches, the optimizer can decide to use large I/O for:
Queries that scan entire tables
Range queries using clustered indexes, such as queries using >, <, > x and < y, between, and like “charstring %”
Queries that scan a large number of index leaf pages
If the cache used by the table or index is configured for 16K I/O, a single I/O can read up to eight pages simultaneously. Each named data cache can have several pools, each with a different I/O size. Specifying the I/O size in a query causes the I/O for that query to take place in the pool that is configured for that size. See the System Administration Guide for information on configuring named data caches.
To specify an I/O size that is different from the one chosen by the optimizer, add the prefetch specification to the index clause of a select, delete, or update statement. The syntax is:
select select_list
from table_name
( [index {index_name | table_name} ]
prefetch size)
[, table_name ...]
where ...
delete table_name from table_name
( [index {index_name | table_name} ]
prefetch size)
...
update table_name set col_name = value
from table_name
( [index {index_name | table_name} ]
prefetch size)
...
The valid prefetch size depends on the page size. If no pool of the specified size exists in the data cache used by the object, the optimizer chooses the best available size.
If there is a clustered index on au_lname, this query performs 16K I/O while it scans the data pages:
select *
from authors (index au_names prefetch 16)
where au_lname like "Sm%"
If a query normally performs large I/O, and you want to check its I/O performance with 2K I/O, you can specify a size of 2K:
select type, avg(price)
from titles (index type_price prefetch 2)
group by type
Reference to Large I/Os are on a 2K logical
page size server. If you have an 8K page size server, the basic
unit for the I/O is 8K. If you have a 16K page size server,
the basic unit for the I/O is 16K.
