This discussion is archived
5 Replies Latest reply: Mar 5, 2013 7:13 AM by vittel RSS

Query on big table

vittel Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    PARTITION :)

    Cheers,
    Manik.
  • 2. Re: Query on big table
    vittel Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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:
    ----------------------------------------------------------------------------------------------------

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points