UPDATE STATISTICS may use indexes?
I'm chatting with a customer DBA about UPDATE STATISTICS HIGH and he's
reporting some seemingly counterintuitive performance results...
On a table having three columns (say A, B and C), he is reporting:
UPDATE STATISTICS HIGH FOR TABLE tbl(A);
UPDATE STATISTICS HIGH FOR TABLE tbl(B,C);
is completing about 30% faster than:
UPDATE STATISTICS HIGH FOR TABLE tbl;
This seems wrong to me as two UPDATE STATS HIGH statements should result
in two full-table scans, no?
Unless...
Column A is the table's primary key and there is an index on (B,C).
Does UPDATE STATISTICS use an index to gather distribution statistics if
columns are explicitly specified and an index on those columns exists?
--
John Hardin KA7OHZ
Senior Applications Developer, BI Specialist
EPICOR Retail
web: http://www.epicor.com
email: <jhardin@epicor.com>
--- Posted via news://freenews.netfront.net/ - Complaints to news@netfront.net ---
It can use indexes but probably that's not the point.
You seem to be assuming that the whole table statement only does one scan.
That's probably wrong.
Assuming you're using a currently supported version use the set explain on
statement and you'll see how it's performing the update statistics.
Giving the table name and no colums will force all coluns. This means much
mote work and much more resources (memory) which can force several table
scans.
The correct way to do that would be to use a single statement with the
three columns. Probably spliting the "high ... distribution only" and the
"low" would also be better.
On Sep 11, 2012 7:55 PM, "John Hardin" <jhardin@epicor.com> wrote:
> I'm chatting with a customer DBA about UPDATE STATISTICS HIGH and he's
> reporting some seemingly counterintuitive performance results...
>
> On a table having three columns (say A, B and C), he is reporting:
>
> UPDATE STATISTICS HIGH FOR TABLE tbl(A);
> UPDATE STATISTICS HIGH FOR TABLE tbl(B,C);>
> is completing about 30% faster than:
>
> UPDATE STATISTICS HIGH FOR TABLE tbl;>
> This seems wrong to me as two UPDATE STATS HIGH statements should result
> in two full-table scans, no?
>
> Unless...
>
> Column A is the table's primary key and there is an index on (B,C).
>
> Does UPDATE STATISTICS use an index to gather distribution statistics if
> columns are explicitly specified and an index on those columns exists?
>
> --
> John Hardin KA7OHZ
> Senior Applications Developer, BI Specialist
> EPICOR Retail
> web: http://www.epicor.com
> email: <jhardin@epicor.com>
>
> --- Posted via news://freenews.netfront.net/ - Complaints to
> news@netfront.net ---
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>