Re: Very Large Database, dont know what my options are
Date: Wed, 22 Oct 2008 11:05:47 -0700 (PDT)
On Oct 22, 4:11 am, gazzag <gar..._at_jamms.org> wrote:
> On 21 Oct, 01:57, "victor.s.em..._at_gmail.com"
> <victor.s.em..._at_gmail.com> wrote:
> > Hi,
> > Im a newbie DBA and a little bit stuck with a problem with an
> > Oracle Database and I would appreciate your help. I have a database
> > of billions of records. I need to dump a set of data for
> > investigation. I was using SELECT * OUTFILE... Because of the size of
> > the DB and lack of indexes (data model was poorly designed from the
> > get-go), the query ran for 4 hours, and I was cut off as Oracle has
> > setup the profile of every user to only allow a query to run for 4
> > hours. So I was wondering
> > a) Without creating indexes, is there a way I can do a plaintext
> > file dump of a set of data from a table more efficiently? If not,
> > other than changing the user profile to allow unlimited query cpu/
> > session resource, are there any other means?
> > b) Im anticipating that the data the analyst needs to investigate
> > in will have to stored within a table of some sort for querying.
> > Aside from creating indexes on that table, what other efficiency
> > improver would you advice to make sure that this new table won't take
> > forever to query? Im thinking about denormalizing the table.
> > Thanks,
> > Victor
> I agree with Joel Garry here. I obviously don't know the business
> case, but I am always suspicious of such requests. Surely the best
> place for the data is the RDBMS? Surely the best tool for
> manipulating the data is the RDBMS?
At the risk of disagreeing with myself :-) it depends. I was talking to a fellow on the train, who turned out to be a statistician for a big credit bureau. Turned out we had quite similar opinions about unix tools, AIX idiosyncracies, silly user requests and so forth. So I asked him about analytics, and boy, did he have opinions about that! Definitely a vertical market that Oracle built-ins don't hit there. No, I don't know enough to give any examples, I only see people buying packages to do analytics by sucking from the Oracle db into dark unmentionables.
> I was once asked by a manager if I could "dump" a database into MS
> Excel (*shudder*) for him. The database, while not huge, is 9Gb in
> size and consists of 400-odd tables. The politest way that I could
> tell him that he obviously doesn't know what he's talking about is to
> inform him that Excel is limited to 65535 rows :)
:) Still easier than the dirty data I get _from_ Excel, not to mention wasting time figuring out and explaining to people why their forumulae don't match the properly rounded numbers from the db.
-- @home.com is bogus. Anyone else notice Yahoo stock down by 2/3 in 6 months? And yet Kirkorian bails on Ford...Received on Wed Oct 22 2008 - 13:05:47 CDT