I'd definitely go with the partitioning. The only question that I'd have
is whether I would make the partition large enough to satisfy a single
user's query or small enough that it could reside in the SGA. I guess that
that depends on how many users are querying at one time. Just off the top
of my head I'd say that if you have more than 10 simultaneous users go with
larger partitions. Each individual query may take longer but no query will
get stuck in the queue. With the smaller partition, each individual query
should be optimized but if there are too many queries, it may take a while
before any particular query can get up to bat.
Take it with a grain of salt. Just thinking off the top of my head on the
first day back from holiday.
"Rachel
Carmichael" To: Multiple recipients of list ORACLE-L
<carmichr@hot <[EMAIL PROTECTED]>
mail.com> cc:
Sent by: Subject: Datawarehouse suggestions?
root@fatcity.
com
09/02/2001
12:25 AM
Please
respond to
ORACLE-L
Okay, first the rant.... they want me to build a new database to hold logs
of every time someone hits a page on our website or uses one of the
functions on the site. Without a new server or disk, on a box that
currently holds the OLTP database, on a disk array that is RAID 5 and that
EMC wants to take BACK disks from. In other words, they want magic. Where
did I leave that rabbit and hat?
end rant, here's the setup:
Conservative estimate says that one of the two main tables (at least for
now) will grow to a GB in a year or two, the other will hit 500M in that
timeframe. Rough estimates of # of rows is 273M for the first table, 70M
for
the second over 2 years. Rows are < 200 bytes at max. There will be
several
small lookup tables and the number of logging tables will increase over
time.
They are NOT talking about purging data, ever. And we are hoping that the
volume INCREASES, not decreases or remains the same, which means the space
estimates are no more realistic than throwing a dart at a board and picking
a number.
Oh yeah, did I mention that they want this to be a reporting database for
external customers (read revenue stream) and want these external people to
be able to query on any combination of columns -- so they want indexes on
EVERY column.
I will have a nightly maintenance window for loads of prior day's data. I'm
thinking to do a shutdown, change the init.ora to tune for heavy batch,
startup, drop indexes, load data, recreate indexes, shutdown, backup,
startup with init.ora tuned for querying.
question:
I've always worked on either batch reporting databases or OLTP, this will
be
the first semi, sort of data warehouse for me. Advice please on how to
build
this, as I am doing this one from scratch and can plan it.
I'm thinking:
database will be 8.1.7, Solaris 2.7
rbs and temp tablespaces as LMTs, remaining tablespaces dictionary-managed
db_block_size 16K
a "large table" tablespace with initial/next at 10M
a "large index" tablespace as above
a "small table" tablespace with initial/next at 16K
a "small index" tablespace as above
possibly partitioning (I have to check our licenses) on the timestamp
field.
Any suggestions, gotchas, "this is WRONG, here's how to do it" comments?
Thanks
Rachel
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
