Re: No future for DB2 - slightly off-topic, discusses what people are being taught at uni

"DA Morgan" <damorgan@psoug.org> wrote in message news:1122533361.487428@yasure... > It is not that DB2 is technically incapable of competing. Rather IBM > is presiding over an aging baby-boom workforce. Speaking only from my > experience in the US ... a large number of colleges and universities, > including mine, have active programs teaching SQL Server and Oracle. > I can not think of a single one teaching DB2. Speaking from my experience in the UK I find an even wider trend: in the four years by consultancy has been going we've recruited several graduate trainees. In 2001 and 2002 you could be pretty sure that most applicants from university would have a pretty decent grounding in UNIX and/or Linux, these being the platforms favoured by academia. We'd also find that most would have done at least one hands-on course or practical assignment with SQL Server or Oracle. We didn't recruit in 2003 or 2004 and this year has been a real eye-opener. Perhaps less than one in five applicants has had *any* practical experience of a non-Windows OS - if they know about UNIX/Linux at all it's because they're done a theoeritical course lasting a most a couple of hours. Also, the level of database theory has dropped too: asked to name a commercial database system they name Access, which is the "database" that the majority of them have hands-on experience of. A few had also used SQL (as they call SQL Server, using the phrases interchangably). One or two were able to name a non-MS database product, which was Oracle. None had heard of DB2, Ingres, Informix etc. When we probed into the characteristics of a database, and why it might be more effective for retrieving small sets of data from much larger sets, most struggled, and although one or two had a grasp of the principle of indexes (or "keys"), I'm can only remember one correctly identifying, even at a high level, how an index might help in this regard. All this change in just 4 years or less. I've no reason to believe that the academic qualities of applicants is lower than in previous years: indeed we met some brilliant young people. But I infer from this exercise that UK academia at least has quickly gone from a bastion of UNIX to not teaching anything non-MS.

"Neil Truby" <neil.truby@ardenta.com> wrote in message news:3l3kneF10n87tU1@individual.net... > Speaking from my experience in the UK I find an even wider trend: in the > four years by consultancy has been going we've recruited several graduate > trainees. In 2001 and 2002 you could be pretty sure that most applicants > from university would have a pretty decent grounding in UNIX and/or Linux, > these being the platforms favoured by academia. We'd also find that most > would have done at least one hands-on course or practical assignment with > SQL Server or Oracle. > > We didn't recruit in 2003 or 2004 and this year has been a real eye-opener. > Perhaps less than one in five applicants has had *any* practical experience > of a non-Windows OS - if they know about UNIX/Linux at all it's because > they're done a theoeritical course lasting a most a couple of hours. Also, > the level of database theory has dropped too: asked to name a commercial > database system they name Access, which is the "database" that the majority > of them have hands-on experience of. A few had also used SQL (as they call > SQL Server, using the phrases interchangably). One or two were able to name > a non-MS database product, which was Oracle. None had heard of DB2, Ingres, > Informix etc. > > When we probed into the characteristics of a database, and why it might be > more effective for retrieving small sets of data from much larger sets, most > struggled, and although one or two had a grasp of the principle of indexes > (or "keys"), I'm can only remember one correctly identifying, even at a high > level, how an index might help in this regard. > > All this change in just 4 years or less. I've no reason to believe that the > academic qualities of applicants is lower than in previous years: indeed we > met some brilliant young people. But I infer from this exercise that UK > academia at least has quickly gone from a bastion of UNIX to not teaching > anything non-MS. The project I am working on has been developed by UK folks and we are customizing it for this Canadian customer. I too got a feeling that those guys are experts only in SQL Server. Their concept of a RDBMS seems to be weak. For e.g. in SQL Server one can create a Foreign Key without creating an index. In fact index creation on the FKY columns is a separate process. Same in Oracle and DB2. What the UK folks never realized is that almost all the time a FKY column is joined with PKY column for Query (otherwise why would it be a separate table). So creating index on the FKY column should be automatic when creating the FKY constraint. When I joined the project, one of the first fire I had to fight in the testing phase was locking. SQL server use to do table scan when it can't find matching index and that pretty soon escalated into locking and deadlock problem. ISOLATION LEVEL - well most of them don't even know what it is, let alone it is. I am beginning to wonder whether being a full time DBA is a dying profession left for old fogies like me.

