From: jasc27054@NO-SPAM.yahoo.co.uk   
      
   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"  schreef in bericht   
   news:b6015d6e.0310241853.3ce8c041@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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)