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?
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?