You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Radovan Biciste <rb...@softhome.net> on 2005/10/17 20:15:05 UTC
How to update cardinality statistics
Hello,
I'm very new to derby. I was looking how to update cardinality
statistics. I have joins between tables with couple hundreds of rows and
about 600 thousands of rows. I'm using embedded server version 10.1.1 .0.
Here is what I've found so far:
1. UPDATE STATISTICS FOR TABLE mytable is giving me a syntax error.
Should work on Cloudscape though.
2. SYSCS_UTIL.SYSCS_COMPRESS_TABLE should update statistics but does not
make too much sense as I do only inserts. It would take tooo much time.
Am I missing something ?
Thank you very much,
Radovan
Re: How to update cardinality statistics
Posted by Jeffrey Lichtman <sw...@rcn.com>.
>Its a nice goal to be zero admin. however, I wonder if you're implying
>something that may be misleading.
Good point - this needs clarification. The idea of zero admin with
Derby is that an application doesn't need maintenance once it's
deployed. It doesn't mean that no tuning is required - just that it's
not required once the app is in use. This is an important aspect of
an embeddable database, since the end user may not even know there's
a copy of Derby in the app.
So, for example, if the developer sets up indexes to ensure adequate
performance, the end user will not have to do periodic maintenance on
the indexes to keep the same level of performance.
- Jeff Lichtman
swazoo@rcn.com
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
Re: How to update cardinality statistics
Posted by Mike Matrigali <mi...@sbcglobal.net>.
You are right I was unclear. Let me try again. Derby uses existing
indexes created by the user to derive histogram information to be used
in costing various query plans, and thus in chosing which indexes it
uses. This histogram is dynamic and never needs to be updated by the
user. All other databases that I have worked on in the past have
required users to periodically update histogram based statistics if
their data distribution changes. I was trying to point out that this
manual update was not necessary in Derby.
I also really miss having a single index to search the documents.
Michael J. Segel wrote:
> On Tuesday 18 October 2005 13:50, Mike Matrigali wrote:
>
>>I 2nd Satheesh's query, it is useful to know why you care. Derby
>>unlike most other database's automatically maintains histogram
>>type information about the tables (this does require indexes to
>>exist). This information is gathered directly from the indexes,
>>and is automatically maintained by Derby.
>>
>
> I believe the issue is that the documentation may be sparse or hard to find.
> In fact, a quick check on the 10.1 docs, in the Reference Manual the
> description for SYSSTATISTICS is incorrect. (It appears to be a typo as a
> replication of SYSSCHEMAS entry. see page 149).
>
> [Seriously it would be nice if someone created an index for these docs.
> (Informix used to have their indexes reference mutiple docs which was a nice
> feature...)]
>
>
>>The row count is also automatically maintained by Derby - whether
>>an index exists or not.
>>
>
> I don't think that the row count itself is an issue.
>
> From looking at the original poster, he said he was adding 600 thousand rows
> in to the database and was looking for a way to update statistics. Since most
> people who are looking at Derby are also familiar with another RDBMS or two,
> they are going to try and find if similar functions exist.
>
> Without going in to an example, how many times have you seen on c.d.i, "Did
> you UPDATE STATISTICS..." as a reply to a performance question.
>
> Going from memory, which is never a good thing to do, after you've tried OTC's
> bath tub gin, I believe that Informix's IDS will not use an index but do a
> sequential scan if the table has less than 10K rows? (You may want to check
> with Mohan, he'd know for sure....) While I may be off on the number of rows,
> the point is that IDS will do something to optimize for performance. The
> downside is that if you have a rapidly growing/changing database, you may
> need to update statistics so that the optimizers will perform correctly.
>
> [Note: Derby has a much smaller footprint. This would entail that Derby lacks
> features/functionality that other databases would have. This is not
> necessarily a bad thing....]
>
>
>>A goal of Derby is to be zero-admin so we are interested in problems
>>that users encounter which make them think they are required to run
>>admin type tools. Do note that Derby does require users to appropriate
>>secondary indexes on their tables, once those indexes are created derby
>>should be able to automatically pick the correct plan.
>>
>
>
> Its a nice goal to be zero admin. however, I wonder if you're implying
> something that may be misleading. What indexes exist when I create a table?
> You said:"Derby unlike most other database's automatically maintains histogram
> type information about the tables (this does require indexes to exist)."
>
> So if I create a table foo that has column A an integer, and column B a 20
> character text field... What index on table foo exists?
>
Re: How to update cardinality statistics
Posted by "Michael J. Segel" <ms...@segel.com>.
On Tuesday 18 October 2005 13:50, Mike Matrigali wrote:
> I 2nd Satheesh's query, it is useful to know why you care. Derby
> unlike most other database's automatically maintains histogram
> type information about the tables (this does require indexes to
> exist). This information is gathered directly from the indexes,
> and is automatically maintained by Derby.
>
I believe the issue is that the documentation may be sparse or hard to find.
In fact, a quick check on the 10.1 docs, in the Reference Manual the
description for SYSSTATISTICS is incorrect. (It appears to be a typo as a
replication of SYSSCHEMAS entry. see page 149).
[Seriously it would be nice if someone created an index for these docs.
(Informix used to have their indexes reference mutiple docs which was a nice
feature...)]
> The row count is also automatically maintained by Derby - whether
> an index exists or not.
>
I don't think that the row count itself is an issue.
From looking at the original poster, he said he was adding 600 thousand rows
in to the database and was looking for a way to update statistics. Since most
people who are looking at Derby are also familiar with another RDBMS or two,
they are going to try and find if similar functions exist.
Without going in to an example, how many times have you seen on c.d.i, "Did
you UPDATE STATISTICS..." as a reply to a performance question.
Going from memory, which is never a good thing to do, after you've tried OTC's
bath tub gin, I believe that Informix's IDS will not use an index but do a
sequential scan if the table has less than 10K rows? (You may want to check
with Mohan, he'd know for sure....) While I may be off on the number of rows,
the point is that IDS will do something to optimize for performance. The
downside is that if you have a rapidly growing/changing database, you may
need to update statistics so that the optimizers will perform correctly.
[Note: Derby has a much smaller footprint. This would entail that Derby lacks
features/functionality that other databases would have. This is not
necessarily a bad thing....]
> A goal of Derby is to be zero-admin so we are interested in problems
> that users encounter which make them think they are required to run
> admin type tools. Do note that Derby does require users to appropriate
> secondary indexes on their tables, once those indexes are created derby
> should be able to automatically pick the correct plan.
>
Its a nice goal to be zero admin. however, I wonder if you're implying
something that may be misleading. What indexes exist when I create a table?
You said:"Derby unlike most other database's automatically maintains histogram
type information about the tables (this does require indexes to exist)."
So if I create a table foo that has column A an integer, and column B a 20
character text field... What index on table foo exists?
--
Michael Segel
Principal
MSCC
(312) 952-8175
Re: How to update cardinality statistics
Posted by Mike Matrigali <mi...@sbcglobal.net>.
The histogram information I am talking does not cause overhead at insert
time. We use the actual index itself for the histogram. The overhead
exists at compile time.
At compile time the optimizer will ask store a histogram question like
how many rows do you estimate exist such that id > 5 and id < 100. To
answer this question store does a left search in the tree for 5 and
a search for 100 and then estimates from where those nodes lie in the
tree how many rows are in between. This information along with row
counts is 90% of the stat used by Derby.
There is one other statistic that currently is only gathered when
an index is created either explicitly or part of a complete table
reorg. That statistic tells average number of duplicates for a
key or a leading sequence of columns in a key. The key must
have an index associated with it.
There have been some discussion on the mail list on how to better
automatically update this stat.
Michael J. Segel wrote:
> On Wednesday 19 October 2005 04:04, Øystein Grøvlen wrote:
>
>>>>>>>"MM" == Mike Matrigali <mi...@sbcglobal.net> writes:
>>
>> MM> I 2nd Satheesh's query, it is useful to know why you care. Derby
>> MM> unlike most other database's automatically maintains histogram
>> MM> type information about the tables (this does require indexes to
>> MM> exist). This information is gathered directly from the indexes,
>> MM> and is automatically maintained by Derby.
>>
>> MM> The row count is also automatically maintained by Derby - whether
>> MM> an index exists or not.
>>
>>Sounds good, but I think read somewhere in the Derby documentation
>>that some statistical information was only updated when a query did a
>>sequential scan of the table.
>
> Well that doesn't make sense in light of what Mike wrote in a different
> e-mail.
>
> He indicated that Derby used information gathered by an index that the
> DBA/user created on a table.
>
> If I understood correctly, suppose I created the following
>
> Table foo that had two columns: id an integer , and bar a char(25) field. I
> then create an identity index on foo.id.
>
> Derby would utilize the identity index to automatically update the table
> statistics.
>
> If you think about it, it kind of makes sense to do this. I mean after all, on
> an identity index (non null, all distinct and unique), the number of nodes
> would represent the number of rows in the table.
>
> Of course it would also imply some overhead on performing the inserts. No?
> (There is no such thing as a free lunch. Any sort of hands free automation
> will come at a cost.)
>
> I haven't thought a lot about it, but shooting from the hip, I would imagine
> that the cost of this "optimization" would increase as the tables increased
> in size by a log(N) factor. So Derby takes a hit when the database gets
> large.
>
> This of course is assuming I understood Mike's comment.
>
> -G
>
Re: How to update cardinality statistics
Posted by "Michael J. Segel" <ms...@segel.com>.
On Wednesday 19 October 2005 04:04, Øystein Grøvlen wrote:
> >>>>> "MM" == Mike Matrigali <mi...@sbcglobal.net> writes:
>
> MM> I 2nd Satheesh's query, it is useful to know why you care. Derby
> MM> unlike most other database's automatically maintains histogram
> MM> type information about the tables (this does require indexes to
> MM> exist). This information is gathered directly from the indexes,
> MM> and is automatically maintained by Derby.
>
> MM> The row count is also automatically maintained by Derby - whether
> MM> an index exists or not.
>
> Sounds good, but I think read somewhere in the Derby documentation
> that some statistical information was only updated when a query did a
> sequential scan of the table.
Well that doesn't make sense in light of what Mike wrote in a different
e-mail.
He indicated that Derby used information gathered by an index that the
DBA/user created on a table.
If I understood correctly, suppose I created the following
Table foo that had two columns: id an integer , and bar a char(25) field. I
then create an identity index on foo.id.
Derby would utilize the identity index to automatically update the table
statistics.
If you think about it, it kind of makes sense to do this. I mean after all, on
an identity index (non null, all distinct and unique), the number of nodes
would represent the number of rows in the table.
Of course it would also imply some overhead on performing the inserts. No?
(There is no such thing as a free lunch. Any sort of hands free automation
will come at a cost.)
I haven't thought a lot about it, but shooting from the hip, I would imagine
that the cost of this "optimization" would increase as the tables increased
in size by a log(N) factor. So Derby takes a hit when the database gets
large.
This of course is assuming I understood Mike's comment.
-G
--
Michael Segel
Principal
MSCC
(312) 952-8175
Re: How to update cardinality statistics
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
I did not get any replies to this email. It would be useful if
someone could clarify whether the statistics are automatically updated
or not.
--
Øystein
>>>>> "ØG" == Øystein Grøvlen <Oy...@Sun.COM> writes:
>>>>> "MM" == Mike Matrigali <mi...@sbcglobal.net> writes:
MM> Would need to see the exact documentation reference, but this is not
MM> the case. The storage system keeps an estimate of the number of
MM> rows in a table. For performance reasons this estimate is not exact
MM> (ie. we don't update a single could for every insert/delete/abort
MM> insert/abort delete/commit). The actual update is tied to the time
MM> pages move out of the cache. Because the number can drift there is
MM> an optimization in the language execution engine that if it has just
MM> executed a complete scan of the table then it updates the value
MM> in the storage system.
MM> But in almost all cases the automatic maintained row count maintained
MM> by store is good enough and never needs to be updated. The scan
MM> optimization is there to automatically catch the drift if it is
MM> cheap to do so, it wasn't really meant for users to use to explicitly
MM> update it (it was take advantage if a user happened to be executing
MM> a complete scan for some other reason).
ØG> The tuning guide says:
ØG> ----------------------------------------------------------------------
ØG> As you saw in When cardinality statistics are automatically updated,
ØG> cardinality statistics are automatically updated only in limited
ØG> cases. Normal insert, update, and delete statements do not cause the
ØG> statistics to be updated. This means that statistics can go
ØG> stale. Stale statistics can slow your system down, because they worsen
ØG> the accuracy of the optimizer's estimates of selectivity.
ØG> ----------------------------------------------------------------------
ØG> Note especially: "Normal insert, update, and delete statements do not
ØG> cause the statistics to be updated." Is this wrong?
ØG> The "When cardinality statistics are automatically updated" section
ØG> says
ØG> ----------------------------------------------------------------------
ØG> For the following operations that you perform on a table, Derby
ØG> automatically creates statistics. Those operations are:
ØG> * (new index only) When you create a new index on an existing
ØG> non-empty table.
ØG> * (new backing indexes only) When you add a primary key, unique,
ØG> or foreign key constraint to an existing non-empty table.
ØG> For other operations, Derby automatically updates statistics for the
ØG> table and all indexes on the table if they are already exist. Those
ØG> operations are:
ØG> * (all indexes) When you execute SYSCS_UTIL.SYSCS_COMPRESS_TABLE.
ØG> * (index only) When you drop a column that is part of a table's
ØG> index; the statistics for the affected index are dropped, and
ØG> statistics for the other indexes on the table are updated.
ØG> ----------------------------------------------------------------------
ØG> --
ØG> Øystein
Re: How to update cardinality statistics
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
>>>>> "MM" == Mike Matrigali <mi...@sbcglobal.net> writes:
MM> Would need to see the exact documentation reference, but this is not
MM> the case. The storage system keeps an estimate of the number of
MM> rows in a table. For performance reasons this estimate is not exact
MM> (ie. we don't update a single could for every insert/delete/abort
MM> insert/abort delete/commit). The actual update is tied to the time
MM> pages move out of the cache. Because the number can drift there is
MM> an optimization in the language execution engine that if it has just
MM> executed a complete scan of the table then it updates the value
MM> in the storage system.
MM> But in almost all cases the automatic maintained row count maintained
MM> by store is good enough and never needs to be updated. The scan
MM> optimization is there to automatically catch the drift if it is
MM> cheap to do so, it wasn't really meant for users to use to explicitly
MM> update it (it was take advantage if a user happened to be executing
MM> a complete scan for some other reason).
The tuning guide says:
----------------------------------------------------------------------
As you saw in When cardinality statistics are automatically updated,
cardinality statistics are automatically updated only in limited
cases. Normal insert, update, and delete statements do not cause the
statistics to be updated. This means that statistics can go
stale. Stale statistics can slow your system down, because they worsen
the accuracy of the optimizer's estimates of selectivity.
----------------------------------------------------------------------
Note especially: "Normal insert, update, and delete statements do not
cause the statistics to be updated." Is this wrong?
The "When cardinality statistics are automatically updated" section
says
----------------------------------------------------------------------
For the following operations that you perform on a table, Derby
automatically creates statistics. Those operations are:
* (new index only) When you create a new index on an existing
non-empty table.
* (new backing indexes only) When you add a primary key, unique,
or foreign key constraint to an existing non-empty table.
For other operations, Derby automatically updates statistics for the
table and all indexes on the table if they are already exist. Those
operations are:
* (all indexes) When you execute SYSCS_UTIL.SYSCS_COMPRESS_TABLE.
* (index only) When you drop a column that is part of a table's
index; the statistics for the affected index are dropped, and
statistics for the other indexes on the table are updated.
----------------------------------------------------------------------
--
Øystein
Re: How to update cardinality statistics
Posted by Mike Matrigali <mi...@sbcglobal.net>.
Would need to see the exact documentation reference, but this is not
the case. The storage system keeps an estimate of the number of
rows in a table. For performance reasons this estimate is not exact
(ie. we don't update a single could for every insert/delete/abort
insert/abort delete/commit). The actual update is tied to the time
pages move out of the cache. Because the number can drift there is
an optimization in the language execution engine that if it has just
executed a complete scan of the table then it updates the value
in the storage system.
But in almost all cases the automatic maintained row count maintained
by store is good enough and never needs to be updated. The scan
optimization is there to automatically catch the drift if it is
cheap to do so, it wasn't really meant for users to use to explicitly
update it (it was take advantage if a user happened to be executing
a complete scan for some other reason).
Øystein Grøvlen wrote:
>>>>>>"MM" == Mike Matrigali <mi...@sbcglobal.net> writes:
>
>
> MM> I 2nd Satheesh's query, it is useful to know why you care. Derby
> MM> unlike most other database's automatically maintains histogram
> MM> type information about the tables (this does require indexes to
> MM> exist). This information is gathered directly from the indexes,
> MM> and is automatically maintained by Derby.
>
> MM> The row count is also automatically maintained by Derby - whether
> MM> an index exists or not.
>
> Sounds good, but I think read somewhere in the Derby documentation
> that some statistical information was only updated when a query did a
> sequential scan of the table.
>
Re: How to update cardinality statistics
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
>>>>> "MM" == Mike Matrigali <mi...@sbcglobal.net> writes:
MM> I 2nd Satheesh's query, it is useful to know why you care. Derby
MM> unlike most other database's automatically maintains histogram
MM> type information about the tables (this does require indexes to
MM> exist). This information is gathered directly from the indexes,
MM> and is automatically maintained by Derby.
MM> The row count is also automatically maintained by Derby - whether
MM> an index exists or not.
Sounds good, but I think read somewhere in the Derby documentation
that some statistical information was only updated when a query did a
sequential scan of the table.
--
Øystein
Re: How to update cardinality statistics
Posted by Mike Matrigali <mi...@sbcglobal.net>.
I 2nd Satheesh's query, it is useful to know why you care. Derby
unlike most other database's automatically maintains histogram
type information about the tables (this does require indexes to
exist). This information is gathered directly from the indexes,
and is automatically maintained by Derby.
The row count is also automatically maintained by Derby - whether
an index exists or not.
A goal of Derby is to be zero-admin so we are interested in problems
that users encounter which make them think they are required to run
admin type tools. Do note that Derby does require users to appropriate
secondary indexes on their tables, once those indexes are created derby
should be able to automatically pick the correct plan.
Satheesh Bandaram wrote:
> Why exactly do you need to know? Do you suspect they are wrong?
>
> The row count info is stored internally, not in SYS.SYSSTATISTICS.
> Statistics table contains some more additional statistics, which are
> collected during index creation. (or backing index creation) If you
> create an index on your table, you would see a row there.
>
> Satheesh
>
> Radovan Biciste wrote:
>
>
>>Hello Satheesh,
>>I'm not sure why I missed that part of documentation, sorry.
>>I've checked SYS.SYSSTATISTICS and it is empty.
>>In the documentation there is also note "If statistics are turned off
>>system wide".
>>Is there any way how to check that my statistics are up to date or at
>>least existing ? I'm running embedded server.
>>Thank you,
>>Radovan
>>
>>
>>Satheesh Bandaram wrote:
>>
>>
>>>Take a look at Tuning Derby manual page:
>>>http://db.apache.org/derby/docs/10.1/tuning/ctunstats848901.html
>>>
>>>This provides several ways to update cardinality information.
>>>
>>>Satheesh
>>>
>>>Radovan Biciste wrote:
>>>
>>>
>>>>Hello,
>>>>I'm very new to derby. I was looking how to update cardinality
>>>>statistics. I have joins between tables with couple hundreds of rows
>>>>and about 600 thousands of rows. I'm using embedded server version
>>>>10.1.1 .0.
>>>>Here is what I've found so far:
>>>>1. UPDATE STATISTICS FOR TABLE mytable is giving me a syntax error.
>>>>Should work on Cloudscape though.
>>>>2. SYSCS_UTIL.SYSCS_COMPRESS_TABLE should update statistics but does
>>>>not make too much sense as I do only inserts. It would take tooo much
>>>>time.
>>>>
>>>>Am I missing something ?
>>>>Thank you very much,
>>>>Radovan
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Re: How to update cardinality statistics
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Why exactly do you need to know? Do you suspect they are wrong?
The row count info is stored internally, not in SYS.SYSSTATISTICS.
Statistics table contains some more additional statistics, which are
collected during index creation. (or backing index creation) If you
create an index on your table, you would see a row there.
Satheesh
Radovan Biciste wrote:
> Hello Satheesh,
> I'm not sure why I missed that part of documentation, sorry.
> I've checked SYS.SYSSTATISTICS and it is empty.
> In the documentation there is also note "If statistics are turned off
> system wide".
> Is there any way how to check that my statistics are up to date or at
> least existing ? I'm running embedded server.
> Thank you,
> Radovan
>
>
> Satheesh Bandaram wrote:
>
>> Take a look at Tuning Derby manual page:
>> http://db.apache.org/derby/docs/10.1/tuning/ctunstats848901.html
>>
>> This provides several ways to update cardinality information.
>>
>> Satheesh
>>
>> Radovan Biciste wrote:
>>
>>> Hello,
>>> I'm very new to derby. I was looking how to update cardinality
>>> statistics. I have joins between tables with couple hundreds of rows
>>> and about 600 thousands of rows. I'm using embedded server version
>>> 10.1.1 .0.
>>> Here is what I've found so far:
>>> 1. UPDATE STATISTICS FOR TABLE mytable is giving me a syntax error.
>>> Should work on Cloudscape though.
>>> 2. SYSCS_UTIL.SYSCS_COMPRESS_TABLE should update statistics but does
>>> not make too much sense as I do only inserts. It would take tooo much
>>> time.
>>>
>>> Am I missing something ?
>>> Thank you very much,
>>> Radovan
>>>
>>>
>>>
>>
>>
>
>
>
Re: How to update cardinality statistics
Posted by Radovan Biciste <rb...@softhome.net>.
Hello Satheesh,
I'm not sure why I missed that part of documentation, sorry.
I've checked SYS.SYSSTATISTICS and it is empty.
In the documentation there is also note "If statistics are turned off
system wide".
Is there any way how to check that my statistics are up to date or at
least existing ? I'm running embedded server.
Thank you,
Radovan
Satheesh Bandaram wrote:
> Take a look at Tuning Derby manual page:
> http://db.apache.org/derby/docs/10.1/tuning/ctunstats848901.html
>
> This provides several ways to update cardinality information.
>
> Satheesh
>
> Radovan Biciste wrote:
>
>> Hello,
>> I'm very new to derby. I was looking how to update cardinality
>> statistics. I have joins between tables with couple hundreds of rows
>> and about 600 thousands of rows. I'm using embedded server version
>> 10.1.1 .0.
>> Here is what I've found so far:
>> 1. UPDATE STATISTICS FOR TABLE mytable is giving me a syntax error.
>> Should work on Cloudscape though.
>> 2. SYSCS_UTIL.SYSCS_COMPRESS_TABLE should update statistics but does
>> not make too much sense as I do only inserts. It would take tooo much
>> time.
>>
>> Am I missing something ?
>> Thank you very much,
>> Radovan
>>
>>
>>
>
>
Re: How to update cardinality statistics
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Take a look at Tuning Derby manual page:
http://db.apache.org/derby/docs/10.1/tuning/ctunstats848901.html
This provides several ways to update cardinality information.
Satheesh
Radovan Biciste wrote:
> Hello,
> I'm very new to derby. I was looking how to update cardinality
> statistics. I have joins between tables with couple hundreds of rows
> and about 600 thousands of rows. I'm using embedded server version
> 10.1.1 .0.
> Here is what I've found so far:
> 1. UPDATE STATISTICS FOR TABLE mytable is giving me a syntax error.
> Should work on Cloudscape though.
> 2. SYSCS_UTIL.SYSCS_COMPRESS_TABLE should update statistics but does
> not make too much sense as I do only inserts. It would take tooo much
> time.
>
> Am I missing something ?
> Thank you very much,
> Radovan
>
>
>