From: fbortel@home.nl   
      
   Eric wrote:   
      
   > I'm trying to insert data for a real-time application that demands   
   > less than 3 second receipt-to-commit time, has a relatively large   
   > (500,000 records/hr) data rate, and requires access times on the order   
   > of 10-15 seconds.  We're storing about 24 hours worth of data on a   
   > rolling basis, so I've partitioned the table by hour.  Three of the   
   > columns are (separately) indexed.   
   >   
   > I know I can do this by piping data through sqlldr into the live table   
   > as long as partitions are small enough to keep the index modifications   
   > from becoming too taxing.  However, I'd like to keep my hardware   
   > requirements to a minimum, since I have about 100 of these streams in   
   > all, and would like to avoid spending $10M on hardware to brute-force   
   > this.  So I cooked up a scheme I thought would save on hardware.   
   >   
   > I know if I can direct load data into an offline staging table it's   
   > considerably more efficient, but in that case I won't be able to   
   > satisfy my 3 second receipt-to-commit requirement unless my partitions   
   > are impractically small.   
   >   
   > I plan to have a partitioned, unindexed table to receive data the   
   > "loading" table).  Once I get enough data for a full hour, I'll   
   > exchange the partition with an unpartitioned table's data segment (the   
   > "staging" table).  Then I'll build the indexes I need for the   
   > destination ("live") table, and exchange the newly indexed table into   
   > a partition in the "live" table.   
   >   
   > The idea here is I can do indexed searches on 23 hours worth of data,   
   > while being able to direct-load my source data without recalculating   
   > indexes.  The reason this will work for me is actual _access_ to this   
   > data is relatively uncommon, so I think I would rather deal with a   
   > full table scan on the "loading" table every once in awhile than take   
   > the performance hit from updating indexes every second.   
   >   
   > I plan to create a view to access all three tables at once.  I have a   
   > couple of questions, though:   
   >   
   > 1)  Can you access a table while you're building indexes?  It seems   
   > like I should be able to do non-indexed searches of tables while   
   > they're being indexed.  Is that true?   
   >   
   > 2)  If I'm selecting from a table, does the select block the partition   
   > exchange?  If it doesn't, is the result set determinate?   
   >   
   >   
   > 3)  What I really want to know, with all this table indexing and data   
   > segemnt swapping, is what are the chances some of my data is going to   
   > fall through the cracks?   
      
   Apart from the math (500k inserts/hr == 138.8/sec, not 0.3/sec),   
   did you ever read the licence agreement?   
   Real time applications (Nuclear Plants are mentioned!) are not   
   supported...   
      
   Bottom line is, that -unless you're talking near real time- you   
   can never have the data real time with (any?) RDBMS; you'll have   
   to code your own, with known execution paths, leading to known   
   delays.   
   --   
   Regards, Frank van Bortel   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)