Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Database Design for speed question

Re: Database Design for speed question

From: Ulrik Hoffmann <ulrik_at_hoffmann-kiel.de>
Date: Wed, 17 Nov 1999 21:56:45 +0100
Message-ID: <80v4rc$19ov2$1@fu-berlin.de>


Hi,

On Oracle 8 you have the possibilities of partition tables. Look at the online help for exact syntax, you can do with this something like

CREATE TABLE customers
Splitting Partitions ( <Columns...>)
PARTITION BY RANGE ( customer_ID)

    ( PARTITION cust1 VALUES LESS THAN ( 1000 )       TABLESPACE tsa,
    PARTITION cust2 VALUES LESS THAN ( 2000 )       TABLESPACE tsb,
    PARTITION cust3 VALUES LESS THAN ( 3000 )       TABLESPACE tsc,
    PARTITION cust4 VALUES LESS THAN ( 4000 )       TABLESPACE tsd);

The tables data will be stored on different tablespaces, high performant if on different disks. If you select data form the table, the database handles
each partition like a smaller table (sorf of...).

The idea of you is what oracle7 had with partition views. The program logic has to store the data in say 50 tables (seems a lot to me...) and one view like.

create view customers as select * from customer1 union all select * from customer2...

Data which is often needed is automatically 'pinned' in the sga.

Hope it was a little help,

bye
Uli

[...]
> I'm thinking of a possible redesign of a database to increase speed of
> transactions. Any musings or corrections would be welcomed.
>
> The database is feb by the Web. Hundreds of sessions are kept active
> and idling to help get data in and out as quickly as possible. Java
> opens and controls these sessions. Data is customer specific and the
> customer must be verified before query or input is performed.
> Currently the data is essentially in a couple of big tables, and the
> tables are getting bigger and bigger. To reduce the time to find and
> input specific customer data, I'm thinking along the following lines:
>
> Create, say, 50 tables that are identical in structure to hold the
> customer data. Create a small 'lookup' table to point to which table a
> particular customer's data is in. As new customers come on-line, they
> are methodically put into the next available table. When table 50 is
> reached, is starts over at table 1. Pin the small 'lookup' table in
> RAM for speed (Is that possible?). Has anyone done this? Does Oracle
> provide this with some other method already?
[...] Received on Wed Nov 17 1999 - 14:56:45 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US