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

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1122836433.950130_at_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. Received on Sun Jul 31 2005 - 14:24:14 CDT