Re: [firebird-support] Re: TempDirectories and ALTER INDEX ACTIVE
Thanks Vlad, but this is too complicated to give as a guidance to our customers. However, it sounds like rebuilding an index cannot require more space that the database size itself, right? On Fri, Jun 8, 2012 at 7:22 AM, hvlad wrote: > ** > > > > > --- In firebird-support@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 size of every > field (for [var]char(N) it is N * bytes_per_char, for ex.), add extra 8 > bytes (for record number) - this is sort record size. > Multiply it by number of records and you'll get a lower estimate of > temp space required. Substract value of TempCacheLimit and you'll get > estimate of disk space required. > > Regards, > Vlad > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] URGENT: Invalid request BLR
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 temporary sort file and doesn't corrupt the database. Thanks, Alec [Non-text portions of this message have been removed]
Re: [firebird-support] URGENT: Invalid request BLR
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, Thomas Steinmaurer wrote: > ** > > > > We are getting the following error during a SELECT-query and gfix with > > gback do not fix it. > > > > org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544343. invalid > > request BLR at offset 226 > > there is no index IDX_p39A2dTscKu7xw43c5lrjQ== for table COPY > > > > The customer is really frustrated, what should we do? > > Are you forcing the optimizer to use an index via an explicit PLAN, but > the index doesn't exist? > > Regards, > Thomas > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] URGENT: Invalid request BLR
Hello, We are getting the following error during a SELECT-query and gfix with gback do not fix it. org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544343. invalid request BLR at offset 226 there is no index IDX_p39A2dTscKu7xw43c5lrjQ== for table COPY The customer is really frustrated, what should we do? Thanks, Alec
Re: [firebird-support] Re: Embedded Firebird leaves a lot of temp files behind
I confirmed that TempDirectories works with our embedded Firebird database. "Success!" I thought yet to find out that TempDirectories doesn't work completely with ALTER INDEX ACTIVE (see my latest thread). I also confirmed that Flamerobin still works even if I delete firebird.conf from directory where flamerobin_unicode.exe resides. So, Flamerobin must have firebird.conf packaged internally, which sounds kind of weird. Thank you to all who contributed to this discussion. It was fun ;-) Alec On Sun, Jun 3, 2012 at 12:17 PM, Alec Swan wrote: > Vlad, > > Actually, FB correctly deletes fb_sort file used by a transaction after > the transaction completes. However, we have a lot of long-running > concurrent transactions which generate a lot of large fb_sort files. > Moreover, when our customer rebuilt indexes they ran out of system disk > space (since this is where %TEMP% points to) and left FB in corrupt state. > > I am using Flamrobin and set TempDirectories in firebird.conf in the same > directory as flamerobin_unicode.exe. Let me test it in other environments. > > Thanks! > > Alec > > > On Sun, Jun 3, 2012 at 11:16 AM, hvlad wrote: > >> ** >> >> >> >> >> --- In firebird-support@yahoogroups.com, Alec Swan wrote: >> > >> > Vlad, the large files have fb_sort prefix. But we also see a lot of >> small >> > fb_lock files. >> >> They could be left only if engine was not shutted down correctly. >> >> fb_sort_XXX files left means application was crushed. >> >> fb_lock_XXX files are per database and will be reused at next connect. >> I don't think it is an issue unless you works with different databases >> every time. Note, since v2.5 Firebird embedded doesn't put lock files >> into temp folder, so this files was left by another embedded Firebrid >> version. >> >> Make sure you close all attachments before program exits. It would be >> the best if you call fb_shutdown() before exit. Also, you can cleanup >> that files at your program startup before first call of ISC API. >> >> >> > Lester, we are a Windows-based shop and most of our customers have FB >> > databases are under 200MB. There are just a few that have 10-20GB >> databases. >> > >> > Our customers need the ability to put those files on a separate disk and >> > restrict their cumulative size. TempDirectories setting would have been >> > perfect for it, but I can't get it to work with embedded Firebird. >> > >> > If its is supposed to work in embedded mode then I will spend more time >> > making sure that I am not doing something wrong before taking any more >> of >> > your time. >> >> It must work. Sooner of all engine can't find firebird.conf. For v2.5 it >> should be placed at the same folder as fbembed.dll. >> >> Regards, >> Vlad >> >> >> > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] TempDirectories and ALTER INDEX ACTIVE
Hello, Our app rebuilds indexes periodically (about once a month) by calling "ALTER INDEX index_name ACTIVE" on a bunch of indexes in the database. I need to estimate how much free disk space the customer should have in order to complete this process. I decided to restrict TempDirectories size, run the command and see when it starts failing. Here are my findings: 1. TempDirectories = C:\Temp\Firebird Running "ALTER INDEX idx_physical_copy_commit_number ACTIVE" succeeds 2. TempDirectories = C:\Temp\Firebird 1000 Running "ALTER INDEX idx_physical_copy_commit_number ACTIVE" causes "GDS Exception. 335544675. sort error" The database size is 1.5GB so 100 GB should be more than enough to activate any index, right? 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? We will provide this as a recommendation to our customers, so it has to be simple :) Thanks, Alec
Re: [firebird-support] Slow query because an index is not picked up during sort
SET thanks, it makes sense. So, Firebird is unable to pick the right plan for >= because it doesn't have histograms, right? On Tue, Jun 5, 2012 at 9:55 AM, Ann Harrison wrote: > ** > > > > > > > > > > > I am assuming you meant to use LEFT not INNER JOIN, right? > > > > Right - and right that I got SAP and SAS confused (again). Typing, > talking, and trying to get the dog to stop emptying the trash should not be > done simultaneously. > > > Ann > > [Non-text portions of this message have been removed] > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Slow query because an index is not picked up during sort
> > 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 on source_id? Especially given that there was an ORDER BY on commit_number? > select c.city, s.stateName > from cities c > inner join states s on c.stateCode = s.stateCode > where c.population > 100 > > I am assuming you meant to use LEFT not INNER JOIN, right? Thanks, Alec [Non-text portions of this message have been removed]
Re: [firebird-support] Why PLAN token is not allowed after ORDER BY?
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 forget UNION, Alec. I.e. > > select * from T > where a = 1 > PLAN (T INDEX (IDX_TA)) > union > select * from T > where a = 2 > PLAN (T INDEX (IDX_TA)) > order by 1 > > makes it quite simple to see why the PLAN clause has to be before the > ORDER BY. Of course, I have no problems understanding that having the > option of specifying ORDER BY before PLAN in simple selects could be easier > to read (particularly when the plan involves ORDER as opposed to INDEX). > > Set > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Slow query because an index is not picked up during sort
Svein, the selectivity of source_id is 0.000446 and commit_number is 0.01. So, it is not clear why FB is not using index on commit_number to filter AND sort on. Thanks, Alec On Mon, Jun 4, 2012 at 1:11 AM, Svein Erling Tysvær < svein.erling.tysv...@kreftregisteret.no> wrote: > ** > > > >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 about LEFT is that it pushes SORT > down to the table and uses an index on it whereas > >INNER does SORT after the join. > > Firebird doesn't have histograms yet, and logically speaking (I do not > know the internals of the optimizer, nor the selectivity of your indexes, > so this is how I would think if I should do things manually): > > COPY."SOURCE_ID" = '123431234' seems fairly selective, whereas > > PHYSICAL_COPY."COMMIT_NUMBER" >= 1000 seems like something that would > match 50% (a bit less if considering NULLs) of the records. > > Naturally, if I had to choose how to do this, I would think using the > index for COPY would be far superior to using the index for PHYSICAL_COPY > and INNER JOIN Firebird makes the same conclusion as I would have done (and > as a second table, it is also natural to prefer the index for > PHYSICAL_COPY.COPY_ID over PHYSICAL_COPY.COMMIT_NUMBER). > > Adding LEFT, however, reduces the options for the optimizer. That word > basically commands Firebird to consider PHYSICAL_COPY before COPY, and > hence, it has to find an alternative plan (as a first table in the plan, > PHYSICAL_COPY.COPY_ID is not an option, and PHYSICAL_COPY.COMMIT_NUMBER is > the only choice besides NATURAL). You've shown us that this alternative > plan in your situation is a lot better than the other, to me that basically > says that 'COMMIT_NUMBER >= 1000' actually is quite selective. Change > that to 'COMMIT_NUMBER" >= -1000' and it might be that LEFT JOIN will > be as time consuming (or more) as the INNER JOIN. At prepare time, Firebird > has no way of knowing that for values below a certain value it should use > one plan, whereas for another value it should use a completely different > plan (that might change with histograms). > > HTH, > Set > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Slow query because an index is not picked up during sort
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 about LEFT is that it pushes SORT down to the table and uses an index on it whereas INNER does SORT after the join. Now back to your questions. I had to modify your first query a little for it to compile. Here are the stats: Preparing query: SELECT FIRST (1000) PHYSICAL_COPY."ID" FROM ( SELECT PHYSICAL_COPY."ID" FROM PHYSICAL_COPY INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID" WHERE (PHYSICAL_COPY."IS_DIRTY" = 1) AND (COPY."SOURCE_ID" = '5d74876f-1b14-466a-aeee-f000b8b16512') AND (PHYSICAL_COPY."COMMIT_NUMBER" >= 1000) ORDER BY PHYSICAL_COPY."COMMIT_NUMBER" ) AbstractTable INNER JOIN PHYSICAL_COPY on PHYSICAL_COPY."ID" = AbstractTable."ID" INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID" INNER JOIN COPY_CLASSIFICATION ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID" Prepare time: 0.204s Field #01: PHYSICAL_COPY.ID Alias:ID Type:STRING(38) PLAN JOIN (JOIN (SORT (JOIN (ABSTRACTTABLE COPY INDEX (IDX_Lhi+/ZlWWZ/FDOab6YV2Vg==), ABSTRACTTABLE PHYSICAL_COPY INDEX (IDX_AlJS5EmMT9tODQnFqmid0w==))), PHYSICAL_COPY INDEX (PK_lx3z+dt9CnekKkF+ZE8NIA==)), JOIN (COPY INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==), COPY_CLASSIFICATION INDEX (IDX_soqMJd+Yux0RNvCbmE9rrg==))) Executing... Done. 6262754 fetches, 0 marks, 808411 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 1563528 index, 0 seq. Delta memory: 527584 bytes. Total execution time: 0:01:58 (hh:mm:ss) Script execution finished. Here is your second query (has very high reads): Preparing query: SELECT PHYSICAL_COPY."ID" FROM COPY INNER JOIN PHYSICAL_COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID" INNER JOIN COPY_CLASSIFICATION ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID" WHERE (PHYSICAL_COPY."IS_DIRTY" = 1) AND (COPY."SOURCE_ID" = '5d74876f-1b14-466a-aeee-f000b8b16512') AND (PHYSICAL_COPY."COMMIT_NUMBER" >= 1000) ORDER BY PHYSICAL_COPY."COMMIT_NUMBER" ASC Prepare time: 0.015s Field #01: PHYSICAL_COPY.ID Alias:ID Type:STRING(38) PLAN SORT (JOIN (COPY INDEX (IDX_Lhi+/ZlWWZ/FDOab6YV2Vg==), PHYSICAL_COPY INDEX (IDX_AlJS5EmMT9tODQnFqmid0w==), COPY_CLASSIFICATION INDEX (IDX_soqMJd+Yux0RNvCbmE9rrg==))) Executing... Done. 6262689 fetches, 0 marks, 808409 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 1563505 index, 0 seq. Delta memory: 517200 bytes. Total execution time: 47.437s Script execution finished. On Sun, Jun 3, 2012 at 3:27 PM, Leyne, Sean wrote: > ** > > > Alec, > > > > Sean, > > > > The query you proposed will not produce the same result because you are > > sorting and topping the join before applying the filter. > > You are correct. > > But a small change to my proposed query would resolve that issue, the > new/corrected query would be: > > > SELECT > FIRST (1000) > PHYSICAL_COPY."ID", ... > FROM ( > SELECT > PHYSICAL_COPY."ID" > FROM PHYSICAL_COPY > INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID" ) AbstractTable > WHERE > (PHYSICAL_COPY."IS_DIRTY" = 1) AND > (COPY."SOURCE_ID" = '123431234') AND > (PHYSICAL_COPY."COMMIT_NUMBER" >= 1000) > ORDER BY > PHYSICAL_COPY."COMMIT_NUMBER" > ) AbstractTable > INNER JOIN PHYSICAL_COPY on PHYSICAL_COPY."ID" = AbstractTable."ID" > INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID" > INNER JOIN COPY_CLASSIFICATION ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID" > > > We have a one-to-one relationship between PHYSICAL_COPY and COPY and > > COPY_CLASSIFICATION. So, the solution I went with was to replace INNER > > JOINs with LEFT JOINs which changed the execution plan from (I translated > > index names): > > > This is the second time (see my older post "Force query plan to filter > before > > join") where LEFT join saved the day. It would be nice to understand why > > LEFT join optimization works so much better for us. Is there an > explanation of > > FB optimization techniques available somewhere? > > What is the performance of your query like if you change the query to be: > > > SELECT > PHYSICAL_COPY."ID", ... > FROM COPY > INNER JOIN PHYSICAL_COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID" > > INNER JOIN COPY_CLASSIFICATION ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID" > WHERE ... > > (the position of COPY and PHYSICAL_COPY in the query was changed, but the > defined relationship is the same) > > Sean > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/
Re: [firebird-support] Slow query because an index is not picked up during sort
Sean,
The query you proposed will not produce the same result because you
are sorting and topping the join before applying the filter. The
original query was joining and filtering first and after that sorting
and topping. So, your query will always return a subset of rows of the
original query.
The selectivity of IS_DIRTY is 0.5 - a record is either dirty or not.
We have a one-to-one relationship between PHYSICAL_COPY and COPY and
COPY_CLASSIFICATION. So, the solution I went with was to replace INNER
JOINs with LEFT JOINs which changed the execution plan from (I
translated index names):
PLAN SORT (JOIN (COPY INDEX ("IDX_COPY_source_id"), PHYSICAL_COPY
INDEX ("IDX_PHYSICAL_COPY_copy_id"), COPY_CLASSIFICATION INDEX
("IDX_COPY_CLASS_copy_id")))
-- takes about 3 minutes to execute
to
PLAN JOIN (JOIN (PHYSICAL_COPY ORDER "IDX_PHYSICAL_COPY_commit_number"
INDEX ("IDX_PHYSICAL_COPY_commit_number",
"IDX_PHYSICAL_COPY_is_dirty"), COPY INDEX ("PK_COPY_id")),
COPY_CLASSIFICATION INDEX ("IDX_COPY_CLASS_copy_id"))
-- takes about 200ms to execute
This is the second time (see my older post "Force query plan to filter
before join") where LEFT join saved the day. It would be nice to
understand why LEFT join optimization works so much better for us. Is
there an explanation of FB optimization techniques available
somewhere?
Thanks,
Alec
On Sun, Jun 3, 2012 at 12:53 PM, Leyne, Sean wrote:
>
>
>
> Alex,
>
> > 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"?
>
> Cause the optimizer decided that the "cost" of navigating that index was more
> than filtering the result set and then performing a sort. The optimizer is
> concerned about the disk IO cost of a query, as such navigating an index is
> very expensive since rows are generally stored in index order so, reading
> each row from different areas of disk is very expensive.
>
> BTW, your post gives us no real details in which to answer your question. We
> don't know what fields/columns the PLAN refers to -- the hackish names
> obscure those details. Further, we don't know what the selectivity of the
> PHYSICAL_COPY."IS_DIRTY" (the index I assume the optimizer selected).
>
> At that said, the fact that the temp file was so much larger than the
> database size leads me to believe that rather than focus on the query PLAN,
> you need to re-think the basic query.
>
> Your original select was:
>
> SELECT
> PHYSICAL_COPY."ID", ...
>
>
> FROM PHYSICAL_COPY
> INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
> INNER JOIN COPY_CLASSIFICATION ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID"
> WHERE
> (PHYSICAL_COPY."IS_DIRTY" = 1) AND
> (COPY."SOURCE_ID" = '123431234') AND
> (PHYSICAL_COPY."COMMIT_NUMBER" >= 1000)
> ORDER BY
> PHYSICAL_COPY."COMMIT_NUMBER"
>
> If you were to modify the query to read as follows, you would almost
> completely eliminate the size of the temp file, and significantly increase
> the performance of the query.
>
> SELECT
> PHYSICAL_COPY."ID", ...
> FROM (
>
> SELECT
> FIRST (1000) PHYSICAL_COPY."ID"
> FROM PHYSICAL_COPY
> INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID" ) AbstractTable
>
> WHERE
> (PHYSICAL_COPY."IS_DIRTY" = 1) AND
> (COPY."SOURCE_ID" = '123431234') AND
> (PHYSICAL_COPY."COMMIT_NUMBER" >= 1000)
> ORDER BY
> PHYSICAL_COPY."COMMIT_NUMBER"
> ) AbstractTable
> INNER JOIN PHYSICAL_COPY on PHYSICAL_COPY."ID" = AbstractTable."ID"
>
> INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
> INNER JOIN COPY_CLASSIFICATION ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID"
>
> Sean
>
>
++
Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
Re: [firebird-support] Why PLAN token is not allowed after ORDER BY?
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 wrote: > ** > > > Alec Swan schrieb am 03.06.2012 um 09:01 (-0600): > > > > > Is there online docs for SELECT syntax? > > There's an online HTML version of the SQL reference linked to from the > overview page: > > http://www.ibphoenix.com/files/60sqlref.html > > > > On Sun, Jun 3, 2012 at 8:59 AM, Alec Swan wrote: > > > > > > 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 for the entire > > > SELECT statement? > > There's no complete one-stop doc for the whole thing yet, as far as I > know. Guess most people are happy enough using the old InterBase 6.0 > docs (links at the bottom of the doc overview page) supplemented by > the Firebird additions you've found. All here: > > http://www.firebirdsql.org/en/reference-manuals/ > > From the "InterBase 6.0 Manuals" section, download either the "Full Set" > or just the "Language Reference". > > Producing a comprehensive doc would require a lot of work, and there > might also be copyright issues for the old InterBase 6.0 docs. > > Michael > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Why PLAN token is not allowed after ORDER BY? - Email found in subject
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. Thanks, Alec On Sun, Jun 3, 2012 at 1:06 PM, Leyne, Sean wrote: > ** > > > > > We create indexes programmatically and we we generate their names based > > on their definition, such the columns they index. > > If that is the case, why is the name gibberish? > > You could create an index which has the name(s) of the source fields (ie. > "PK_PhysicalCopy.ID" or "FK_PhysicalCopy.CopyID" or "PC.ISDIRTY"), rather > than the GUID-like non-names which you currently use. > > Yes, there is a limit to the length of an Firebird index name, but you > could come up with an algorithm which would be able to shorten the names as > appropriate. For example an compound index of > COPY_CLASSIFICATION."CLASSIFICATION_CODE" and > COPY_CLASSIFICATION."LABEL_CLASSIFICATION_CODE" could be named > "COPY_CLASS.CLASSIF_LABELCLASSIF". > > Sean > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: Re[2]: [firebird-support] Re: Embedded Firebird leaves a lot of temp files behind
Dmitry, Thanks for clearing up when fb_sort files are being deleted. We are certainly looking for ways to "Just fix your application", however with complex logic-intensive applications it's not always that simple. "change %TEMP%" is just not going to work in any enterprise environment. You can strong-arm your single-license user into doing this but not the IT departments of companies with thousands of user. Thanks, Alec On Sun, Jun 3, 2012 at 12:26 PM, Dmitry Kuzmenko wrote: > ** > > > Hello, Alec! > > Sunday, June 3, 2012, 10:17:05 PM, you wrote: > > AS> Actually, FB correctly deletes fb_sort file used by a transaction > after the > AS> transaction completes. > > wrong. sort files being deleted when query that have plan sort fetched > to EOF, i.e. to the last record. Same happens, for example, when > group by query sort a huge resultset and result fits in memory. > > If sort file left in temp, then application was terminated or failed, > or computer was powered off or reset. > Nothing to the transactions. > > AS> However, we have a lot of long-running concurrent > AS> transactions which generate a lot of large fb_sort files. > > long-running transactions is a different issue. Just fix your application. > > AS> Moreover, when our customer rebuilt indexes they ran out of system > disk space (since this > AS> is where %TEMP% points to) and left FB in corrupt state. > > change %TEMP%. > > -- > Dmitry Kuzmenko, www.ib-aid.com > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Re: Embedded Firebird leaves a lot of temp files behind
Vlad, Actually, FB correctly deletes fb_sort file used by a transaction after the transaction completes. However, we have a lot of long-running concurrent transactions which generate a lot of large fb_sort files. Moreover, when our customer rebuilt indexes they ran out of system disk space (since this is where %TEMP% points to) and left FB in corrupt state. I am using Flamrobin and set TempDirectories in firebird.conf in the same directory as flamerobin_unicode.exe. Let me test it in other environments. Thanks! Alec On Sun, Jun 3, 2012 at 11:16 AM, hvlad wrote: > ** > > > > > --- In firebird-support@yahoogroups.com, Alec Swan wrote: > > > > Vlad, the large files have fb_sort prefix. But we also see a lot of small > > fb_lock files. > > They could be left only if engine was not shutted down correctly. > > fb_sort_XXX files left means application was crushed. > > fb_lock_XXX files are per database and will be reused at next connect. > I don't think it is an issue unless you works with different databases > every time. Note, since v2.5 Firebird embedded doesn't put lock files > into temp folder, so this files was left by another embedded Firebrid > version. > > Make sure you close all attachments before program exits. It would be > the best if you call fb_shutdown() before exit. Also, you can cleanup > that files at your program startup before first call of ISC API. > > > > Lester, we are a Windows-based shop and most of our customers have FB > > databases are under 200MB. There are just a few that have 10-20GB > databases. > > > > Our customers need the ability to put those files on a separate disk and > > restrict their cumulative size. TempDirectories setting would have been > > perfect for it, but I can't get it to work with embedded Firebird. > > > > If its is supposed to work in embedded mode then I will spend more time > > making sure that I am not doing something wrong before taking any more of > > your time. > > It must work. Sooner of all engine can't find firebird.conf. For v2.5 it > should be placed at the same folder as fbembed.dll. > > Regards, > Vlad > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Re: Embedded Firebird leaves a lot of temp files behind
Vlad, the large files have fb_sort prefix. But we also see a lot of small fb_lock files. Lester, we are a Windows-based shop and most of our customers have FB databases are under 200MB. There are just a few that have 10-20GB databases. Our customers need the ability to put those files on a separate disk and restrict their cumulative size. TempDirectories setting would have been perfect for it, but I can't get it to work with embedded Firebird. If its is supposed to work in embedded mode then I will spend more time making sure that I am not doing something wrong before taking any more of your time. Thanks, Alec On Sun, Jun 3, 2012 at 3:15 AM, Lester Caine wrote: > ** > > > Alec Swan wrote: > > We are working on tracking those queries down, but fixing them will > require > > an upgrade which the customer does not want to do. Moreover, their db > size > > is 12GB, so it may not be possible to optimize the queries a lot further. > > > > The customer also ran out of disk space trying to rebuild database > indexes > > which left firebird in a corrupted state. We had to add more space and > run > > gfix to repair it. This is a different problem, but it's also related to > FB > > disk usage. > > > > Is TempDirectories setting expected to work in embedded mode? What > happens > > when max allowed temp space is not big enough for a sort? > > Personally I would not be comfortable with the set-up you seem to be > trying to > run. 12Gb of database really needs it's own server rather than trying to > run it > with just a single embedded connection. I'd also be a lot happier having > that on > a Linux box which is just managing the database. Windows has it's own > 'layers' > of protection that keep files open while a program is running, so I > suspect that > it is windows that is retaining the disk space rather than Firebird. Until > a > program closes windows does not release some resources. > > But more important is to review the queries since a CTE approach may be > able to > apply filtering before you try sorting the extremely large results sets > you seem > to be working with. I have half million to one million record sets of data > these > days and have had to add extra indexes to allow some processing that was > fast > even on the slower machines from 10 years ago, but was getting slower. > > -- > Lester Caine - G8HFL > - > Contact - http://lsces.co.uk/wiki/?page=contact > L.S.Caine Electronic Services - http://lsces.co.uk > EnquirySolve - http://enquirysolve.com/ > Model Engineers Digital Workshop - http://medw.co.uk// > Firebird - http://www.firebirdsql.org/index.php > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Why PLAN token is not allowed after ORDER BY?
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 wrote: > 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 for the entire SELECT > statement? > > Thanks, > > Alec > > > On Sun, Jun 3, 2012 at 2:03 AM, Mark Rotteveel wrote: > >> ** >> >> >> On 2-6-2012 23:17, Dmitry Kuzmenko wrote: >> > Hello, Alec! >> > >> > AS> The question is why FB complains about PLAN token after ORDER BY >> clause? >> > >> > the answer is that PLAN is wrong. ORDER BY in query affects PLAN, it >> > will contain PLAN SORT or TABLE ORDER INDEX specification. Your PLAN >> > specification is crap, sorry for rude answer. >> > As I said before, you need to know how Firebird produces plan >> > specification and how it accepts it. >> >> Dmitry, I think this is not a very constructive way to respond. Alec is >> asking why Firebird does not accept a PLAN after ORDER BY, but it does >> before ORDER BY. >> >> I think the reason is simply that the grammar definition of Firebird >> expects it before the ORDER BY. There is probably nothing more to it. >> >> Telling people they are stupid (my interpretation of your messages) is >> not helpful. >> >> Mark >> -- >> Mark Rotteveel >> >> >> > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Why PLAN token is not allowed after ORDER BY?
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 for the entire SELECT statement? Thanks, Alec On Sun, Jun 3, 2012 at 2:03 AM, Mark Rotteveel wrote: > ** > > > On 2-6-2012 23:17, Dmitry Kuzmenko wrote: > > Hello, Alec! > > > > AS> The question is why FB complains about PLAN token after ORDER BY > clause? > > > > the answer is that PLAN is wrong. ORDER BY in query affects PLAN, it > > will contain PLAN SORT or TABLE ORDER INDEX specification. Your PLAN > > specification is crap, sorry for rude answer. > > As I said before, you need to know how Firebird produces plan > > specification and how it accepts it. > > Dmitry, I think this is not a very constructive way to respond. Alec is > asking why Firebird does not accept a PLAN after ORDER BY, but it does > before ORDER BY. > > I think the reason is simply that the grammar definition of Firebird > expects it before the ORDER BY. There is probably nothing more to it. > > Telling people they are stupid (my interpretation of your messages) is > not helpful. > > Mark > -- > Mark Rotteveel > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: Re[4]: [firebird-support] Why PLAN token is not allowed after ORDER BY?
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 T (a int); create asc index idx_Ta on T (a); select * from T order by a; -- generates PLAN (T ORDER IDX_TA) select * from T order by a PLAN (T ORDER IDX_TA); ERROR: An "Invalid token ... Token unknown - line 4, column 28 PLAN" Is there a workaround for specifying PLAN after ORDER BY? Thanks, Alec On Sat, Jun 2, 2012 at 4:27 PM, Dmitry Kuzmenko wrote: > ** > > > Hello, Alec! > > AS> 2. Changed the query to "A INNER JOIN B ORDER BY X PLAN P", where P is > the > AS> plan generated by FB in #1. Received "Unexpected token PLAN" error. > AS> 3. Change the query to "A INNER JOIN B PLAN P ORDER BY X" - success! > > AS> Why does #3 succeed but #2 fail? > > because inner join is not equal to left join. > > -- > Dmitry Kuzmenko, www.ib-aid.com > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: Re[2]: [firebird-support] Why PLAN token is not allowed after ORDER BY?
Dmitry, If you look at the plans in my original post they both have "PHYSICAL_COPY ORDER "IDX_214/CmDhH936xtHXcXNQKg==" INDEX" just like you suggested. Sorry for the misleading table and index names. Once again, the plan I used was generated by FB query optimizer itself! I did NOT create it by hand. What I did was this: 1. Ran a query which does "A LEFT JOIN B ORDER BY X" and copied the plan that FB generated. 2. Changed the query to "A INNER JOIN B ORDER BY X PLAN P", where P is the plan generated by FB in #1. Received "Unexpected token PLAN" error. 3. Change the query to "A INNER JOIN B PLAN P ORDER BY X" - success! Why does #3 succeed but #2 fail? Thanks, Alec On Sat, Jun 2, 2012 at 3:17 PM, Dmitry Kuzmenko wrote: > ** > > > Hello, Alec! > > AS> The question is why FB complains about PLAN token after ORDER BY > clause? > > the answer is that PLAN is wrong. ORDER BY in query affects PLAN, it > will contain PLAN SORT or TABLE ORDER INDEX specification. Your PLAN > specification is crap, sorry for rude answer. > As I said before, you need to know how Firebird produces plan > specification and how it accepts it. > > -- > Dmitry Kuzmenko, www.ib-aid.com > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Why PLAN token is not allowed after ORDER BY?
Dmitry,
We create indexes programmatically and we we generate their names based on
their definition, such the columns they index. In fact the plan I am trying
to force the query optimizer to use is exactly the plan that was generated
by query optimizer when I replaced INNER with LEFT JOIN in the query.
COPY is the name of the table.
The question is why FB complains about PLAN token after ORDER BY clause?
Thanks,
Alec
On Sat, Jun 2, 2012 at 2:31 PM, Dmitry Kuzmenko wrote:
> **
>
>
> Hello, Alec!
>
> Saturday, June 2, 2012, 11:28:52 PM, you wrote:
>
> AS> specify a plan for a query that has ORDER BY clause. I get a "invalid
> AS> token PLAN" error if I specify the plan after ORDER BY, but it works
>
> AS> COPY INDEX ("PK_ZM6SRonqR8AHSQuCISgvnQ=="))
>
> and, finally, who told you about "COPY" word in a plan,
> or where you got it?
>
> --
> Dmitry Kuzmenko, www.ib-aid.com
>
>
>
[Non-text portions of this message have been removed]
++
Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
[firebird-support] Why PLAN token is not allowed after ORDER BY?
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 BY as well. What's the problem?
This works:
SELECT FIRST (1000) *
FROM PHYSICAL_COPY INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
WHERE (PHYSICAL_COPY."IS_DIRTY" = 1) AND (COPY."SOURCE_ID" =
'5d74876f-1b14-466a-aeee-f000b8b16512') AND
(PHYSICAL_COPY."COMMIT_NUMBER" >= 1000)
PLAN JOIN (PHYSICAL_COPY ORDER "IDX_214/CmDhH936xtHXcXNQKg=="
INDEX ("IDX_214/CmDhH936xtHXcXNQKg==", "IDX_4I5n6Ay0py37rBgakWZS3Q=="),
COPY INDEX ("PK_ZM6SRonqR8AHSQuCISgvnQ=="))
ORDER BY PHYSICAL_COPY."COMMIT_NUMBER" ASC
This DOES NOT WORK!!
SELECT FIRST (1000) *
FROM PHYSICAL_COPY INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
WHERE (PHYSICAL_COPY."IS_DIRTY" = 1) AND (COPY."SOURCE_ID" =
'5d74876f-1b14-466a-aeee-f000b8b16512') AND
(PHYSICAL_COPY."COMMIT_NUMBER" >= 1000)
ORDER BY PHYSICAL_COPY."COMMIT_NUMBER" ASC
PLAN JOIN (PHYSICAL_COPY ORDER "IDX_214/CmDhH936xtHXcXNQKg=="
INDEX ("IDX_214/CmDhH936xtHXcXNQKg==", "IDX_4I5n6Ay0py37rBgakWZS3Q=="),
COPY INDEX ("PK_ZM6SRonqR8AHSQuCISgvnQ=="))
Message: isc_dsql_prepare failed
SQL Message : -104
Invalid token
Engine Code: 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 6, column 1
PLAN
Re: [firebird-support] Slow query because an index is not picked up during sort
Hello Michael and Dmitry, Michael, the index on PHYSICAL_COPY."COMMIT_NUMBER" is ascending, so in theory it should be used. Dmitry, I simplified the query by removing one join and selecting just one field. The query is still very slow but the temporary sort file was only 130MB. This is good, but may not be easy to implement with our ORM layer. So, I changed INNER with LEFT JOIN and got the query to run in 140ms. But looking at the query plan it seems like it's doing sorting before joining. Can you tell me if the following query does FIRST(1000) on PHYSICAL_COPY table before joining it with COPY or FIRST(1000) is done after the join? SELECT FIRST (1000) COPY."ID" FROM PHYSICAL_COPY LEFT JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID" WHERE (PHYSICAL_COPY."IS_DIRTY" = 1) AND (COPY."SOURCE_ID" = '5d74876f-1b14-466a-aeee-f000b8b16512') AND (PHYSICAL_COPY."COMMIT_NUMBER" >= 1000) ORDER BY PHYSICAL_COPY."COMMIT_NUMBER" ASC Prepare time: 0.015s Field #01: COPY.ID Alias:ID Type:STRING(38) PLAN JOIN (PHYSICAL_COPY ORDER IDX_214/CmDhH936xtHXcXNQKg== INDEX (IDX_214/CmDhH936xtHXcXNQKg==, IDX_4I5n6Ay0py37rBgakWZS3Q==), COPY INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==)) Executing... Done. 1815 fetches, 0 marks, 933 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 247 index, 0 seq. Delta memory: 1403300 bytes. Total execution time: 0.140s Thanks, Alec On Sat, Jun 2, 2012 at 3:11 AM, Dmitry Kuzmenko wrote: > ** > > > Hello, Alec! > > Saturday, June 2, 2012, 2:31:36 AM, you wrote: > > AS> We tracked down the query that generated a 10GB temp file running > AS> against a 1.5GB database. Can anybody explain why the query is not > AS> using an index on PHYSICAL_COPY."COMMIT_NUMBER"? > > the sort file is big because you are trying to get lot of > fields. Are they all needed? For example, if you have > varchar(1000), and you store only <=100 characters in it, > on disk it will be not more than 100 characters, but sort > will expand it to 1000, because sort can sort only fixed-sized > records. > > So, for queries with plan sort the less number of fields is selected, then > better. > > -- > Dmitry Kuzmenko, www.ib-aid.com > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] Slow query because an index is not picked up during sort
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", PHYSICAL_COPY."COPY_ID", PHYSICAL_COPY."REPOSITORY_TYPE", PHYSICAL_COPY."CANONICAL_LOCATION", PHYSICAL_COPY."CANONICAL_NAME", PHYSICAL_COPY."DISPLAY_LOCATION", PHYSICAL_COPY."DISPLAY_NAME", PHYSICAL_COPY."LOCATION_TYPE", PHYSICAL_COPY."IS_RECYCLED", PHYSICAL_COPY."SIZE", PHYSICAL_COPY."LAST_MODIFIED_TIMESTAMP", PHYSICAL_COPY."CREATED_TIMESTAMP", PHYSICAL_COPY."EXTENSION", PHYSICAL_COPY."IS_MARKED_DELETED", PHYSICAL_COPY."IS_DIRTY", PHYSICAL_COPY."CANONICAL_LOCATION_HASH", PHYSICAL_COPY."CANONICAL_LOCATION_CHECKSUM", COPY."ID", COPY."COMMIT_NUMBER", COPY."VERSION_ID", COPY."EXISTENCE_TIMESTAMP", COPY."REGISTRATION_TIMESTAMP", COPY."PREDECESSOR_ID", COPY."PREDECESSOR_CONFIDENCE_LEVEL", COPY."SOURCE_ID", COPY."THREAD_ID", COPY."ROOT_ID", COPY."IS_DIRTY", COPY_CLASSIFICATION."ID", COPY_CLASSIFICATION."COMMIT_NUMBER", COPY_CLASSIFICATION."COPY_ID", COPY_CLASSIFICATION."CLASSIFICATION_CODE", COPY_CLASSIFICATION."CONFIRMATION_STATUS", COPY_CLASSIFICATION."LABEL_CLASSIFICATION_CODE", COPY_CLASSIFICATION."LABEL_CONFIRMATION_STATUS", COPY_CLASSIFICATION."CUTOFF_TIMESTAMP", COPY_CLASSIFICATION."IS_CUTOFF_TIME_CONFIRMED", COPY_CLASSIFICATION."IS_DIRTY" FROM PHYSICAL_COPY INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID" INNER JOIN COPY_CLASSIFICATION ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID" WHERE (PHYSICAL_COPY."IS_DIRTY" = 1) AND (COPY."SOURCE_ID" = '123431234') AND (PHYSICAL_COPY."COMMIT_NUMBER" >= 1000) ORDER BY PHYSICAL_COPY."COMMIT_NUMBER" ASC Prepare time: 3.969s PLAN SORT (JOIN (COPY INDEX (IDX_Lhi+/ZlWWZ/FDOab6YV2Vg==), PHYSICAL_COPY INDEX (IDX_AlJS5EmMT9tODQnFqmid0w==), COPY_CLASSIFICATION INDEX (IDX_soqMJd+Yux0RNvCbmE9rrg==))) Executing... Done. 6273556 fetches, 12 marks, 808464 reads, 10 writes. 0 inserts, 0 updates, 0 deletes, 1563789 index, 4789 seq. Delta memory: 607388 bytes. Total execution time: 0:02:04 (hh:mm:ss) Script execution finished.
Re: [firebird-support] Re: Embedded Firebird leaves a lot of temp files behind
We are working on tracking those queries down, but fixing them will require an upgrade which the customer does not want to do. Moreover, their db size is 12GB, so it may not be possible to optimize the queries a lot further. The customer also ran out of disk space trying to rebuild database indexes which left firebird in a corrupted state. We had to add more space and run gfix to repair it. This is a different problem, but it's also related to FB disk usage. Is TempDirectories setting expected to work in embedded mode? What happens when max allowed temp space is not big enough for a sort? Thanks, Alec On Fri, Jun 1, 2012 at 12:16 PM, Michael Ludwig wrote: > ** > > > Alec Swan schrieb am 01.06.2012 um 11:55 (-0600): > > > Our customers complain that Firebird creates 6GB temporary sort file > > and we need to address this asap. Can anyone suggest a solution? > > That does sound unhealthy. Have you figured out what query or queries > are causing these exorbitant temporary sort files? There might be ways > of tailoring them such that the sort needed to produce the result is > reduced to a more manageable size. > > Michael > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Re: Embedded Firebird leaves a lot of temp files behind
Thanks, Michael. The issue you pointed out is similar to mine and the resolution was to set FIREBIRD_TMP environment variable. But this approach does not allow me to restrict the size of the temp directory. Our customers complain that Firebird creates 6GB temporary sort file and we need to address this asap. Can anyone suggest a solution? Thanks, Alec On Fri, Jun 1, 2012 at 11:28 AM, Michael Ludwig 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. But TempDirectories in > > firebird.conf seems to have no effect with or without FIREBIRD_TMP env > > var set. Is TempDirectories setting broken in FB 2.5? Does it have no > > effect on embedded FB version? > > Doesn't sound exactly relevant for the issue you're facing, but maybe > you can make sense of the discussion and so gain some insights: > > TempDirectories parameter in firebird.conf ignored by global temporary > tables - http://tracker.firebirdsql.org/browse/CORE-1241 - 03/May/07 > > Michael > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] Re: Embedded Firebird leaves a lot of temp files behind
I verified that FIREBIRD_TMP environment variable can be used to control where temporary files are written. But TempDirectories in firebird.conf seems to have no effect with or without FIREBIRD_TMP env var set. Is TempDirectories setting broken in FB 2.5? Does it have no effect on embedded FB 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 wrote: > 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 wrote: >> 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 wrote: >>> 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
[firebird-support] Re: Embedded Firebird leaves a lot of temp files behind
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 wrote: > 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 wrote: >> 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
[firebird-support] Re: Embedded Firebird leaves a lot of temp files behind
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 wrote: > 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
[firebird-support] Embedded Firebird leaves a lot of temp files behind
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
Re: [firebird-support] Force query plan to filter before join
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 NATURAL, PROJECT_CODE_DESCRIPTOR INDEX (FK_zDTEgB/EMb14zlRjEdzCZw==)), COPY_CLASSIFICATION INDEX (IDX_epDQN2rI7u0hoaXiWuFXAw==)), JOIN (COPY INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==), PHYSICAL_COPY INDEX (IDX_AlJS5EmMT9tODQnFqmid0w== 459 fetches, 0 marks, 6 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 108 index, 12 seq. Delta memory: -104 bytes. Total execution time: 0.025s I think I will stick with this solution, but I appreciate Set educating me on how to fool the query optimizer :) Arno, how did you know that using LEFT JOIN will cause the query optimizer to choose the plan we wanted? Thanks, Alec On Tue, Feb 21, 2012 at 2:15 AM, Arno Brinkman wrote: > ** > > > What about : > > > SELECT > PROJECT."PRIMARY_PROJECT_CODE" AS COL0, > COUNT(PHYSICAL_COPY."ID") AS COL1 > FROM > PROJECT > LEFT JOIN PROJECT_CODE_DESCRIPTOR ON PROJECT_CODE_DESCRIPTOR."PROJECT_ID" > = PROJECT."ID" > JOIN COPY_CLASSIFICATION ON COPY_CLASSIFICATION."CLASSIFICATION_CODE" = > PROJECT_CODE_DESCRIPTOR."PROJECT_CODE" > JOIN COPY ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID" > JOIN PHYSICAL_COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID" > WHERE > ( (PROJECT_CODE_DESCRIPTOR."PROJECT_ID" IS NOT NULL) and > > (PROJECT."ASSIGNMENT_STATUS" != 'UNASSIGNED') and > (PHYSICAL_COPY."IS_MARKED_DELETED" = 0) and > > (PHYSICAL_COPY."IS_RECYCLED" = 0) ) > GROUP BY > PROJECT."PRIMARY_PROJECT_CODE" > > Regards, > Arno > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Force query plan to filter before join
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 (PK_ZM6SRonqR8AHSQuCISgvnQ==), PC INDEX (IDX_AlJS5EmMT9tODQnFqmid0w== 527 fetches, 0 marks, 60 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 138 index, 12 seq. Delta memory: 61020 bytes. Total execution time: 0.067s Here is the query result: ACME 6 BANANA 1 CODE A 1 PROJECT A 1 PROJECT B 1 Notice that the sum of counts is 10 - this is what I meant by 'filters out all but 10 joined rows'. If UNASSIGNED was included in the list it would have had a count of almost 14,000 associated with it! So, what I wanted to avoid is for the query optimizer to join all tables first and only then apply the != 'UNASSIGNED' filter. Instead, I wanted the optimizer to apply the filter to the PROJECT table first, then join (and find 10 matching rows) and only after that do the aggregation. And that's what your query is doing I believe. But I understand that the optimizer is not doing what I want it to do because it does not know that 99% of rows are associated with project with 'UNASSIGNED' status and will be filtered after the join. I like using table expressions, but it will be hard to refactor our ORM tool to use them, so I am wondering if there is a way to force the plan of the original query to match your query? Thanks, Alec 2012/2/19 Svein Erling Tysvær > ** > > > >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 > >PROJECT."ASSIGNMENT_STATUS" != 'UNASSIGNED' filter to be > >applied before the join? > > Hi Alec! > > I don't quite understand what you mean by 'filters out all but 10 joined > rows', != (not equal) would normally not be able to use any index and > trying to force that to be applied before any JOIN would of course find all > that are different from 'UNASSIGNED' (note that NULL is neither equal to or > different from 'UNASSIGNED') regardless of their connection to any other > table, and making an index for a field where all but 10 rows have the same > value would only be useful in very special circumstances. You can of course > try something like > > WITH MyTemp AS > (SELECT DISTINCT p.ID, p.PRIMARY_PROJECT_CODE > FROM PROJECT p > WHERE p.ASSIGNMENT_STATUS != 'UNASSIGNED') > > SELECT tmp.PRIMARY_PROJECT_CODE AS COL0, COUNT(pc.ID) AS COL1 > FROM PROJECT_CODE_DESCRIPTOR pcd > JOIN MyTemp tmp ON pcd.PROJECT_ID = tmp.ID > JOIN COPY_CLASSIFICATION cc ON pcd.PROJECT_CODE = cc.CLASSIFICATION_CODE > JOIN COPY c ON cc.COPY_ID = c.ID > JOIN PHYSICAL_COPY pc ON c.ID = pc.COPY_ID > WHERE pc.IS_MARKED_DELETED = 0 > AND pc.IS_RECYCLED = 0 > GROUP BY tmp.PRIMARY_PROJECT_CODE > > but my guess is that this might be slower than your original query (and > I'm not certain Firebird would choose the PLAN I'm hoping for, we might > have to change things), Note that I'm not saying it is impossible to speed > up your query, we just don't know enough about your tables and how > selective your indexes are etc. so any suggestion will be more or less > guesswork. > > Set > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] Force query plan to filter before join
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 PROJECT."ASSIGNMENT_STATUS" != 'UNASSIGNED' filter to be applied before the join? Thanks! Preparing query: SELECT PROJECT."PRIMARY_PROJECT_CODE" AS COL0, COUNT(PHYSICAL_COPY."ID") AS COL1 FROM PROJECT_CODE_DESCRIPTOR INNER JOIN PROJECT ON PROJECT_CODE_DESCRIPTOR."PROJECT_ID" = PROJECT."ID" INNER JOIN COPY_CLASSIFICATION ON PROJECT_CODE_DESCRIPTOR."PROJECT_CODE" = COPY_CLASSIFICATION."CLASSIFICATION_CODE" INNER JOIN COPY ON COPY_CLASSIFICATION."COPY_ID" = COPY."ID" INNER JOIN PHYSICAL_COPY ON COPY."ID" = PHYSICAL_COPY."COPY_ID" WHERE ( (PROJECT."ASSIGNMENT_STATUS" != 'UNASSIGNED') and (PHYSICAL_COPY."IS_MARKED_DELETED" = 0) AND (PHYSICAL_COPY."IS_RECYCLED" = 0) ) GROUP BY PROJECT."PRIMARY_PROJECT_CODE" Prepare time: 0.004s Field #01: . Alias:COL0 Type:STRING(256) Field #02: . Alias:COL1 Type:INTEGER PLAN SORT (JOIN (PHYSICAL_COPY INDEX (IDX_sKfIZDMkd26ITRo1Da5mzQ==), COPY_CLASSIFICATION INDEX (IDX_soqMJd+Yux0RNvCbmE9rrg==), COPY INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==), PROJECT_CODE_DESCRIPTOR INDEX (IDX_lEwvSCR+VZpQCfw5Duxo0A==), PROJECT INDEX (PK_f3m9slJ+02gL6hFClhrZvg==))) Executing... Done. 278233 fetches, 0 marks, 16305 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 69581 index, 0 seq. Delta memory: -3956 bytes. Total execution time: 1.243s Script execution finished.
