|
datachange()
The
system function
datachange() determines how many changes to existing data has happened since
the last execution of
update statistics.
This includes the total number of
inserts,
updates
and
deletes
on an
object,
partition
or
column
of a
table.
The function must be invoked with all of its three parameters.
datachange() can be executed by any user and is available in
ASE
starting with version 15.0.
The
function
datachange() is helpful in deciding if running update statistics would be advantageous
for optimizing
query plans.
The number of diferent
DMLs
is not listed. It must be considered that the
"datachange counter" will be increased by 2 during an update,
because an update consists of a
delete and an insert.
The result set of the system function datachange() is based on the currently available
number of rows in percent of a given object. If, for example, a row was added to a table
containing three rows, this table will consequently contain 4 rows. The additional row
makes up 25% of all rows in the table. The "datachange counter"
will be increased by one. The same is true for partitions. Because the number of changes
can be higher than the total number of rows in a table or partition, the return value can be
larger than 100%.
The value determined by the system function datachange() is retrieved from server memory.
This means that it can differ from the actual value, which was already flushed to disk.
This value is loaded into memory by the
houskeeper
process or during execution of the
system procedure
sp_flushstats
or if the so-called
objects descriptor
is flushed to memory.
When is the "datachange counter" reset or initialized with "0"?
The "datachange counter" is
not reset during
update statistics,
if a
histogram
is created for a
global index
on a
partitioned table.
New tables, though, or newly added
columns
or
partitions
are initialized with "0".
When creating, deleting or updating of the respective histogram,
the "datachange counter" is reset.
When
truncating
a table or partition and therefore its datachanges, the
"datachange counter" is reset.
When reorganising a partition of a table,
the values of the "datachange counter" are reset.
When removing all partitions of a table, the value of the
"datachange counter" is reset.
Limitations of the System Function datachange()
-
For
tempdb,
user defined tempdbs,
system tables
or
proxy tables,
no datachange statistics are maintained.
-
In case of a
rollback,
the value of the
"datachange counter" is not rolled back. This can result in
inaccuracies in the "datachange counter".
-
Occasionally, the "datachange counter" for
columns cannot be determined from memory.
In this case, the value for the partition layer is used instead.
-
If the "datachange counter" for column layers is reset,
but did not contain any values,
the "datachange counter" for the partition layer is reset instead.
Syntax of the System Function datachange()
datachange("object_name", "partition_name", "column_name")
The parameters of the System Function datachange()
"object_name"
The parameter "object_name"
is the name of the object in the respective
database.
It can be retrieved from the
column
name in the
system table
sysobjects.
"partition_name"
The parameter "partition_name" is the name of the
partition.
The parameter can specified with
NULL.
"column_name"
The parameter "column_name"
is the name of the column for which to determine data changes.
See also:
dump transaction,
sp_thresholdaction,
See also:
ASE T-SQL - Aggregate Functions,
audit_event_name(),
col_length(),
col_name(),
curunreservedpgs(),
datalength(),
data_pages(),
data_pgs(),
db_id(),
db_name(),
host_id(),
host_name(),
identity(),
index_col(),
index_colorder(),
isnull(),
lct_admin(),
license_enabled(),
lockscheme(),
next_identity(),
object_id(),
object_name(),
pagesize(),
pointer_size(),
ptn_data_pgs(),
reserved_pages(),
reserved_pgs(),
rowcnt(),
row_count(),
showplan_in_xml(),
syb_sendmsg(),
syb_sendmsg(),
System Functions,
tempdb_id(),
tran_dumpable_status(),
tsequal(),
used_pages(),
used_pgs().
|
