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?