Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2008
    Posts
    5

    Unanswered: Oracle 10G perfrmance issue with group by

    Hi All,
    I am having unique problem with group by in Oracle 10G.

    Here is the query

    Query 1 -- 7 Min
    select unit_id, sum(gross_sales)
    from transaction_syn
    where period_id between 20060101 and 20060131
    group by unit_id

    Query 2 - 1 min
    select unit_id
    from transaction_syn
    where period_id between 20060101 and 20060131

    Table is indexed on period id. For some reason query 1 is not using the index, but the query 2 is using the index.

    Why is query 1 not using the index?
    Any help ?

    DB : Oracle 10.2.0.3
    OS : Sun 5.9

    Thanks in advance !
    Deeksha

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by deeksha88888
    Why is query 1 not using the index?
    My assumption would be that because the gross_sales column is included in the query, the index is not used.

    How many rows does the condition affect relatively to the whole table?
    Usually Oracle decides that a full table scan is more efficient if more than 10% of the total number of rows is affected.

    When adding a non-indexed column to the select list, the cost when using the index might even be higher than without the index, as Oracle needs to first look up the index entry, and then use that information to retrieve the gross_sales column for that row. Therefor the full table scan is assumed to be cheaper.

    You might try to replace the index on unit_id with a index on (unit_id, gross_sales), in that case Oracle would be able to retrieve all the needed information from the index.

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Could you post the two execution plans ?

    BTW, the way you have written dates is incorrect but you must know this since without quotes thiese queries will not work... I just wanted to point out that if you are passing the dates as strings, then always add TO_DATE since implicit conversions, especially with dates, can lead to many many problems.

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by RBARAER
    BTW, the way you have written dates is incorrect but you must know this since without quotes thiese queries will not work... I just wanted to point out that if you are passing the dates as strings, then always add TO_DATE since implicit conversions, especially with dates, can lead to many many problems.
    Provided of course that the period_id is a date column. I'm betting that it isn't and that some... one has decided to make it a number (hence the yyyymmdd format) or even worse a Character datatype.
    (I must admit, I don't see why they should fail without the quotes, maybe I missed something)

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by pablolee
    (I must admit, I don't see why they should fail without the quotes, maybe I missed something)
    Because date literals have to be enclosed in single quotes:
    Quote Originally Posted by manual
    You can specify a DATE value as a string literal, or you can convert a character or numeric value to a date value with the TO_DATE function.
    http://download-uk.oracle.com/docs/c...3.htm#BABGIGCJ

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by shammat
    Because date literals have to be enclosed in single quotes:
    Exactly :
    Code:
    SQL> select * from dual where sysdate between 20060101 and 20060131;
    select * from dual where sysdate between 20060101 and 20060131
                                     *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected DATE got NUMBER
    
    
    SQL>
    And as Shammat pointed out in the doc, TO_DATE does not work with numbers...

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  7. #7
    Join Date
    Dec 2007
    Posts
    253
    Yes, My final statement was clearly lacking in caffeine. of course you are both correct providing (as said earlier in my original post) the period_id column is in fact a date. Again, I'd bet that some... one has made it either a character or number datatype (I'd be quite happy if the OP were to post back and refute this of course)
    And as Shammat pointed out in the doc, TO_DATE does not work with numbers...
    I don't think that he did point this out from the docs. Infact my reading of the snippet would be the opposite:
    you can convert a character or numeric value to a date value with the TO_DATE function.
    and a demo would seem to prove that:
    Code:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> create table dte as select trunc(sysdate) dcol from dual;
    
    Table created.
    
    SQL> select * from dte;
    
    DCOL
    ---------
    07-MAR-08
    
    SQL> select * from dte
      2  where dcol = to_date(20080307,'yyyymmdd');
    
    DCOL
    ---------
    07-MAR-08
    And if you look at the stdspec script in rdbms/admin, the to_date function is actually overloaded to accept a number as the first parameter
    Although I think that the documentation should actually say:
    you can convert a VALID character or numeric value to a date value with the TO_DATE function.
    Maybe I am misunderstanding the point that you were trying to make.

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by pablolee
    I don't think that he did point this out from the docs. Infact my reading of the snippet would be the opposite


    Quote Originally Posted by SQL Reference
    TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype
    TO_DATE

    Implicit conversions going all around here !!!!!

    One uses TO_DATE to avoid implicit conversions, not to help implicitly converting numbers to CHAR and then to DATE.

    That works, OK, but yet for me it is wrong.

    To the OP : and these execution plans ?

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  9. #9
    Join Date
    Jan 2008
    Posts
    5
    Thanks guy’s for your response.

    Unit_id,gross_sales and period_id all are number fields.

    -- I tried converting to date, but got error this error "ORA-00932 inconsistent datatypes expected NUMBER got DATE"

    -- I also tried creating index on unit_id and period_id, but it did not improve the performance.

    -- I do not like to create index on unit_id and gross_sales

    -- Listed sql takes on 26 sec

    select unit_id,gross_units
    from transaction_new_2
    where period_id between 20070101 and 20070131

    Could this be issue with group by? Why is oracle taking long time to group by?

    So, how to I improve the performance?

    Thanks
    Deeksha

  10. #10
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    -- I do not like to create index on unit_id and gross_sales
    Why not?

    So, how to I improve the performance?
    You can at least try to create the index on (unit_id, gross_sales) and see if that improves the query.

    By all means, show us the execution plan of the slow query.
    Without it, everything is just guessing. Ideally from within SQL*Plus with autotrace turned on, that gives more details (especially about IO) than a simple EXPLAIN PLAN.

  11. #11
    Join Date
    Dec 2003
    Posts
    1,074
    Maybe a rewrite of the SQL? I think the Optimizer's too smart for something this simple, but it's worth a try:

    Code:
    select unit_id, sum(gross_sales) 
    from (select unit_id, gross_sales 
          from transaction_syn
          where period_id between 20060101 and 20060131) inline_view
    group by unit_id
    --=cf

  12. #12
    Join Date
    Dec 2007
    Posts
    253
    Implicit conversions going all around here !!!!!
    Please point out the implicit conversion(s) in my code above. But before you do, please have a look at the standard package.
    !!!!!

  13. #13
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    [QUOTE=pablolee]Please point out the implicit conversion(s) in my code above.QUOTE]
    I did :
    Quote Originally Posted by rbaraer
    not to help implicitly converting numbers to CHAR and then to DATE
    Please have a look at the TO_DATE documentation I pointed out. NUMBER datatype is NOT supported as first argument of TO_DATE :
    Quote Originally Posted by SQL Reference - TO_DATE
    TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype.
    So in your code below :
    Code:
    select * from dte where dcol = to_date(20080307,'yyyymmdd');
    As 20080307 is a NUMBER, NOT supported by TO_DATE, Oracle implicitly converts it to CHAR : 20080307 => '20080307' and then converts '20080307' to DATE.

    Hence my previous post.

    Hope it is clearer now

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  14. #14
    Join Date
    Dec 2007
    Posts
    253
    And did you bother to look at the standard package where there is the overloaded:
    TO_DATE(left NUMBER, right CHAR) IS
    BEGIN
    TO_DATE(TO_CHAR(left), right);
    END;
    Not supported, fair play, implicitly converted, wrong. I'm done with this discussion.

  15. #15
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by pablolee
    And did you bother to look at the standard package where there is the overloaded:
    TO_DATE(left NUMBER, right CHAR) IS
    BEGIN
    TO_DATE(TO_CHAR(left), right);
    END;
    Not supported, fair play, implicitly converted, wrong. I'm done with this discussion.
    This is a system package that you should not even look at. This IS IMPLICITLY CONVERTED as it IS NOT DOCUMENTED.

    The overload is just here to PERFORM the IMPLICIT conversion (it HAS to be done somewhere by Oracle, isn't it ?).

    If you don't want to see it, then I cannot force you .

    Yet, I must admit this is not a serious implicit conversion, but it is one, and again, using TO_DATE to avoid implicit conversion and doing one within the TO_DATE call does not seem very logical to me.

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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