Re: Informix data fragmentation or RAID disk
'ORDER BY' doesn't always create temporary tables. It depends on the
indexing and the query itself.
Example:
CREATE TABLE nomaccounts
(
co_code integer,
nom_code char( 10 ),
...
);
CREATE UNIQUE INDEX i_nomaccounts on nomaccounts( co_code, nom_code );
SET EXPLAIN ON;
SELECT * FROM nomaccounts ORDER BY co_code, nom_code;
And 'sqexplain.out' should show that the index on co_code, nom_code is
being used and so far as I have been able to tell (by looking in
$DBTEMP (we use SE) ) *no* temporary tables etc. are created.
However...
SELECT * FROM nomaccounts WHERE co_code = 1 ORDER BY nom_code
fails to use the index! Altering the ORDER BY to match the index solves
the problem.
In article <4a0gij$ke9@cygnus.mincom.oz.au>
dhammika@mincom.oz.au "Dhammika Weerasekera" writes:
> Still there is a perfomance problem with fragmented tables (even on 7.11UC1).
> When you have an ORDER BY clause in a query (on an indexpath or not) it
> creates a temporary table to sort which is very very slow on large tables.
> So if you are using ORDER BY think twice before you fragment them.
--
============================================================================
Sally Woolrich | This mail contains my personal
sally@excelsis.demon.co.uk | views not those of my employer!
============================================================================