Re: [Firebird-devel] SEC$USERS and indices
On 09/19/17 20:45, Magnus Johansson wrote: -Original Message- From: Alex Peshkoff via Firebird-devel [mailto:firebird- de...@lists.sourceforge.net] Sent: Tuesday, September 19, 2017 10:19 AM I'm curious about why there is no index on SEC$USERS.SEC$USER_NAME (*) You are right - because it's virtual table. Thanks for confirming, as expected although not what I had hoped for. Having database with a couple of thousand users it doesn't feels optimized doing a natural scan every time I want to look up a user. The only excuse for us is that before SEC$USERS table arrived one could have no access from SQL to actual user's list at all (RDB$USERS table even if present in regular database has nothing to do with actual users list). I suppose optimizer can in theory use hash joins here but not sure does it do so in current code. -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] SEC$USERS and indices
> -Original Message- > From: Alex Peshkoff via Firebird-devel [mailto:firebird- > de...@lists.sourceforge.net] > Sent: Tuesday, September 19, 2017 10:19 AM > > > I'm curious about why there is no index on SEC$USERS.SEC$USER_NAME (*) > > You are right - because it's virtual table. Thanks for confirming, as expected although not what I had hoped for. Having database with a couple of thousand users it doesn't feels optimized doing a natural scan every time I want to look up a user. > > and about the reason as to why there is a difference between the usage of > VARCHAR and CHAR on tables that seems related. > > Use of CHAR for system tables is typical solution. What about related fields > in > tables used by plugins - they are logically related, but separated by plugin > code > and plugin is free to choose any format to store that data. One can write > plugin > that stores user's list in non-firebird database or even in text file. Understood and makes sense in that perspective. > > And if there would have been an index, would a UNIQUE INDEX on SEC$USERS > (SEC$USER_NAME, SEC$PLUGIN) been correct? > > If virtual table _could_ have indices - yes, that pair is unique. I see that I didn't express myself clearly, what I meant was that SEC$USER_NAME alone cannot be guaranteed unique if mixing different plugins, but I take it that's how you read it and answered accordingly. :-) Thanks for your time Alex. Regards, Magnus Johansson -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] SEC$USERS and indices
On 09/18/17 23:39, Magnus Johansson wrote: Hello, I'm looking on FB 3.0 and doing some thinking comparing with FB 2.5. In 2.5 there is an index on RDB$USERS.RDB$USER_NAME and that field is defined as VARCHAR. In 3.0 there is no index on SEC$USERS.SEC$USER_NAME and it is defined as CHAR. Looking further in 3.0, PLG$SRP.PLG$USER_NAME and PLG$USERS.PLG$USER_NAME both has indexes and are defined as VARCHAR. RDB$AUTH_MAPPING.RDB$MAP_NAME also has an index but is defined as CHAR. I'm curious about why there is no index on SEC$USERS.SEC$USER_NAME (*) You are right - because it's virtual table. and about the reason as to why there is a difference between the usage of VARCHAR and CHAR on tables that seems related. Use of CHAR for system tables is typical solution. What about related fields in tables used by plugins - they are logically related, but separated by plugin code and plugin is free to choose any format to store that data. One can write plugin that stores user's list in non-firebird database or even in text file. And if there would have been an index, would a UNIQUE INDEX on SEC$USERS (SEC$USER_NAME, SEC$PLUGIN) been correct? If virtual table _could_ have indices - yes, that pair is unique. If these kinds of question don't belong here then I apologize, else it would be most appreciated if anyone with some insight and time over care to enlighten me. (*) I first thought that it had to do with SEC$USERS being a virtual table materialized on execution for that transaction. Then I read a little in UserManagement.h about getting users list for SEC$USERS from security database for deferred work, and although I realize that "security database" here can be any database I got uncertain. So being a Delphi guy only I probably miss the point but I have to ask probably stupid question with obvious answers. Regards, Magnus Johansson -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
