Re: Will this work?

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