"rkusenet" <rkusenet@hotmail.com> wrote in message news:3l3q1gF10mdvsU1@individual.net... > The project I am working on has been developed by UK folks and we are > customizing it for this Canadian customer. I too got a feeling that those > guys are experts only in SQL Server. > Their concept of a RDBMS seems to be weak. For e.g. in SQL Server > one can create a Foreign Key without creating an index. In fact index > creation on the FKY columns is a separate process. Same in Oracle > and DB2. What the UK folks never realized is that almost all the time > a FKY column is joined with PKY column for Query (otherwise why > would it be a separate table). So creating index on the FKY column > should be automatic when creating the FKY constraint. > When I joined the project, one of the first fire I had to fight in the > testing phase was locking. SQL server use to do table scan when it > can't find matching index and that pretty soon escalated into locking > and deadlock problem. > ISOLATION LEVEL - well most of them don't even know what it is, > let alone it is. > > I am beginning to wonder whether being a full time DBA is a > dying profession left for old fogies like me. > The subject of foreign keys and indexes has been discussed in the Oracle newsgroup recently. Not all foreign keys need (or should have indexes). Some foreign keys are merely connected to code tables that are used to make sure a valid value is used, and they are never joined. The example used in the Oracle thread is division_code on sales transaction table that relates to a division_code table with only 3 rows (divisions). Having an index on the foreign key for division_code would slow down inserts on the sales transaction table, and would never be used for queries (cardinality of 3 is too low for a RDBMS to use this index for queries), except for the extremely unlikely event of someone trying to change or delete a row in the division_code table. So creating an index on a foreign key should not be automatic.

"rkusenet" <rkusenet@hotmail.com> wrote: > So creating index on the FKY column > should be automatic when creating the FKY constraint. Not necessarily, at least AIUI. Say one has a table person with fields title, first_name, last_name, add1, 2, 3..... Now, you have title as a lookup table - it's a lookup into the title table. Now, the vast bulk of your inserts will be Mr. or Ms., so you will get a skewed index and performance can suffer if you have an index, rather than doing a simple table scan. I know that this has been an issue with Interbase in the past which automagically created (AIUI, one can now change this). I'm not sure of the performance implications for Oracle, but it is AFAICS, one good reason for not *_automatically_* creating an index. Paul... -- plinehan __at__ yahoo __dot__ __com__ XP Pro, SP 2, Oracle, 9.2.0.1.0 (Enterprise Ed.) Interbase 6.0.1.0; When asking database related questions, please give other posters some clues, like operating system, version of db being used and DDL. The exact text and/or number of error messages is useful (!= "it didn't work!"). Thanks. Furthermore, as a courtesy to those who spend time analysing and attempting to help, please do not top post.

"Mark A" <nobody@nowhere.com> wrote in message news:d9idnXLXjqxxhXDfRVn-qw@comcast.com... > The subject of foreign keys and indexes has been discussed in the Oracle > newsgroup recently. > > Not all foreign keys need (or should have indexes). Some foreign keys are > merely connected to code tables that are used to make sure a valid value is > used, and they are never joined. The example used in the Oracle thread is > division_code on sales transaction table that relates to a division_code > table with only 3 rows (divisions). > > Having an index on the foreign key for division_code would slow down inserts > on the sales transaction table, and would never be used for queries > (cardinality of 3 is too low for a RDBMS to use this index for queries), > except for the extremely unlikely event of someone trying to change or > delete a row in the division_code table. > > So creating an index on a foreign key should not be automatic. While I agree with you, I would add that for code lookups like Title (Mr, Mrs, Ms) I would rather go with a check constraint than a FKY. Anything with a low cardinality and known static values should be handled in a check constraint. At least I would.

"rkusenet" <rkusenet@yahoo.com> wrote in message news:3l4k0cF107mn3U1@individual.net... >> So creating an index on a foreign key should not be automatic. > > While I agree with you, I would add that for code lookups like Title (Mr, > Mrs, Ms) > I would rather go with a check constraint than a FKY. Anything with a low > cardinality > and known static values should be handled in a check constraint. At least > I > would. > The fact is that almost all modeling tools will show a foreign key relationship to the lookup table as part of 3rd normal form. When the DDL is generated by the tool, it will generate indexes for these relationships automatically (incorrectly IMO). The result is poor performance on inserts of the dependent table. The result is that many schemas have way too many indexes that will never be used, and of course invariably there are at least a few indexes missing.

