Re: What is the efficient way to select data from huge database?

Thanks. I will try.

"Jasper Scholten" <jasc27054_at_NO-SPAM.yahoo.co.uk> wrote in message news:<3f9a1ba5$0$58714$e4fe514c_at_news.xs4all.nl>...
> Hi Henry,
>
> To answer your questions as far as I can at this moment:
>
> 1. Let the optimizer do it's work, 1 complex query should be okay as long as
> you analyze all tables excluding owned by SYS.
>
> 1/2. Where you might look into in this case is materialized views, this is
> the appropriate way for getting better response times.
>
> 3. I do not see what stored procedures could do. If you can do it in a plain
> SQL statement, do it in a plain SQL statement.
>
> 4. Yes
>
> 5. Let the optimizer do it's work. Size your database properly and 1 big
> query should be okay. Splitting up is more something you use when your bound
> in some sort of way or when you are using database types that are less
> scalable to handle such a huge amount of data.
>
> Extra comment: What I see from the amount of records is that it is all quite
> small, it depends on the right use of indexes, analyzing tables and
> datamodel to get good response in all cases.
>
> When the data grows over time, you may want to investigate in a year or
> something to use partitioning.
>
> HTH,
>
> --
> Jasper Scholten
> DBA / Application Manager / Systems Engineer
>
> "Henry" <web_poster03_at_yahoo.com> schreef in bericht
> news:b6015d6e.0310241853.3ce8c041_at_posting.google.com...
> > Hi experts of Oracle,
> >
> > I used Oracle for small queries, but now I have a task to create
> > WEB-based report by getting data, summarying ( several counts, min and
> > max) and re-formatting, where the data are from around 10 tables, each
> > of them is > 10k records, some larger ones have > 1 million records.
> > Using sql*plus to count all records in a table took 25 seconds
> >
> > Since it is web-based, the number one concern for me is speed. Hope
> > you Oracle gurus can give me some hints how to do it?
> > The following is what I can think about:
> > 1. Create a single complex query with all summary data (group by some
> > big categories); OR
> > 2. Create some intermediate tables to keep data I need by running
> > several queries each night, and the application uses them when user
> > run application.
> > 3. Can stored procedure do any help in improving speed here?
> > 4. Can materilized view do any help for such case?
> > 5. which is faster, many small queries to get data OR 1/2 big queries?
> >
> > Thanks
Received on Mon Oct 27 2003 - 22:54:45 CET