Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Database Design for speed question
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