There exists a Oracle 9i Database with 7 tables each with 15+ Million records. There's no relation between your table i.e. no foreign secrets.

Here's a good example of among the tables

CREATE TABLE GSS.SHOWPD
(
  INSERVID          VARCHAR2(7 CHAR)            NOT NULL,
  CAGEPOS           VARCHAR2(8 CHAR)            DEFAULT NULL,
  DETAILEDSTATE     VARCHAR2(100 CHAR)          DEFAULT NULL,
  FAILEDMB          NUMBER                      DEFAULT NULL,
  FREECHUNK         NUMBER                      DEFAULT NULL,
  FREEMB            NUMBER                      DEFAULT NULL,
  FWREV             VARCHAR2(100 CHAR)          DEFAULT NULL,
  FWSTATUS          VARCHAR2(100 CHAR)          DEFAULT NULL,
  AID               NUMBER                      DEFAULT NULL,
  LDA               VARCHAR2(100 CHAR)          DEFAULT NULL,
  MANUF             VARCHAR2(100 CHAR)          DEFAULT NULL,
  AMODEL            VARCHAR2(4000 CHAR)         DEFAULT NULL,
  NODEWWN           VARCHAR2(64 CHAR)           DEFAULT NULL,
  NRMUNUSEDFAIL     VARCHAR2(100 CHAR)          DEFAULT NULL,
  NRMUNUSEDFREE     VARCHAR2(100 CHAR)          DEFAULT NULL,
  NRMUNUSEDUNAVAIL  VARCHAR2(100 CHAR)          DEFAULT NULL,
  NRMUSEDFAIL       VARCHAR2(100 CHAR)          DEFAULT NULL,
  NRMUSEDOK         VARCHAR2(100 CHAR)          DEFAULT NULL,
  AORDER            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PATHA0            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PATHA1            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PATHB0            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PATHB1            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PORTA0            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PORTA1            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PORTB0            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PORTB1            VARCHAR2(100 CHAR)          DEFAULT NULL,
  RDCERR            VARCHAR2(100 CHAR)          DEFAULT NULL,
  REUERR            VARCHAR2(100 CHAR)          DEFAULT NULL,
  SERIAL            VARCHAR2(100 CHAR)          DEFAULT NULL,
  SIZEMB            NUMBER                      DEFAULT NULL,
  SPARECHUNK        VARCHAR2(100 CHAR)          DEFAULT NULL,
  SPAREMB           NUMBER                      DEFAULT NULL,
  SPEEDKRPM         VARCHAR2(100 CHAR)          DEFAULT NULL,
  SPRUNUSEDFAIL     VARCHAR2(100 CHAR)          DEFAULT NULL,
  SPRUNUSEDFREE     VARCHAR2(100 CHAR)          DEFAULT NULL,
  SPRUNUSEDUNINIT   VARCHAR2(100 CHAR)          DEFAULT NULL,
  SPRUSEDFAIL       VARCHAR2(100 CHAR)          DEFAULT NULL,
  SPRUNUSEDOK       VARCHAR2(100 CHAR)          DEFAULT NULL,
  STATE             VARCHAR2(100 CHAR)          DEFAULT NULL,
  TEMPDEGC          NUMBER                      DEFAULT NULL,
  TOTALCHUNK        VARCHAR2(100 CHAR)          DEFAULT NULL,
  ATYPE             VARCHAR2(100 CHAR)          DEFAULT NULL,
  UNAVAILABLEMB     NUMBER                      DEFAULT NULL,
  VOLUMEMB          NUMBER                      DEFAULT NULL,
  WRCERR            VARCHAR2(100 CHAR)          DEFAULT NULL,
  WRUERR            VARCHAR2(100 CHAR)          DEFAULT NULL,
  COMMANDTIMESTAMP  TIMESTAMP(6)                DEFAULT NULL  NOT NULL,
  FETCHTIMESTAMP    TIMESTAMP(6)                DEFAULT NULL  NOT NULL
)

Observe that INSERVID may be one of the 1400 types. So you can have say 1400 tables each devoted to 1 inserv. Would that be insane factor to complete ? I question.

We iterate within the no of inservs and run all of our queries against them. At this time we're getting rid of data therefore we pricier to overshoot 15+ million records.

1) We've distinct within the queries therefore it takes full table scan. Oracle Execution plan shows we're doing quite full table scan.

select distinct(inservid),commandtimestamp from statpd order by commandtimestamp desc;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   665 | 13300 | 79488 |
|   1 |  SORT UNIQUE         |             |   665 | 13300 | 69088 |
|   2 |   TABLE ACCESS FULL  | STATPD      |  4128K|    78M| 19406 |
--------------------------------------------------------------------
Note: cpu costing is off
10 rows selected.

2) Every table comes with an index on inserv id. It's assisted in lessening the price of queries, so what can we all do further ? Any tips/methods to create things faster ?

3) Will it seem sensible when we make our sql queries running against each table as parallel scripts running against each table. Would this lead to faster completion ?

4) We've enough RAM to load the entire DB in memory. Can you really do this with ORACLE.

Thanks ahead of time