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

[Henry]

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

[Jasper Scholten]

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" <we**********@yahoo.com> schreef in bericht
news:b6**************************@posting.google.c om...

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

[Henry]

Thanks. I will try.
"Jasper Scholten" <ja*******@NO-SPAM.yahoo.co.uk> wrote in message news:<3f***********************@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" <we**********@yahoo.com> schreef in bericht
news:b6**************************@posting.google.c om...

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