RE: No primary keys

Unless, of course, you are trying to produce two rows from where there was only one. Not a normal occurrence. Good points Art. I had not thought of this from that angle. I've already posted my thoughts on the absence of indices, I'll leave that there. What can I say, I'm a fantastic programmer. (I WILL talk about keeping data unique through force of will). We had a process which attempted to use a unique index to prevent duplicates from entering a 60GB table. The insert process for 10 million rows every other night took 9 hours. if the number of rows to process exceeded 18 million or so we would run out of log space, get a long transaction, the engine would go down, and it would take 24 hours of rebooting and letting the logs play until they ran into a checkpoint and hung (lil' feature there of 8.30) before we got the engine back. I changed this process to work against a raw table, do a hash join to determine IF there were duplicates in the incoming data and if so move them off so that the incoming data was gauranteed to always be fresh. This process now runs on a nightly basis in 16-20 minutes. Since that time we have not lost the engine once to long transactions (we don't even need those huge logs anymore). Oh, by the way, if we get 100 million rows to insert for some strange reason? The process slows down to 21 minutes. We have a lot more time for playing frisbee in the hall. cheers j. > -----Original Message----- > From: Art S. Kagel [mailto:kagel@bloomberg.net] > Sent: Wednesday, January 10, 2001 5:50 PM > To: informix-list@iiug.org > Subject: Re: No primary keys > > > Whether or not your tables use PRIMARY KEY constraints, > UNIQUE constraints, > UNIQUE indexes or none at all and you just (YUCK!) maintain > uniqueness > through force of will and fantastic programming, EVERY ROW IN > EVERY TABLE > MUST HAVE A COMBINATION OF COLUMNS WHICH CAN BE USED TO > UNIQUELY IDENTIFY > A SINGLE ROW! I guarantee your DW Fact tables and dimension > tables do > indeed have a unique key even if they have no constraints or > other unique > indexes on them. Otherwise they are not very useful, how would the > dimension table rows reference the fact row they identify > without them? > > Art S. Kagel > > cedarsiding@my-deja.com wrote: > > > > > First of all, the terms I think you meant to use are "referenced" > > > and "referencing" tables. There is no such thing as a child and > > > parent table in SQL; those terms are from the days of > pointer chains > > > in hierarchical and network databases. > > > > > > Secondly, wrong! ALL tables, to be tables, have to have > a key. That > > > is a basic definition. This is very valid generic advice. > > > > Joe, > > > > Thank you for that bit of SQL indoctrination. > > > > Now I'd just like to point out that I was not talking > > about SQL nor anything specifically defined within > > that religion. I was responding to questions specifically > > about Informix databases, in which, let me assure you, > > one can define a "table" that has no "key." > > > > Please excuse me for confusing anybody that was thrown > > completely off track by my heretical use of the terms > > "child" and "parent" to express the roles in a > > referential relationship. > > > > Finally, in defense of my statement that not all "tables" > > in an Informix database might want to have a primary key > > (constraint explicitly) defined on them, consider the case > > of a fact table in a data warehouse. Typically, this > > sucker is loaded up with foreign key constraints associated > > with the surrounding dimension tables, but need not have > > a primary key explicitly created for it within the RDBMS > > (whether or not it has one conceptually is a different > > matter). > > > > I've even heard rumors that some folks don't use ANY indexes > > in their datamart/data warehouse, which I guess means they > > didn't explicitly define even the primary key indexes on > > the dimension tables. Go figure. > > > > -cs > > > > Sent via Deja.com > > http://www.deja.com/ >