rkusenet wrote: > I am beginning to wonder whether being a full time DBA is a > dying profession left for old fogies like me. I think from one standpoint it is. That standpoint being the traditional DBA job: It is on its last legs. The DBAs and SysAdmins of the last decade or two are the equivalent of the county sheriff during the days of the Wild West. A bunch of self-selected cowboys with a skill set viewed as valuable but untrained, uncertified, and being driven into obsolecense by changes in the law and changes in the demands placed upon them by the community at large. Just as a point-in-time came when it was not enough to be tough and be good with a gun ... we are rapidly approaching the time when it will no longer be enough to be as we have been. Just as a group of physicians formed the American Board of Medical Specialities (www.abms.org) and just as attorney's formed the American Bar Association: With equivalent organizations for other professions such as accountant, engineer, and pharmacist we too will need to define and certify members of our profession. All joking aside ... how many people do you know in IT today that can't, given a single first-normal form table, convert it to 2NF, 3NF, etc. More on this later and hopefully something about the American College of Database Professionals: An organization in its formative stages and modeled after other professional organizations such as www.facs.org. -- Daniel A. Morgan http://www.psoug.org damorgan@x.washington.edu (replace x with u to respond)

Mark A wrote: > "rkusenet" <rkusenet@hotmail.com> wrote in message > news:3l3q1gF10mdvsU1@individual.net... > >>The project I am working on has been developed by UK folks and we are >>customizing it for this Canadian customer. I too got a feeling that those >>guys are experts only in SQL Server. >>Their concept of a RDBMS seems to be weak. For e.g. in SQL Server >>one can create a Foreign Key without creating an index. In fact index >>creation on the FKY columns is a separate process. Same in Oracle >>and DB2. What the UK folks never realized is that almost all the time >>a FKY column is joined with PKY column for Query (otherwise why >>would it be a separate table). So creating index on the FKY column >>should be automatic when creating the FKY constraint. >>When I joined the project, one of the first fire I had to fight in the >>testing phase was locking. SQL server use to do table scan when it >>can't find matching index and that pretty soon escalated into locking >>and deadlock problem. >>ISOLATION LEVEL - well most of them don't even know what it is, >>let alone it is. >> >>I am beginning to wonder whether being a full time DBA is a >>dying profession left for old fogies like me. >> > > The subject of foreign keys and indexes has been discussed in the Oracle > newsgroup recently. > > Not all foreign keys need (or should have indexes). Some foreign keys are > merely connected to code tables that are used to make sure a valid value is > used, and they are never joined. The example used in the Oracle thread is > division_code on sales transaction table that relates to a division_code > table with only 3 rows (divisions). > > Having an index on the foreign key for division_code would slow down inserts > on the sales transaction table, and would never be used for queries > (cardinality of 3 is too low for a RDBMS to use this index for queries), > except for the extremely unlikely event of someone trying to change or > delete a row in the division_code table. > > So creating an index on a foreign key should not be automatic. A syntax such as the following would solve the problem: ALTER TABLE tab1ADD CONSTRAINT fk_tab1_col1 FOREIGN KEY (col1) REFERENCES tab2(col2) USING INDEX; Giving the power to decide whether to index, or not, to the database professional. It needn't be an all, or nothing, syntax. -- Daniel A. Morgan http://www.psoug.org damorgan@x.washington.edu (replace x with u to respond)

Mark A wrote: > The result is that many schemas have way too many indexes that will never be > used, and of course invariably there are at least a few indexes missing. While I have seen my fair share of under and over indexed tables I am a wondering why this concern about slowing up an insert. Rarely is the problem with an application's performance related to speed of inserts. Rather it is the speed to retrieval, SELECT, that is the issue and the focus on getting the data IN should not take precedence over getting it back out. One can only insert a record one time. Likely the record will be queried many many times thereafter. -- Daniel A. Morgan http://www.psoug.org damorgan@x.washington.edu (replace x with u to respond)

"DA Morgan" <damorgan@psoug.org> wrote in message news:1122836433.950130@yasure... > While I have seen my fair share of under and over indexed tables I am > a wondering why this concern about slowing up an insert. Rarely is the > problem with an application's performance related to speed of inserts. > Rather it is the speed to retrieval, SELECT, that is the issue and > the focus on getting the data IN should not take precedence over getting > it back out. > > One can only insert a record one time. Likely the record will be queried > many many times thereafter. > -- > Daniel A. Morgan When a row is inserted into a table (for example a sales_transaction table) then the database must add the table row and add a row to each index. Typically, it takes more time to add the index row than the data row in a b-tree index because it must be stored in exact order in the index, and if the index block is full, a block split occurs, and the non-leaf blocks need to be updated. With a low cardinality column like division_code (I assumed there were only 3 valid divisions), an single column index on division_code would not be used by a query (unless the entire table happened to be in physical sequence by division_code, or the sales_transaction table had an extremely large row length). Typically, there are many of these foreign key relationships to parent tables, so we are not talking about just one additional index. If we were talking about the department_code in the employee table, it would not be much of a problem because I don't know of any companies adding so many employees to their employee table to make a difference. But for a sales_transaction table, where rows are inserted at a high volume, it certainly could make a difference, especially with multiple unnecessary indexes.. But my philosophy is, regardless of the size of the table, that if an index will not be used (assuming that no one is going to do delete cascade or update on the parent division_code table), then whey have it? Unfortunately, most "DBA's" don't understand the nature of the application well enough, and they don't understand enough about how optimizers work, to make these decisions on a case by case basis. Many DBA's are looking for a single rule they can follow in every circumstance. IMO, these people are not real DBA's, and should consider becoming a UNIX/Linux Administrator.

