How To Query Large Data/Records ?

[affiza]

Hi All,

Here is my Problem - In my search form I have to query 50 million records in a
single transaction and to display the results . - Without sweating the
machine.

my Approach(s):
1) Indexing - this is bad, because the batch processing is using the same
table to load data at the same time - daily.
2) Data base partition - well, the search results will be still ard 800 , 000
records/per partition.
3) Search keys - to filter results -for 50 mill ?
This is a common approach, but to search over 50 mill
or with 800,000 records/per partition .Hmmm


The Appl/ Enviroment:
1) web server - CF MX ,win2k, RAM 133MB, Processor - x86 Family 6 Model 8
Stepping 3
2)Database - Oracle 9i /IBM AIX 5.2
3)With above system requirement - The server/pc
cannot even hold for some 60,000 records per transaction. Unfortunately for me
is I ll still be using the same server/pc(above). Plus , to add another
partition/sub part or indexing scheme in the database will effect the back end
processing some how.

System Requirement
------------------
1) To display a precise total records for every search transaction - the
results.
2) To have a fast/ reliable / robust application.

Any ideas , new algo or technique ? Please help

affiza
:confused;

[The ScareCrow]

I assume that you are using a store proc ?

How fast does it run in the db environment ?

As I don't use oracle, I'm not sure this will work, but

In sql server I had a similar problem, I created a stored procedure. This sp
also took a param that indicated the starting point for the next recordset to
return. That is I only return 1000 records at a time.

But this is all dependant upon the execution time of the sp.
When I get home I will post some example code.

Ken