Re: Views question?

ngte4-inf@yahoo.com wrote: > > In informix if I have a table and create a view on it. > Then I drop the table, does the view also get dropped ? > Oracle does not drop it - it makes is invalid and can be re-validated. > > Is there a setting in informix. No. It's never been considered worth the effort to implement I guess. I expect that the Oracle structure makes it a bit easier to leave abandoned views hanging around. This is is a bit of a nuisance, but dbschema -d mydbname -t myviewname > viewname.sql is a good way to capture your view's schema. By the way, why are you dropping a table and re-creating it? I have seen that Oracle DBA's seem to drop tables and reload them when you need to alter it's shape. Is this what you are doing? Informix is very flexible so you rarely need to drop a table just to add, remove, or change a column. You don't even have to drop the table if you want to "rebuild it" any more. I can't think of any valid reason to drop a table for routine DBA work in Informix. So.... what's your game plan?

> I have seen > that Oracle DBA's seem to drop tables and reload them when you need to alter > it's shape. Huh ? You don't need to do this in Oracle either - at least, for adding, dropping or renaming a column. You do (under the covers) drop and re-create tables for bigger online redefinition operations that cannot be done in place (such as moving a table from one disk to another, or changing a table structure from heap to index organized, for example)

Mark Townsend wrote: >> I have seen >> that Oracle DBA's seem to drop tables and reload them when you need >> to alter it's shape. > > Huh ? You don't need to do this in Oracle either - at least, for > adding, dropping or renaming a column. > > You do (under the covers) drop and re-create tables for bigger online > redefinition operations that cannot be done in place (such as moving a > table from one disk to another, or changing a table structure from > heap to index organized, for example) OK - I don't know why they do it then. Maybe it's hystorical? Maybe just something they settled on for a few obscure reasons? I'll ask and post their reasons... What about changing the type of a column? Adding a constraint that might be in violation? Was it ever necessary? I sort-of remember them mumbling something about this.

Andrew Hamm wrote: > > What about changing the type of a column? Depends on the scope of the change. If resizing, then not a problem (downsizing only works if no truncation is required. If truncation is required, then it an online redef op, and is not done in place). If changing actual data types (CHAR to DATE, for instance), then you can do it in-place only if the column contains nulls, otherwise you would require an online redefinition operation. > Adding a constraint that might be > in violation? Nope - violations would just go into the execptions table > Was it ever necessary? I sort-of remember them mumbling > something about this. > Prior to Oracle8i, DROP column was not supported. Informix did have that before us, so if you go back 5 years plus there was some grumbling about that. (I remember presenting a sneak peek on Oracle8i at a User Conference, spending an hour talking about all the cool Internet features (Java in the DB, virtual private database etc) to an interested but largely stunned DBA audience, only to mention offhand at the end that we had added Drop Column, at which stage the audience stood as one and gave me a standing ovation) BTW - as a trivia question - which of the big three databases STILL doesn't have drop column ?

Mark Townsend wrote: > > Depends on the scope of the change. If resizing, then not a problem > (downsizing only works if no truncation is required. If truncation is > required, then it an online redef op, and is not done in place). So what's an online redef op? Similar to informix's rebuild of a table, which basically locks it out for the duration while it is being physically rebuilt? If so, then I can guess that the guys I'm talkin' 'bout might do an unload ritual just to ward off fragging. > BTW - as a trivia question - which of the big three databases STILL > doesn't have drop column ? Would it cause a prolonged flame war if we started discussing? Oh wait, you haven't cross-posted. Shall I? :->>

