Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2009
    Posts
    9

    Question Unanswered: Problem with group by on millions of data

    Hi All.
    I work at datawarehouse in a telephony company. The problem i'm having is the following.
    I'm running a process that inserts the cost of calls in a table. The original data is in a partitioned table by date, the process is always ran for a specific date, and the amount of data on the partition is aproximately 20 million records.
    The purpose is that in my second table I will have the cost sum by operator and user (telephone number).

    I started getting the total amount and it is ok, then I made the sum to be by operator and it is ok too, but when I wanted to sum by phone number and made a sum on this resumed table I did not get the same grand total I first mentioned, and every time I run the process it always gives me different results and never equals the original total.
    Is it probably that there is a bug on my version of Oracle (10.2.0.2.0) on large amount of records? Or maybe there is a parameter that handle this?

    Please help me if anyone has suffered this.

    Thank you.

    Julio.

  2. #2
    Join Date
    Apr 2009
    Posts
    9
    I forgot to mention that the difference in the totals is maybe not bigger than 10% like if the DBMS would be missing some records.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I want to make sure I understand this situation.
    You have a couple of table (& we don't know anything about these tables).
    You have data in these table (& we don't know anything about the data).
    You have some local composed SQL (& we don't know what the SQL is).
    You are getting results which you deem is wrong & don't know why you are not getting answers you expect.

    So you conclude 1 of 2 possible causes:
    1) there is a bug on my version of Oracle (10.2.0.2.0)
    2) maybe there is a parameter that handle this?

    Implicit in the leap of logic is that there is NO possibility that the SQL may be what generates in the "erroneous" results.

    I know Oracle is too dumb to make up wrong answers.
    I suggest you produce a small subset of test data & debug the SQL using it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Apr 2009
    Posts
    9

    Arrow

    The problem here is that my partition has 20 million records. I don't make any changes to the process and the partition on the table doesn't have any changes too (because it's historical). So, I run the process two or three times and then every time I sum all the resultant records, and this total is always different!!

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > I run the process two or three times and then every time I sum all the resultant records, and this total is always different!!

    Repeating this whine, accomplishes nothing.

    Do you expect us to send you some magic pixie dust that makes things better for you?

    Why do you expect miracles when you have not provided us any relevant details?

    How did this SQL pass unit testing, integration testing, & get deployed into production while producing incorrect results?

    Are you claiming that no test/development system exists which can be used?
    Are you developing & deploying code on the Production system?

    I suggest you produce a small subset of test data & debug the SQL using it.
    I suggest you produce a small subset of test data & debug the SQL using it.
    I suggest you produce a small subset of test data & debug the SQL using it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Apr 2009
    Posts
    9

    Post

    Ok. Here is precise information.
    I created a new table which has the columns I need, I first thougth it was because of the join I was having problem.
    I have this columns:
    tel_number varchar2(30)
    cost number(16,2)
    amount_1 number(16,2)
    amount_2 number(16,2)
    seconds number
    operator varchar2(1)
    round_factor number(5)

    Now, the data is distributed like this:
    I have 13,528,334 records. On this table, I have 2,015,048 distinct tel_number, and the operators are divided on six types:
    E has 2,776,933 records
    I has 454,559 records
    L has 400,081 records
    N has 247,872 records
    O has 9,552,888 records
    V has 96,001 records

    What I am doing is a query that groups by operator and tel_number the sum of core and makes other operations using amount_1, amount_2, seconds and round_factor, and this will be on another table, the idea is to have records like these:

    TEL_NUMBER OPERATOR SUM(CORE)
    12345 E 115.20
    12345 O 16.45
    12345 V 1.10
    123467 I 114.40

    The query is this:
    select tel_number, operator, sum(cost), sum((((seconds*cost)/(cost+amount_1+amount_2))/round_factor )/DECODE(round_factor,1,60,1)) mins
    from temp_prueba_cuadre
    group by tel_number, operator;

    At most, I'll have 6 records for each tel_number, and my goal is that when I make a sum on that final table, it must have these totals:
    E 1921539.31
    I 877135.13
    L 302428.22
    N 162614.61
    O 5130186.05
    V 135752.58

    Now, the problem in fact (I'm seeing this today) is that for types I, L, N and V I have no problem (they have the correct totals). The problem is on types E and O, because the sum does not reach the total I'm telling you above. I see that those two types has more than 2 million records each one. Now, remember I'm grouping by tel_number and operator and the final total grouped by operator gives me always different results, but it's just for these two types.

    The data I'm giving you is for one specific date, but the problem is the same for every day, and the number of records is similar from day to day.

    I hope with this information you can bring me some ligth, because I don't think its too much data for Oracle to handle, but I'm realizing maybe there is any parameter that handles this.


    Thank you.

  7. #7
    Join Date
    Apr 2009
    Posts
    9
    Notice something else that is important: If i make a sum on my intermediate table just by operator, it is ok...

  8. #8
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    And the query you are executing is.... ?

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Here is my bottom line Oracle reality....

    If you run the exact same SQL (SELECT) multiple times & get different results each time, it means the data in the underlying tables is changing!
    Oracle is much too dumb to selectively lie to you & produce "random" results.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Apr 2009
    Posts
    9
    The query i wrote up is to insert to the final table:
    insert into llamadas
    select tel_number, operator, sum(cost), sum((((seconds*cost)/(cost+amount_1+amount_2))/round_factor )/DECODE(round_factor,1,60,1)) mins
    from temp_prueba_cuadre
    group by tel_number, operator;

    and when the process is finished i run this to verify:
    select tipo, sum(costo)
    from llamadas
    group by tipo;

    tipo is the same as operator.

    anacedent: the data is not changing because it's historical. And I run a query to verify the total on the original table and it's always the same. That's why I think it's something peculiar because those two operators have more than 2 million records.

  11. #11
    Join Date
    Apr 2009
    Posts
    9
    And another information, and that is what is really confusing me, the difference between the original total and the final total is not too big, on the contrary, it's small, but I need to be exact.

  12. #12
    Join Date
    Apr 2009
    Posts
    9
    Sounds like some records are not taken.

  13. #13
    Join Date
    Dec 2003
    Posts
    1,074
    Are you saying that

    Code:
    select tel_number, operator, sum(cost)
    from temp_prueba_cuadre
    group by tel_number, operator;
    ultimately gives you a different sum than a process which takes the above results, and then further sums them by operator? Something similar to

    Code:
    select operator, 
           sum(cost_by_phone_and_operator) as cost_by_operator
    FROM (select tel_number, 
                 operator, 
                 sum(cost) as cost_by_phone_and_operator
          from temp_prueba_cuadre
          group by tel_number, operator)
    group by operator;
    --=Chuck

  14. #14
    Join Date
    Apr 2009
    Posts
    9

    Talking

    No, because it's the same table. The process groups by tel_number in my second table (llamadas). What I do after running the process is verifying the totals by operator on table llamadas, and this total is that differs from the total on temp_prueba_cuadre.
    Now I tried transfering the data to another version of oracle and it works... Now I know it's something about my version of oracle. The rare thing is that the version i'm having trouble with (10.2.0.2.0) is newer than that one where the process runs correct (10.2.0.1.0).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •