database design

[mayday77]

hi
i need to load daily records of around 5 million into the oracle database, so now i am trying to come up with a simple design for efficient search queries, the data for each row can be quite diff sometimes as they are actually call details, like the phonenumber, called number, timestamp and stuff, since there is the case.. i'm not sure actually if i just a single table to store of all them? as i don see the need to normalise..
so how shd the design be like then?

thank you

[slimdave]

Have you had a look at the Oracle Data Warehousing Guide? Features of interest would be partitioning, maybematerialized views, andindexing stuff.

[grjohnson]

Also Parallel Querys and Bitmap Join Indexing.

[mayday77]

hi
thanks for the advice, i'm new to data warehousing, so may i know
wats the diff between the oracle database and the warehouse? does the oracle database enterprise edition supports this oracle warehousing?
or its a completely new application to use?

thanks

[slimdave]

Warehousing is not a completely separate product, although partitioning is a separately licensed option.

DW is more a set of techniques, and a different mindset, to OLTP databases.

[mayday77]

hi slimdave
thanks for the info, so doea it mean that after installing the normal oracle database, we can implement the data warehousing concepts? then what does this oracle datawarehouse builder application for? is it the same thing?

thanks

[slimdave]

There isn't a data warehouse builder aplication in Oracle, it's just a set of features, techniques, and configurations that you would use to build a data warehouse in Oracle.

A strong consideration in the design is what sort of queries/reports are going to be run against it. You might want to normalize, you might not. Since this data gets loaded once then queried many times, the design ought to be more strongly influenced by the type of query.

Anyway, the Oracle Data Warehousing Guide will give you advice on the types of structures and techniques that will let you load 5 million orws per day into the db with the minimum of effort.

This would depend on whether you are trickling the records in throughout the day, or receiving them in one batch once a day. Let me know and we'll talk more.

[mayday77]

hi there
thanks for the info again, i will be receiving the data in a single huge batch per day, and after which we could do queries for report or case solving purposes when we finished loading them into the database, so i also wish to know if these features/ configurations could be set while creating a database using oracle 9i enterprise manager, and in addition, we would need to keep maybe around 2 months worth of records...which we can use anytime..

thanks

[slimdave]

Sounds like the Partitioning Option, which is separately licensed, would be beneficial.

If you range-partition the major table that you are loading records to, you can use "partition exchange" to load data to the table, and "drop partition" to remove old data.

Keep the number of tables low, and and don't kill yourself trying to normalize it -- you want to try minimizing huge joins in the regular queries that will be run against the data.