Andrew Hamm wrote: > > So what's an online redef op? Similar to informix's rebuild of a table, > which basically locks it out for the duration while it is being physically > rebuilt? Nope - It's the next level up the value stack, and a managed operation in the database itself. Basically it starts with the source table, and then a definition of what the source table should look like after the redefinition. Once the DBA has defined the new physical table structure, they start the redefinition operation, and the data is taken from the source table, transformed, and loaded into a temporary table. While this happens, DMLs are still allowed on the source table - the changes are in turn journalled automatically. Once the source table is transformed, the jornalled DMLs are also applied (actually, this is done a number of times during the redef, based on a percentage of change threshold). This repeats for as long as necessary (and on relaly big, active tables, it can take hours or even days) until both tables are in sync. At this stage, both tables are locked, and the data dictionary is updated switching the names. Other things in the dictionary are also re-pointed (such as views, constraint definitions etc). Locks are released, and the initial table is then also dropped. The only outage is the time it takes to update the data dictionary, which is typically sub second. There is a quick lock on the source table at the start of the operation as well to ensure that the definition can be copied in the data dictionary cleanly. Note that the 'inplace' alter table drop/add column operations, like Informix, lock the table (although you can mark a column as dropped online, and then drop dropped columns later). However, you can use the online redef op above do either of these operations online. > If so, then I can guess that the guys I'm talkin' 'bout might do an unload > ritual just to ward off fragging. If you have Oracle DBAs unloading tables to defrag them then they need to be severly re-educated. It's pretty much impossible to cause fragmentation in anything other than an Oracle environment that's been thrown together haphazardly. There is a great deal of literature on how to do this, and it ain't rocket science. However, if they have inherited a badly set up environment, then they can use the online redef operation above to do this ritual, er, online. Personally, I'd also set up the new table defintions correctly, and then do this online, but only once. Then go home early. Reminds me of a very early experience - when memory on PCs went from 640k to 1024k plus. We rewrote a sort program to take advantage of the additional memory capabilities, and added it to a software/hardware upgrade package for a number of SMB apps we had out with some of our customers - charging handsomely for the upgrade, of course :-) This worked well, and everybody was happy, except for one customer, who accused us of doing nothing for his upgrade fee. When we discussed his concerns, it turned out he had a little comp sci himself, and he insisted that all sorts needed to spill to disk - as was taught in the comp sci manuals at the time. As the little red light on his floppy drive didn't come on, and his disk didn't whir, he was convinced we had done a smoke and mirrors on him. Even though we assured him his invoice sort etc was now significantly faster, he just wasn't going to be happy. So, under the auspices of fixing the bug he had obviously discovered, we added some routines that did nothing but flashed the light and whirred the disk. Result - One happy customer. Perhaps your Oracle DBA's are simply flashing their lights and whirring their disks as well ? >>BTW - as a trivia question - which of the big three databases STILL >>doesn't have drop column ? > > > Would it cause a prolonged flame war if we started discussing? Oh wait, you > haven't cross-posted. Shall I? :->> > Actually, I'm more worried about what your Oracle DBA's are going to think of me when you tell them the above. Am I likely to know any of them ? If so, whatever they tell is 100% correct, and I apologise ahead of time.

Andrew Hamm wrote: > Mark Townsend wrote: > >>>I have seen >>>that Oracle DBA's seem to drop tables and reload them when you need >>>to alter it's shape. >> >>Huh ? You don't need to do this in Oracle either - at least, for >>adding, dropping or renaming a column. >> >>You do (under the covers) drop and re-create tables for bigger online >>redefinition operations that cannot be done in place (such as moving a >>table from one disk to another, or changing a table structure from >>heap to index organized, for example) > > > OK - I don't know why they do it then. Maybe it's hystorical? Or maybe hysterical. For a good insight into the Oracle ALTER TABLE you can go to: http://www.psoug.org/reference/tables.html Of course there are other things you can do with Oracle tables too. Such as: http://www.psoug.org/reference/tab_flashback.html I wouldn't suggest you try this with any other RDBMS. -- Daniel Morgan http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp damorgan@x.washington.edu (replace 'x' with a 'u' to reply)

