Best way to build a very large table

I'm wondering the best way to build a table that will end up containing a very large number of rows. Probably at least 10 million and could easily end up even larger. It's actually going to contain individual call details from a number of different phonelines.

Because it will be so large I'm want to make querying it as efficient as possible. A given client may have any number of phonelines. If I want to query on all calls made for client X will it improve the speed of the query significantly if I include in my calldetails table the client_id and search on that. Or would querying a seperate table to get a list of phonelines and then searching for the phonelines in the call_details table give comparable performance.

Any ideas on improving performance will be welcome.

Check out "Oracle Performance Tuning" ISBN 1565922379 by Gurry and Corrigan and/or any information you can find regarding partitioned views and parallel queries. Recommends creating several smaller tables seperated by date and creating partitioned view across the tables. This should also help you with a purging strategy as a table with an out-of-date range can be dropped easily and a current table created with only minimal recoding. There is no in-depth discussion on that particular topic in this book, but it is a very good resource for performance tuning at the database, pl/sql and form level in general.

Perhaps you can answer a more specific question. Thinking through things logically it seems to me that if I put a customer id into my calls table I would be able to retreive all calls for a given customer faster than if I had to check if the service number of each call matched a given customer.

i.e.
WHERE cust_id = 'xxxx'

should run faster than

WHERE service_number IN (SELECT service_number FROM services WHERE cust_id = 'xxxx')

In cases where a customer has 20 numbers I'd expect the 1st query to run about 20 times faster than the 2nd. Is this correct?

You should also look at partitioned tables and maybe bitmap join indexes.

A little explaination of the suggestion above:

If you have a big table with a structure like:

CustID, DateID, ServID, ....

You can partition it by date range, or customer id, or service or whatever and even have subpartition, something like main partitioning by date (i.e. monthly partitions) and subpartitions by customer.
With such a table (and updated statistics of course) Oracle's optimizer will do "partition pruning", this means that it will not consider partitions that will not be affected by your query, scanning only indexes or rows that matter.
This gives you a much better performance on very big tables.
Partitioned tables can also be managed (loaded, unloaded ...) by partition, which gives also better manageability.

If you can add the id to the table, you should. It is absolutely more efficient to eliminate a subquery where you can. But, in order to make it perform, you must index on the ID that you have added. Otherwise, you will still be doing full table scans across that column.

You might consider index-organized tables or partitioning by ID range (if the ID query is a driving consideration).