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

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Henry
    #1

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

    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
    Tags: None
    • Jasper Scholten
      #2
      Re: What is the efficient way to select data from huge database?

      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@y ahoo.comschreef in bericht
      news:b6015d6e.0 310241853.3ce8c 041@posting.goo gle.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

        Comment

        • Henry
          #3
          Re: What is the efficient way to select data from huge database?

          Thanks. I will try.


          "Jasper Scholten" <jasc27054@NO-SPAM.yahoo.co.u kwrote in message news:<3f9a1ba5$ 0$58714$e4fe514 c@news.xs4all.n l>...
          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@y ahoo.comschreef in bericht
          news:b6015d6e.0 310241853.3ce8c 041@posting.goo gle.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

            Comment

            Previous template Next
            Working...