Hi
Am Mittwoch 01 September 2010 23:04 schrieb Ashish Kulkarni:
FETCH FIRST 50 ROWS ONLY
OPTIMIZE FOR 2 ROWS
with both of this you tell the optimizer that the query has a high
selectivity. If the table has only some 10000 records, there would be no
effect, if the table has 100 of millions of records, the optimizer tends in
the direction to use an index (if available) over a full table scan. This
clause might be effective, when a full table scan causes slow perfomance and
is not needed.
the diffrence between the two is, that the first limits the ResultSet to 50
rows, the second does not set a limit. You should have in mind, that defining
a resultSet opens an accesspath, but normally it does not fetch any row.
conclusion of this: optimize for 50 rows and fetch first 50 rows only and
afterwards 10 fetch operations (in Java next()) would be comparable fast or
slow.
FOR READ ONLY
it depends: if you have a lock level allowing dirty reads (no commit, auto
commit or read uncommited, most databases would read blocks of data and cache
it and this mostly would speed up your query. Normally it would not slow down
the query, even if you would fetch only 1 row, because the Operating system
transfers data in blocks anyway.
WITH NC;
thats no performance relevant option for most database systems (including
DB2/400). reading data, there is no diffrence between no commit and read
uncommitted and writing data is optimized for commitment controll. You should
have in mind two things:
writing data using sql without commitment controll is not state of the art and
you have the risc, that data changes between read and write, depending on
your database design and your code.
using commitment controll, you must ensure, that your transactions are defined
correctly, collecting lots of record locks might slow down your performance.
D*B
--
Ashish
www.ayurwellness.com
www.mysoftwareneeds.com
midrange.com
