You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Mike Matrigali <mi...@sbcglobal.net> on 2005/07/20 20:34:50 UTC
any explanation of pointbase performance in results posted in DERBY-465?
Do they automatically create indexes?
The results posted in DERBY-465 seem to indicate that somehow
pointbase can execute select * from table where nonkey=5000 at
a constant performance no matter how many rows in the table with
no index on "nonkey".
Not surprisingly derby performance on this query is linearly
related to the size of the table with no index. Performance is
constant when an index is added.
Does anyone know if pointbase somehow automatically creates
and maintains indexes, maybe as directed by the optimizer?
Re: any explanation of pointbase performance in results posted in DERBY-465? Do they automatically create indexes?
Posted by Francois Orsini <fr...@gmail.com>.
Pointbase keeps track of the execution plan information for a query
into 2 tables named PLAN_QUERIES and PLAN_TABLE (private to the
current user) - it would be interesting to see what plan has been
chosen for the concerned queries...
The PLAN_TABLE table will indicate if an index has been used for a
particular query ID...
You could also issue:
SET PLANONLY ON | OFF (to generate execution plan only)
_or_
SET SHOWPLAN ON | OFF (to display the plan)
For such a simple query, creating an index dynamically could be a real
hit depending on the number of rows, etc...never heard of it,
especially as this is not a range query....
--francois
On 7/20/05, Mike Matrigali <mi...@sbcglobal.net> wrote:
> The results posted in DERBY-465 seem to indicate that somehow
> pointbase can execute select * from table where nonkey=5000 at
> a constant performance no matter how many rows in the table with
> no index on "nonkey".
>
> Not surprisingly derby performance on this query is linearly
> related to the size of the table with no index. Performance is
> constant when an index is added.
>
> Does anyone know if pointbase somehow automatically creates
> and maintains indexes, maybe as directed by the optimizer?
>