Neon Enterprise Software Blog

Traditional software development methods typically create software in stages - Planning, Analysis, Design, Code, Test, Ship.  This staged "waterfall" approach is known for being rigid and slow and as a result it is difficult to get things right.  It prides itself on documentation created at each stage and is a very structured way of building software.  The problem is that it doesn't work. 

Agile development on the other hand is all about prototyping, black boxing, and figuring it out as you go.  It is known for rapid application development and usually involves a team full of superstars that can each do it all.  The problem is that is doesn't always work.

In todays world of offshore development, cultural and language differences and skill shortages, there must be some happy medium.  It's hard to have a daily stand-up meeting (Agile/Scrum) with team members distributed across the globe in four different timezones.  It's tough to figure it out as you go when your team is asleep on the other side of the planet.  There must be some requirements that are documented and analyzed and designed in advance.  There are others that can be done in quick "sprints" so that results can be demonstrated as you go.  But you don't want it to take forever because requirements change and the likelihood of that increases the longer you take to get the job done. 

To the waterfall purists, I say "get real".   To the agile purists, I say "wake up".  Because at the end of the day, it's not about waterfall or scrum or any particular methodology. Instead, it's about deciding what works for you and your team and constantly adjusting and improving so you can delivery high quality software in record time. There are good things about all methodologies - it's up to you to figure out what's best for your particular project.  And for me it's a combination of  Agile Scrum and the Waterfall approach - I call in Scrumwater.  It involves designing and hashing out some things in advance.   It involves some documentation to help all team members, wherever they are.  It involves building the software in sprints.   It is 80% agile, 10% traditional and 10% "I don't know what".   

And on the next project, it'll probably be a little different. 

 

 

After deleting many rows from a table, we face the next challenge.  What can we do do optimize both space usage and performance of this table now that it has much fewer rows?  After a large DELETE operation, our table will look like this:

And while swiss cheese is yummy, it's bad for our tables and bad for our database.  Now we need to get to work.  

•  The first thing we want to do is reset the High Water Mark (HWM).  The HWM comes into play on full scans since Oracle will scan the blocks up to the HWM.  If you had 10 million rows in the table and delete them all, Oracle will still think there's 10 million rows to scan until the HWM is adjusted accordingly.  There are different ways that you can reset the HWM.  In version 10, you can simply use the SHRINK command although row movement must be enabled for the table.  In version 9, it can be a little bit trickier - moving the table will reset the HWM and you can even move it to the same tablespace.  However, the tablespace must a locally managed one (LMT).
  

• The next thing to do is to re-generate optimizer statistics so the optimizer can make more intelligent decisions.  This one is easy  - a simple call to DBMS_STATS will do the job.
  

After these two things are done, you're rid of the pesky rows following the SQL DELETE operation and you've put the table back into an optimal state. 

  

Craig Mullins does a great job of discussing all of the challenges with data archiving in his blog.  Most of the issues with data archiving are indeed database agnostic.  I'm here today to discuss some of those that are specific to Oracle. 

What is the best way to archive data in Oracle?  There are many ways to skin that cat, and like all good software developers would respond ... "it depends".  When I say "archive", I'm referring to the following things that must happen.  

1. Data is specified (SQL WHERE clause) that is targeted to be archived.
   
2. That data, and all related data (FK relationships) is extracted and put in some form in which it can be either reloaded into Oracle or queried through some other means.   It doesn't do us any good to extract rows from the ORDERS table and skip the rows from the ORDER_LINE_ITEMS table if we ever need to be able to re-create the exact data set.  
   
3. Once extracted, the data is then removed from the database.  I used the word "removed" and not "deleted" since we may not use a SQL DELETE to actually remove the rows.  
   

Expanding on #3 above, how would we actually remove the rows?  It depends.  ;-)

If our table is partitioned and the partition key is the same as our archive WHERE clause, then life is good.  We simply drop the partition and it doesn't get any faster than that!  Each partition would be in its own tablespace (with its own datafiles) and we could copy those to tape and restore them at any time.  Archiving becomes quite easy.

If our table is not partitioned, then it depends on the percentage of rows in the table that need archived.  If a large percentage of rows we could CREATE TABLE AS SELECT (the rows we want to keep), drop the old table and rename the new to the old.  No rollback needed, no massive delete operation, etc.

If our table is not partitioned and it's a small percentage of rows in the table, well ... now we're limited to a SQL DELETE. 

You can see how easy archiving is when our data is partitioned accordingly.  Those that take time and design table partitions with archiving requirements in mind are rewarded with easy administration.  

Tune in next time to find out what housekeeping tasks need performed on a table after a large delete operation is performed.