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

On Oct 20, 5:57 pm, "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

It helps to state all versions, options and platform info. If you are on a unix platform, there are tools that can help this kind of investigation. The general idea is to select out a small representative set of data, and use unix tools to manipulate it as you need, then once you get it all working, you can pipe them all together and do the massive work necessary without blowing out oracle sort or buffer areas. Then you can either use external tables or sqlloader to put it back into a db, depending on what exactly you need the analyst to do.

In some cases, it can be more efficient to do everything within the database, given the modern DW and DSS enhancements of Oracle. It just depends. Just as, it depends on what you are doing, exactly, as to whether you want or need indices. Sometimes it actually is better to full table scan, doing this kind of analysis may require quite different tuning than the db is currently set up for. And don't let anyone tell you things like "if you are getting x% of the table, y access is better." The issues are far more sophisticated than that. See the performance tuning manual, google for writings by Jonathan Lewis and Richard Foote, and generally learn about data warehousing. You may even want to get an experienced DBA to help.

What you may want to avoid is doing all this DSS work on a production OLTP system, if that is what you have.

jg

--
@home.com is bogus.
http://www.imdb.com/title/tt0074412/#comment