Thomas,
What can you expect if Firebird needs temporary disk space which isn't
available to create/activate an index?
I expect the statement to fail but but not corrupt the state of the
database. Similarly to how a query with a large sort would fail if there is
not enough disk space for the
@yahoogroups.com, Alec Swan wrote:
My main question is how can we calculate the temp sort space required
to ALTER INDEX ACTIVE (on all indexes serially) given the size of the
database? Is twice the size of the database a good upper bound?
Enumerate all fields in index, calculate summa of full
Thomas, the index used to exist but for some reason it was lost, probably
because of the lack of disk space during index rebuild. Firebird should not
leave the database in a bad state like this if ALTER INDEX ACTIVATE fails
because of lack of disk space!
Alec
On Thu, Jun 7, 2012 at 12:27 PM,
UNION use case makes sense, thanks!
On Mon, Jun 4, 2012 at 1:29 AM, Svein Erling Tysvær
svein.erling.tysv...@kreftregisteret.no wrote:
**
I consider this issue resolved with one side note that having PLAN hint
which affects ORDER BY execution
before ORDER BY clause is misleading.
Don't
Actually, it doesn't. The order of joins is determined by the optimizer in
the case of an inner join, but outer joins determine the order
semantically. Consider this case:
so, if FB optimizes INNER JOIN why didn't it use a more selective index on
commit_number instead of less selective index
Thanks, Mark.
I found FB 2.5 SQL Language reference here
http://www.firebirdsql.org/refdocs/langrefupd25-select.html but it does
not contain the grammar for the entire SELECT statement, just individual
fragments, such as JOIN, ORDER BY, etc.
Where can I find a comprehensive syntax definition
I am sorry for the last post I did not see that Mark already answered it.
Is there online docs for SELECT syntax?
Thank you, Mark.
On Sun, Jun 3, 2012 at 8:59 AM, Alec Swan alecs...@gmail.com wrote:
Thanks, Mark.
I found FB 2.5 SQL Language reference here
http://www.firebirdsql.org/refdocs
Sean, you nailed the reason on the head - the FB restriction on index
names. In fact, on SQL Server we have pretty index names :) Unfortunately,
we didn't have the resources to spend in prettyfying the index names at the
time. At this point it's even harder to do this because of upgrade
scenarios.
Thank you!
I consider this issue resolved with one side note that having PLAN hint
which affects ORDER BY execution before ORDER BY clause is misleading.
Alec
On Sun, Jun 3, 2012 at 1:15 PM, Michael Ludwig mil...@gmx.de wrote:
**
Alec Swan schrieb am 03.06.2012 um 09:01 (-0600
Sean,
I would like to note that the fact that LEFT JOIN can generate an optimal
plan where INNER JOIN fails indicates that all our index statistics are
up-to-date and that there is some discrepancy between LEFT and INNER JOIN
optimization that has already bit us several times. The good thing
Hello,
I am using FB 2.5 and execute SQL using Flamerobin. I am trying to
specify a plan for a query that has ORDER BY clause. I get a invalid
token PLAN error if I specify the plan after ORDER BY, but it works
OK if I specify the plan before ORDER BY. But the plan contains
instructions for ORDER
I would have never expected a parser token error if the query syntax is
correct but the plan itself is bad. That would have been a run-time, not
compile-time error!
And I just verified that what I am saying looks like an FB bug. Here is a
very simple example that reproduces the bug:
create table
version?
Also, is it safe to use FIREBIRD_LOCK env var to control where FB
writes its lock files?
Thanks,
Alec
On Thu, May 31, 2012 at 1:53 PM, Alec Swan alecs...@gmail.com wrote:
I haven't received any responses in 10 days. Maybe the question is not
clear, so I will restate it.
How can I
to address this asap. Can anyone suggest a solution?
Thanks,
Alec
On Fri, Jun 1, 2012 at 11:28 AM, Michael Ludwig mil...@gmx.de wrote:
**
Alec Swan schrieb am 01.06.2012 um 10:16 (-0600):
I verified that FIREBIRD_TMP environment variable can be used to
control where temporary files are written
Hello,
We tracked down the query that generated a 10GB temp file running
against a 1.5GB database. Can anybody explain why the query is not
using an index on PHYSICAL_COPY.COMMIT_NUMBER?
Thanks,
Alec
Preparing query: SELECT FIRST (1000) PHYSICAL_COPY.ID,
PHYSICAL_COPY.COMMIT_NUMBER,
I haven't received any responses in 10 days. Maybe the question is not
clear, so I will restate it.
How can I configure the location of all Firebird temporary, sort and
lock files and control their sizes?
Thanks,
Alec
On Mon, May 21, 2012 at 1:10 PM, Alec Swan alecs...@gmail.com wrote:
I
Hello,
Our customers are complaining that there are lots of (GBs) temp files
written by Firebird in temp directory. Is there a way to have embedded
Firebird 2.5 to clean up temp files, e.g. lock files, after it's done
with them?
Thanks,
Alec
I found the following setting in
http://ibexpert.net/ibe/index.php?n=Doc.ConfiguringFirebird#FBConf:
TempDirectories = c:\temp 1
I am assuming that 1 is in bytes, correct? What happens when
this limit is reached?
Thanks,
Alec
On Mon, May 21, 2012 at 1:00 PM, Alec Swan alecs
Set and Arno,
Thank you both of you for your solutions! Arno's solution required swapping
the order of PROJECT and PROJECT_CODE_DESCRIPTOR tables in the join and
use LEFT JOIN instead of INNER JOIN to join them. This is so simple and the
results are amazing.
PLAN SORT (JOIN (JOIN (JOIN (PROJECT
Hello Set,
Your guesswork worked quite well. Your query executes 20 times faster than
my original query! Here are the stats for your query:
PLAN SORT (JOIN (JOIN (JOIN (SORT (TMP P NATURAL), PCD INDEX
(FK_zDTEgB/EMb14zlRjEdzCZw==)), CC INDEX (IDX_epDQN2rI7u0hoaXiWuFXAw==)),
JOIN (C INDEX
Hello,
I need help optimizing the query shown below. PROJECT-related tables
contain 12 rows each while COPY-related tables contain 14K rows each.
Moreover, PROJECT.ASSIGNMENT_STATUS != 'UNASSIGNED' filters out all
but 10 joined rows.
My question is how do I change the plan to force
21 matches
Mail list logo