5 Replies Latest reply: Mar 5, 2013 9:13 AM by vittel RSS

    Query on big table

    vittel
      Hi,

      Oracle 11gR2
      I have a very big table more than 1billion rows. To reduce de volume of this table we need to aggregate data.
      for that we will create a new table using the data agergated.
      SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
      
      PLAN_TABLE_OUTPUT
      ----------------------------------------------------------------------------------------------------
      Plan hash value: 18469406
      
      ----------------------------------------------------------------------------------------------------
      | Id  | Operation          | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT   |                       |  1146M|   106G|       |    39M  (1)|132:01:06 |
      |   1 |  HASH GROUP BY     |                       |  1146M|   106G|   158G|    39M  (1)|132:01:06 |
      |   2 |   TABLE ACCESS FULL| ASP_ADVANCED_STAT_OLD |  1146M|   106G|       |  4397K  (2)| 14:39:35 |
      ----------------------------------------------------------------------------------------------------
      here is the query explain paln As you see we have to download 106G bytes!!!

      do you have any idea to tune it?

      regards,
        • 1. Re: Query on big table
          Manik
          PARTITION :)

          Cheers,
          Manik.
          • 2. Re: Query on big table
            vittel
            Yes we need to use partitioning but for that we need to create a new one with PARTITIONS

            Can we partion an existing table?

            regards,

            Edited by: vittel on Mar 5, 2013 3:49 PM
            • 3. Re: Query on big table
              Manik
              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:869096700346654484
              http://www.oracle-base.com/articles/misc/partitioning-an-existing-table.php

              Cheers,
              Manik.
              • 4. Re: Query on big table
                Gaff
                Apparently there are several ways to do that.


                http://www.dbasupport.com/forums/showthread.php?28652-Existing-table-to-be-converted-into-Oracle-Partition!Possible!
                • 5. Re: Query on big table
                  vittel
                  Hi,

                  In the first time we need to just aggregate data in the table by date.
                  Partitioning this table will be the seconde steep and we don't have the go to do it.

                  Please find here the script and tell me if you have an idea to tune it...
                  create table ASP_ADVANCED_STAT
                  tablespace ASP_ADV_PROD
                  as select
                  cast(min(ADVANCED_STAT_ID) as NUMBER(10)) as ADVANCED_STAT_ID,
                  STAT_CONFIG_ID,
                  STAT_PERIOD_CONFIG_ID,
                  CAST(min(FRANKING_DATE) as DATE) as FRANKING_DATE,
                  BATCH_ID,
                  cast(sum(nvl(count,1)) as NUMBER(7)) as COUNT,
                  PSD_SERIAL_NUMBER,
                  cast(CUSTOMER_PRODUCT_ID as NUMBER(10)) as CUSTOMER_PRODUCT_ID,
                  POSTAL_PRODUCT_CODE,
                  RATE_PART_NUMBER,
                  DEPARTMENT_ID,
                  OPERATOR_ID,
                  cast(FRANKING_AMOUNT as NUMBER(11,3)) as FRANKING_AMOUNT,
                  cast(SERVICES_AMOUNT as NUMBER(11,3)) as SERVICES_AMOUNT,
                  cast(WEIGHT as NUMBER(10,3)) as WEIGHT,
                  cast(SURCHARGE as NUMBER(10,3)) as SURCHARGE,
                  DESTINATION_ZIP_CODE,
                  PRINTING_MODE,
                  SLOGAN_ID,
                  min(CREATED_TIME) as CREATED_TIME,
                  max(UPDATED_TIME) as UPDATED_TIME
                  from ASP_ADVANCED_STAT_OLD
                  group by STAT_CONFIG_ID,STAT_PERIOD_CONFIG_ID,
                  PSD_SERIAL_NUMBER,CUSTOMER_PRODUCT_ID,RATE_PART_NUMBER,
                  DEPARTMENT_ID,OPERATOR_ID,FRANKING_AMOUNT,SERVICES_AMOUNT,
                  WEIGHT,SURCHARGE,DESTINATION_ZIP_CODE,POSTAL_PRODUCT_CODE,
                  PRINTING_MODE,SLOGAN_ID,
                  trunc(FRANKING_DATE),BATCH_ID
                  
                  
                  PLAN_TABLE_OUTPUT
                  ----------------------------------------------------------------------------------------------------
                  Plan hash value: 2731405675
                  
                  ----------------------------------------------------------------------------------------------------
                  | Id  | Operation              | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                  ----------------------------------------------------------------------------------------------------
                  |   0 | CREATE TABLE STATEMENT |                       |  1146M|   106G|       |    42M  (1)|141:28:39 |
                  |   1 |  LOAD AS SELECT        | ASP_ADVANCED_STAT_TMP |       |       |       |            |          |
                  |   2 |   HASH GROUP BY        |                       |  1146M|   106G|   158G|    39M  (1)|132:01:06 |
                  |   3 |    TABLE ACCESS FULL   | ASP_ADVANCED_STAT_OLD |  1146M|   106G|       |  4397K  (2)| 14:39:
                  ----------------------------------------------------------------------------------------------------