"DA Morgan" <damorgan@psoug.org> wrote in message news:1122835935.944779@yasure... > Just as a group of physicians formed the American Board of Medical > Specialities (www.abms.org) and just as attorney's formed the American > Bar Association: ... More on this later and hopefully something about the > American College > of Database Professionals: ... orrganizations such as www.facs.org. Remind me what that "ww" stands for in www. again ...?

On Sun, 31 Jul 2005 13:24:14 -0600, Mark A interested us by writing: > But my philosophy is, regardless of the size of the table, that if an index > will not be used (assuming that no one is going to do delete cascade or > update on the parent division_code table), then whey have it? This is one statement with which I can unconditionally agree. It follows with my personal belief that the need of every index should be periodically re-evaluated, and - if it's existance can not (or can no longer) be justified, the index should be eliminated. Thankfully, Oracle provides the ability to monitor index usage. -- Hans Forbrich Canada-wide Oracle training and consulting mailto: Fuzzy.GreyBeard_at_gmail.com *** I no longer assist with top-posted newsgroup queries ***

On Sun, 31 Jul 2005 13:24:14 -0600, "Mark A" <nobody@nowhere.com> wrote: >Unfortunately, most "DBA's" don't understand the nature of the application >well enough, and they don't understand enough about how optimizers work, to >make these decisions on a case by case basis. Many DBA's are looking for a >single rule they can follow in every circumstance. IMO, these people are not >real DBA's, and should consider becoming a UNIX/Linux Administrator. If you are involved in remote maintenance, the customer bought the application, the application is just a black box. Yet, when there are performance problems invariably the DBA is blamed, while the vendor plays the usual cover your ass game. IMO, you are -as usual- generalizing way too much, and even worse, your judgement of DBAs must be considered as offending and insulting. But then of course you are only 'nobody@nowhere.com' -- Sybrand Bakker, Senior Oracle DBA

Captain Pedantic wrote: > "DA Morgan" <damorgan@psoug.org> wrote in message > news:1122835935.944779@yasure... > >>Just as a group of physicians formed the American Board of Medical >>Specialities (www.abms.org) and just as attorney's formed the American >>Bar Association: ... More on this later and hopefully something about the >>American College >>of Database Professionals: ... orrganizations such as www.facs.org. > > > Remind me what that "ww" stands for in www. again ...? "Wide web" ;-) I would encourage others, in their countries, to consider taking similar action. -- Daniel A. Morgan http://www.psoug.org damorgan@x.washington.edu (replace x with u to respond)

"Sybrand Bakker" <postbus@sybrandb.demon.nl> wrote in message > If you are involved in remote maintenance, the customer bought the > application, the application is just a black box. Yet, when there are > performance problems invariably the DBA is blamed, while the vendor > plays the usual cover your ass game. > > IMO, you are -as usual- generalizing way too much, and even worse, > your judgement of DBAs must be considered as offending and insulting. > But then of course you are only 'nobody@nowhere.com' > -- > Sybrand Bakker, Senior Oracle DBA If a DBA doesn't have authority to change the indexes, or even recommend changing indexes, then that is a situation I am not talking about.. I am talking about a situation where someone says (regardless of what they have authority to change) that all foreign keys should have indexes, no exceptions. I don't believe that is correct for Oracle or DB2 (or any other RDBMS that I know about). I have explained in detail (in this and other threads) the reasons why I think that is wrong. I have also explained that far too many DBA's are looking for simple rules that cover every situation, without having to think for themselves about how indexes and optimizers work, or without having to analyze each situation for optimum performance. If you are offended and insulted that I think DBA's should be able to have a basic understanding of the application they are working with, and should be able to think for themselves, that is your right, but I suppose you and I shall remain very far apart (both physically and conceptually).

Wyoming-Wide