Re: [firebird-support] Why PLAN token is not allowed after ORDER BY? - Email found in subject
Den 2012-06-03 21:23 skrev Alec Swan såhär: On Sun, Jun 3, 2012 at 1:06 PM, Leyne, Seans...@broadviewsoftware.comwrote: 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, 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. Just as a note to ahyone cosidering prettyfying index names or other auto generated identifiers, and make them short enough for Firebird: One approach that may or may not be suitable, but at least would be rather easy to implement is the way ECO from www.capableobjects.com does it. They truncate at maxidentlength - 3 and calculate some kind of hash on the remaining characters (or the complete identifier?) and appends a three character representation of that hash, e.g. (real example): ASP_RoleRolesASP_RoleUMSK This is for an association from the class ASP_Role to ASP_RoleUser. the UML role name for the link from ASP_Role to ASP_RoleName has, in the model, been named Roles and in the other direction it's named User, which would generate the link table name ASP_RoleRolesASP_RoleUserUser, which is not too long, but since we set the max length to 25 in our app, ECO will truncate at 22 chars (25 - 3) and add MSK which is the hash. In most cases, 22 chars is enough to understand what it is, but in some cases you end up with identifiers that differ only in the has, which is rather annoying. So, depending on your requirements and preferences, this scheme may or may not be useful to you. At least it's simple. Side note: We are forced to set max length shorter than FB's max in our app, because we use Swedish characters. These are encoded with two bytes in UTF8, the storage format FB uses for identifiers. Unfortunately, FB has max 31 BYTES for identifiers, as opposed to 31 CHARACTERS, meaning that if our identifier contains many Swedish characters, the max identifier length in characters is reduced (by one character per Swedish character). To be on the safe side we set the max length to 25. Kjell -- -- Kjell Rilbe DataDIA AB E-post: kj...@datadia.se Telefon: 08-761 06 55 Mobil: 0733-44 24 64
RE: [firebird-support] Slow query because an index is not picked up during sort
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
Re: [firebird-support] Why PLAN token is not allowed after ORDER BY?
On Sun, 3 Jun 2012 08:59:14 -0600, Alec Swan alecs...@gmail.com 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? If you want the definitive source look at /src/dsql/parse.y as that contains the grammar definition used by Firebird. However it is just barely human readable ;) Mark
RE: [firebird-support] Why PLAN token is not allowed after ORDER BY?
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
[firebird-support] FB2 + Vulcan - FB3 en route?
I stumbled upon a Vulcan on the attic … http://www.ibphoenix.com/resources/documents/attic - Vulcan Which made for some interesting reading, but then there's lots of documents and I started wondering about the relevance of it all … What is it? Turns out it's an FAQ I've never seen before: http://www.firebirdfaq.org/faq245/ - What is Vulcan? The intention is to merge Firebird 2 and Vulcan code back and create Firebird 3. Is this what's currently happening? Michael
Re: [firebird-support] FB2 + Vulcan - FB3 en route?
As far as i know, vulcan is another open source project derived from the open source interbase 6 version, so there is an intention to merge this two project into one firebird project and that will be called firebird 3 version. On Mon, Jun 4, 2012 at 8:33 PM, Michael Ludwig mil...@gmx.de wrote: ** I stumbled upon a Vulcan on the attic http://www.ibphoenix.com/resources/documents/attic - Vulcan Which made for some interesting reading, but then there's lots of documents and I started wondering about the relevance of it all What is it? Turns out it's an FAQ I've never seen before: http://www.firebirdfaq.org/faq245/ - What is Vulcan? The intention is to merge Firebird 2 and Vulcan code back and create Firebird 3. Is this what's currently happening? Michael -- Iwan Cahyadi Sugeng Interaktif Cipta Lestari [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] FB2 + Vulcan - FB3 en route?
As far as i know, vulcan is another open source project derived from the Is this what's currently happening? Look here http://www.firebirdsql.org/en/roadmap/ Regards, Alexey Kovyazin PS -Whose motorcycle is this? -It's a chopper, baby. -Whose chopper is this? -Zed's. -Who's Zed? -Zed's dead, baby, Zed's dead [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] FB2 + Vulcan - FB3 en route?
Michael Ludwig schrieb am 04.06.2012 um 15:33 (+0200): I stumbled upon a Vulcan on the attic … http://www.ibphoenix.com/resources/documents/attic - Vulcan http://www.firebirdfaq.org/faq245/ - What is Vulcan? The intention is to merge Firebird 2 and Vulcan code back and create Firebird 3. Is this what's currently happening? No, apparently it's not; it either has happened or won't happen. I found a thread on fb-dev that reveals at least part of the history, especially the message by Bill Oliver of SAS: All of the key features from Vulcan have been front-ported to Firebird 2.5 beta. [Firebird-devel] status of vulcan project - James Gregurich - 03.08.09 http://markmail.org/thread/fzjh2xy2fzbvluih The Vulcan is on the attic for a reason. And hence FAQ 245 should reflect that fact, if only by pointing to that thread on Markmail. Michael
Re: [firebird-support] Slow query because an index is not picked up during sort
On Sun, Jun 3, 2012 at 7:17 PM, Alec Swan alecs...@gmail.com 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 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: select c.city, s.stateName from cities c inner join states s on c.stateCode = s.stateCode where c.population 100 and s.population 500 The optimizer could either choose to look up cities larger than a million then look up the matching states, or it could choose to find states with population less than five million then look up cities. The choice would depend on what indexes are available for city and state populations and stateCodes and the selectivity of those indexes. This case is different. select c.city, s.stateName from cities c inner join states s on c.stateCode = s.stateCode where c.population 100 The only join order possible is to look up cities first and use the city to find matching states, if any. Starting with states would miss all the cities that are not in states. Good luck, Ann [Non-text portions of this message have been removed]
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] FB2 + Vulcan - FB3 en route?
Yes, thanks for the correction, i've read the vulcan project long ago, so i can't remember the exact news about it, all i know that fb 3 is planned to merge vulcan and firebird to have better support on SMP. On Tue, Jun 5, 2012 at 3:37 AM, Michael Ludwig mil...@gmx.de wrote: ** Iwan Cahyadi Sugeng schrieb am 04.06.2012 um 22:26 (+0700): As far as i know, vulcan is another open source project derived from the open source interbase 6 version [ ] This is wrong. Check FAQ 245 the URL of which I posted [see below]: Vulcan was a separate project (fork) programmed by Jim Starkey. It took early Firebird 2.0 alpha sources in order to bring better SMP (multi-CPU) support. Michael What is it? Turns out it's an FAQ I've never seen before: http://www.firebirdfaq.org/faq245/ - What is Vulcan? -- Iwan Cahyadi Sugeng Interaktif Cipta Lestari [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] firebird.conf
The CentOS package firebird-2.1.4.18393.0-1.el5 installs firebird.conf in /etc/firebird/firebird.conf and /var/lib/firebird/firebird.conf. Shouldn't one of these be a symlink to the other, and which location is used by firebird classic and also nbackup? Thanks in advance, Tom Munro Glass