Mark Townsend wrote: > Andrew Hamm wrote: > >> >> So what's an online redef op? Similar to informix's rebuild of a table, >> which basically locks it out for the duration while it is being >> physically >> rebuilt? > > > Nope - It's the next level up the value stack, and a managed operation > in the database itself. Basically it starts with the source table, and > then a definition of what the source table should look like after the > redefinition. Once the DBA has defined the new physical table structure, > they start the redefinition operation, and the data is taken from the > source table, transformed, and loaded into a temporary table. While this > happens, DMLs are still allowed on the source table - the changes are in > turn journalled automatically. Once the source table is transformed, the > jornalled DMLs are also applied (actually, this is done a number of > times during the redef, based on a percentage of change threshold). This > repeats for as long as necessary (and on relaly big, active tables, it > can take hours or even days) until both tables are in sync. > > At this stage, both tables are locked, and the data dictionary is > updated switching the names. Other things in the dictionary are also > re-pointed (such as views, constraint definitions etc). Locks are > released, and the initial table is then also dropped. > > The only outage is the time it takes to update the data dictionary, > which is typically sub second. There is a quick lock on the source table > at the start of the operation as well to ensure that the definition can > be copied in the data dictionary cleanly. > > Note that the 'inplace' alter table drop/add column operations, like > Informix, lock the table (although you can mark a column as dropped > online, and then drop dropped columns later). However, you can use the > online redef op above do either of these operations online. > >> If so, then I can guess that the guys I'm talkin' 'bout might do an >> unload >> ritual just to ward off fragging. > > > If you have Oracle DBAs unloading tables to defrag them then they need > to be severly re-educated. It's pretty much impossible to cause > fragmentation in anything other than an Oracle environment that's been > thrown together haphazardly. There is a great deal of literature on how > to do this, and it ain't rocket science. However, if they have inherited > a badly set up environment, then they can use the online redef operation > above to do this ritual, er, online. Personally, I'd also set up the new > table defintions correctly, and then do this online, but only once. Then > go home early. > > Reminds me of a very early experience - when memory on PCs went from > 640k to 1024k plus. We rewrote a sort program to take advantage of the > additional memory capabilities, and added it to a software/hardware > upgrade package for a number of SMB apps we had out with some of our > customers - charging handsomely for the upgrade, of course :-) This > worked well, and everybody was happy, except for one customer, who > accused us of doing nothing for his upgrade fee. When we discussed his > concerns, it turned out he had a little comp sci himself, and he > insisted that all sorts needed to spill to disk - as was taught in the > comp sci manuals at the time. As the little red light on his floppy > drive didn't come on, and his disk didn't whir, he was convinced we had > done a smoke and mirrors on him. Even though we assured him his invoice > sort etc was now significantly faster, he just wasn't going to be happy. > > So, under the auspices of fixing the bug he had obviously discovered, we > added some routines that did nothing but flashed the light and whirred > the disk. Result - One happy customer. > > Perhaps your Oracle DBA's are simply flashing their lights and whirring > their disks as well ? > >>> BTW - as a trivia question - which of the big three databases STILL >>> doesn't have drop column ? >> >> >> >> Would it cause a prolonged flame war if we started discussing? Oh >> wait, you >> haven't cross-posted. Shall I? :->> >> > > Actually, I'm more worried about what your Oracle DBA's are going to > think of me when you tell them the above. Am I likely to know any of > them ? If so, whatever they tell is 100% correct, and I apologise ahead > of time. And if they wish to point their DBAs to a full demo of these capabilities they can go to: http://www.psoug.org/reference/dbms_redefinition.html -- Daniel Morgan http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp damorgan@x.washington.edu (replace 'x' with a 'u' to reply)

Mark Townsend wrote: >[DESCRIPTION OF online redef] OK - sounds good. Informix Feature Request!!! I know it would just be a bit of hack-work to add this to informix, given the log snooping that ER already performs. >[SNIP comments about unloading tables] I will definitely have to ask now. I cannot speak for them, but I know they've been handling Oracle engines for years 'n' years. > Perhaps your Oracle DBA's are simply flashing their lights and > whirring their disks as well ? I don't know, but they ended up imposing their unload ritual on all database updates, including the Informix sites. I'm dead against it, but it's not really my department, and managers rarely understand enough to agree and apply an order to cease. > Actually, I'm more worried about what your Oracle DBA's are going to > think of me when you tell them the above. Am I likely to know any of > them ? If so, whatever they tell is 100% correct, and I apologise > ahead of time. I don't know - they have been involved with Genasys and I think Peoplesoft from a North Sydney office. Does that spark any connections?