Re: What is the efficient way to select data from huge database?
Date: Sat, 25 Oct 2003 08:43:52 +0200
Message-ID: <3f9a1ba5$0$58714$e4fe514c_at_news.xs4all.nl>
Hi Henry,
To answer your questions as far as I can at this moment:
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 Sat Oct 25 2003 - 08:43:52 CEST