Re: Very Large Database, dont know what my options are

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.

jg

--
@home.com is bogus.
Anyone else notice Yahoo stock down by 2/3 in 6 months?  And yet
Kirkorian bails on Ford...