Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Oct 2003
    Posts
    51

    Unanswered: Oracle-sql-immediate reply needed

    select employee_id,(sum(get_worked_hours(to_date('1-nov-2002'),
    to_Date('15-nov-2002'))))workedhours
    from cen.timesheet_vw group by employee_id;

    ---------------------------------------------------------------------
    The result appears like this
    -----------------------
    EmployeeId Workedhours
    --------------------------------
    1657 30
    1345 40
    1234 50

    Workedhours is got from the function get_worked_hours which accepts 2 parameters. I want my SQL statement to select the records where the Workedhours <>40.

    How to do this.
    sampath gowri

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    having clause
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    Oct 2003
    Posts
    51
    Please make the answer specific.
    Thank you
    sampath gowri

  4. #4
    Join Date
    Feb 2004
    Posts
    108
    SELECT employee_id, workedhours FROM
    ( select employee_id,
    (sum(get_worked_hours(to_date('1-nov-2002'), to_Date('15-nov-2002'))))workedhours
    from cen.timesheet_vw group by employee_id
    )
    WHERE workedhours <> 40

  5. #5
    Join Date
    Oct 2003
    Posts
    51
    Thank you all of you.
    sampath gowri

  6. #6
    Join Date
    Apr 2004
    Posts
    246
    please at least read the manual - I told you what to read about, it's not that hard to read, is it? And for other guy - why the hell do use an in-line view for something that doesn't need it. The correct answer is:

    select employee_id,
    (sum(get_worked_hours(to_date('1-nov-2002'), to_Date('15-nov-2002'))))workedhours
    from cen.timesheet_vw group by employee_id
    having (sum(get_worked_hours(to_date('1-nov-2002'), to_Date('15-nov-2002')))) <> 40
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  7. #7
    Join Date
    Aug 2001
    Posts
    66
    Quote Originally Posted by shoblock
    why the hell do use an in-line view for something that doesn't need it
    Care to tell us why not?
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  8. #8
    Join Date
    Apr 2004
    Posts
    246
    why not?

    well which makes more sense:

    select * from tab;

    or

    select * from ( select * from ( select * from tab )));


    make code as simple and precise as possible, don't add extraneous crap. It's just going to make things worse down the road. Using the query:

    SELECT employee_id, workedhours FROM
    ( select employee_id,
    (sum(get_worked_hours(to_date('1-nov-2002'), to_Date('15-nov-2002'))))workedhours
    from cen.timesheet_vw group by employee_id
    )
    WHERE workedhours <> 40

    what happens when someone needs to join another table to it? Do they join to that in-line above, or make the above statement an entire in-line view to the new query? And when you make something an in-line view, you change the way it processes. The above query will return all the results from the group by, create a memory table of the results, and then apply the <>40 where clause. As the data grows you'll notice differences in processing between the two methods. And it's worse when you join something to it. In-line views are forced to resolve prior to joining to everything else. So:

    select col... from (select * from t where...), x, y where ...

    this query will force the in-line query on t to resolve fully, returning it's rows to be joined to x and y. But:

    select col... from t, x, y where ...

    will allow oracle to decide which order to access things, makes more indexes available, and can use less memory because it doesn't need to create/hold a result set for the rest of the query. The same concepts apply to:

    where (col1, col2,...) in (select .... from other_Tab where...)

    Obviously, there are perfectly valid times to use each and every construct made available by oracle. That's why they're there - because they all serve a purpose. But using it just for the hell of it, or because you don't actually know the correct syntax, is not good programming. It's like using a goto statement to exit a loop - it works, but it's bad programming.

    Now, when you want to outer-join a table to two or more tables (which is not allowed) an in-line view is the solution. When one of the tables in your join has 5 million rows, but will only return 1 row based on the non-join where clauses, putting it in an in-line view can improve performance because you know it will resolve down to one row, as opposed to doing a wasteful merge join (of course, nothing is guaranteed, so you write it both ways and see which is faster).

    Does that help to explain why not?
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  9. #9
    Join Date
    Aug 2001
    Posts
    66
    > Does that help to explain why not?

    No offence but no.

    > which makes more sense.

    Both make perfect sense. The first is simpler, obviously. Did anyone say that putting SELECT ... FROM () around a SELECT was not going to make it harder to read? I thought you were comparing it to the use of HAVING in this example. I use in-line views rather a lot so perhaps I am biased but I tend to feel that the duplication of logic in the HAVING clause makes the statement somewhat less readable not to mention less maintainable and since the two queries appear to be pretty much equivalent (see below) I would probably prefer the view.

    > what happens when someone needs to join another table to it?

    Oracle supports both. I don't really get your point here. Wouldn't the same question apply to joining to either query? It seems to me you cannot trivially join tables to a grouped result set without considering whether it will affect the results of the grouping.

    > The above query will return all the results from the group by, create a memory table of the results

    Don't agree. The view is not materialized in this case, it is merged (see below).

    > In-line views are forced to resolve prior to joining to everything else

    Don't agree. This applies to some specific cases only, and not this one (see below).

    > As the data grows you'll notice differences in processing between the two methods

    Based on the identical execution paths (see below) I am tempted to disagree on this one also.

    > when you want to outer-join a table to two or more tables (which is not allowed)

    This one too I'm afraid. Outer-join to one or more tables has been legal in Oracle (by way of ANSI syntax) for several major releases.
    Code:
    Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 - Production
    
    SQL> CREATE TABLE timesheet (
      2    employee_id NUMBER,
      3    start_date DATE,
      4    end_date DATE);
    
    Table created.
    
    SQL> INSERT INTO timesheet VALUES (
      2    1, TO_DATE ('1-NOV-2002'), TO_DATE ('15-NOV-2002'));
    
    1 row created.
    
    SQL> CREATE OR REPLACE FUNCTION get_worked_hours (
      2    start_date IN DATE,
      3    end_date IN DATE)
      4    RETURN NUMBER
      5  AS 
      6  BEGIN
      7    DBMS_OUTPUT.PUT_LINE ('get_worked_hours');
      8    RETURN (end_date - start_date) * 24;
      9  END;  
     10  /
    
    Function created.
    
    SQL> SET AUTOTRACE ON EXPLAIN;
    SQL> SELECT   employee_id,
      2           SUM (
      3             get_worked_hours (
      4               start_date, end_date)) workedhours
      5  FROM     timesheet
      6  GROUP BY employee_id
      7  HAVING   SUM (
      8             get_worked_hours (
      9               start_date, end_date)) <> 40;
    
    EMPLOYEE_ID WORKEDHOURS
    ----------- -----------
              1         336
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE
       1    0   FILTER
       2    1     SORT (GROUP BY)
       3    2       TABLE ACCESS (FULL) OF 'TIMESHEET'
    
    
    
    SQL> SELECT employee_id, workedhours
      2  FROM  (SELECT employee_id,
      3                SUM (
      4                  get_worked_hours (
      5                    start_date, end_date)) workedhours
      6         FROM     timesheet
      7         GROUP BY employee_id)
      8  WHERE  workedhours <> 40;
    
    EMPLOYEE_ID WORKEDHOURS
    ----------- -----------
              1         336
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE
       1    0   FILTER
       2    1     SORT (GROUP BY)
       3    2       TABLE ACCESS (FULL) OF 'TIMESHEET'
    
    
    
    SQL>
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  10. #10
    Join Date
    Apr 2004
    Posts
    246
    Okay, first - the reference to outer-joins was to indicate that I am not opposed to in-line views. I use in-line views extensively, but not as a catch all way to avoid some other working syntax.

    2nd, the issue of other people joining to something later is that they may not understand why the in-line view was used, they may remove it, they may write something that performs poorly because of it. Yes, my overly simple example - select * from (select * from... - doesn't prove that oracle performs differently. It was used to illustrate that somethings can be overkill - when used for no real reason. Take for example:

    SELECT employee_id, workedhours FROM
    ( select employee_id,
    (sum(get_worked_hours(to_date('1-nov-2002'), to_Date('15-nov-2002'))))workedhours
    from cen.timesheet_vw group by employee_id
    )
    WHERE workedhours <> 40

    Now, we all know that this is not the same as:

    select employee_id,
    (sum(get_worked_hours(to_date('1-nov-2002'), to_Date('15-nov-2002'))))workedhours
    WHERE workedhours <> 40
    from cen.timesheet_vw group by employee_id

    However, the problem is that some other person may modify the sql (with the in-line view) and decide to rewrite it, moving the where clause inside the in-line view, which would be incorrect. Using a having clause does not make it less readable (unless you don't know what a having clause is, and then you should read a manual). To the contrary, the having clause makes it very clear what the code is doing.

    Also, consider the following simple examples to get the % of rows in I2 with a given value of V:

    <pre>
    select (select count(*) from i2 where v='a') / (select count(*) from i2)
    from dual;

    PLAN
    ------------------------------------
    SELECT STATEMENT
    1: TABLE ACCESS SYS.DUAL(3) FULL


    select a/b
    from (select count(*) a from i2 where v='a'), (select count(*) b from i2);

    PLAN
    ----------------------------------------
    SELECT STATEMENT
    1: NESTED LOOPS
    1: VIEW MIKE.(3)
    1: SORT AGGREGATE
    1: TABLE ACCESS MIKE.I2(4) FULL
    2: VIEW MIKE.(1)
    1: SORT AGGREGATE
    1: TABLE ACCESS MIKE.I2(2) FULL


    select sum(decode(v,'a',1,0))/count(*) from i2;

    SELECT STATEMENT
    1: SORT AGGREGATE
    1: TABLE ACCESS MIKE.I2(1) FULL
    </pre>

    All 3 give the same, correct, result (Oracle has always provided many way to accomplish the same task). However, the first plan doesn't even show the base table, so clearly the plan is meaningless. The 2nd shows that the base table is accessed twice - one large volumes of data, this could be bad. The 3rd only access that table once. Each can be optimal, depending on the amount of data in the tables, is the same table used in both parts, where clauses using indexes, and on and on. My point is that using in-line views by default, when not required for accuracy or performance, is overkill.

    Also, while the simple queries in this thread will execute with the same plan regardless of the in-line view, more complex queries may not. Yes
    select * from (select * from t) where c=1
    will pass the where clause into the view, and an index will be used. However, when another table is joined to that in-line view, where clauses may not be passed in - it depends on execution plan. By the same token, using an in-line view to force something to execute first, before the join, may be optimal.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  11. #11
    Join Date
    Aug 2001
    Posts
    66
    > I use in-line views extensively

    Good for you because they rock. If analytics didn't exist I'd have (er...metaphorically speaking at least) an 'in-line' view t-shirt instead.

    > but not as a catch all way to avoid some other working syntax

    You exaggerate - no-one is using it here as a catch-all, both syntaxes work and are valid (this is not WHEN OTHERS THEN NULL). I thought we were discussing which syntax was better - the original example is IMHO shorter, neater and easier to maintain than your version and is (as far as I can determine) equivalent both logically and in terms of performance.

    > the problem is that some other person may modify the sql (with the in-line view) and decide to rewrite it, moving the where clause inside the in-line view,

    Yes I suppose they might put it there (if they didn't know that the WHERE clause goes after the FROM clause and not before it). But wouldn't the same argument apply to someone changing one instance of the duplicated SUM () expression in your query and not the other? Maintenance issues related to redundancy are as critical as those related to complexity and this is very marginally more complex at worst. In any case the fact that it is syntactically incorrect would prevent them from making the change you suggest in your example (I took the liberty of moving the WHERE clause to its more traditional place)...
    Code:
    Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> SELECT employee_id,
      2        (SUM (get_worked_hours (
      3           TO_DATE ('1-nov-2002'),
      4           TO_DATE ('15-nov-2002')))) workedhours
      5  FROM   timesheet
      6  WHERE  workedhours <> 40
      7  GROUP BY employee_id;
    WHERE  workedhours <> 40
           *
    ERROR at line 6:
    ORA-00904: "WORKEDHOURS": invalid identifier
    
    SQL>
    > To the contrary, the having clause makes it very clear what the code is doing.

    I think if you read my post again you'll see I wasn't criticising the HAVING clause per se (it's lovely) - I was saying that the implicit and unnecessary duplication of logic in the HAVING clause is less readable and maintainable - we can discuss GROUP BY too if you like ;-).

    > so clearly the plan is meaningless

    I think you are trying to suggest that because you know of a case where the plan is incomplete that explain plan is in general unreliable and should not be trusted - and hence this is circumstantial evidence that the two queries may not be executed identically (and perhaps that in-line views are mischievous and evil?) whereas in reality your example is a known limitation of explain plan specific to scalar subqueries (*NOT* in-line views and really they are like "apples and toaster ovens") and hence does not I think apply here. Out of interest (let's pretend someone is still reading this) this un-related problem is fixed in 10g, e.g.
    Code:
    Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> SET AUTOTRACE ON EXPLAIN;
    SQL> SELECT (SELECT COUNT(*)
      2          FROM   emp
      3          WHERE  1 = 1) /
      4         (SELECT COUNT(*)
      5          FROM   emp) n
      6  FROM    dual;
    
             N
    ----------
             1
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)
       1    0   SORT (AGGREGATE)
       2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=2 Card=15)
       3    0   SORT (AGGREGATE)
       4    3     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=2 Card=15)
       5    0   FAST DUAL (Cost=2 Card=1)
    
    SQL>
    In any case clearly you do not believe the explain plan - would autotrace data be sufficient?
    Code:
    Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> -- following from previous examples
    SQL> TRUNCATE TABLE timesheet;
    
    Table truncated.
    
    SQL> CREATE TABLE timesheet (
      2    employee_id NUMBER,
      3    start_date DATE,
      4    end_date DATE);
    
    Table created.
    
    SQL> INSERT INTO timesheet
      2  SELECT r, start_date, start_date + DBMS_RANDOM.VALUE (1, 30) end_date
      3  FROM  (SELECT ROWNUM r, SYSDATE - DBMS_RANDOM.VALUE (1, 365) start_date
      4         FROM   all_objects);
    
    10006 rows created.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'TIMESHEET');
    
    PL/SQL procedure successfully completed.
    
    SQL> SET AUTOTRACE TRACEONLY;
    SQL> SELECT   employee_id,
      2           SUM (
      3             get_worked_hours (
      4               start_date, end_date)) workedhours
      5  FROM     timesheet
      6  GROUP BY employee_id
      7  HAVING   SUM (
      8             get_worked_hours (
      9               start_date, end_date)) <> 40;
    
    10006 rows selected.
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=501 Bytes=9519)
       1    0   FILTER
       2    1     SORT (GROUP BY) (Cost=22 Card=501 Bytes=9519)
       3    2       TABLE ACCESS (FULL) OF 'TIMESHEET' (TABLE) (Cost=18 Card=10006 Bytes=190114)
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
             76  consistent gets
              0  physical reads
              0  redo size
         287376  bytes sent via SQL*Net to client
           4946  bytes received via SQL*Net from client
            669  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
          10006  rows processed
    
    SQL> SELECT employee_id, workedhours
      2  FROM  (SELECT employee_id,
      3                SUM (
      4                  get_worked_hours (
      5                    start_date, end_date)) workedhours
      6         FROM     timesheet
      7         GROUP BY employee_id)
      8  WHERE  workedhours <> 40;
    
    10006 rows selected.
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=501 Bytes=9519)
       1    0   FILTER
       2    1     SORT (GROUP BY) (Cost=22 Card=501 Bytes=9519)
       3    2       TABLE ACCESS (FULL) OF 'TIMESHEET' (TABLE) (Cost=18 Card=10006 Bytes=190114)
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
             76  consistent gets
              0  physical reads
              0  redo size
         287376  bytes sent via SQL*Net to client
           4946  bytes received via SQL*Net from client
            669  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
          10006  rows processed
    
    SQL>
    > using an in-line view to force something to execute first

    Why do you keep implying that simply placing something in an in-line view categorically forces it to be executed first? This is in general NOT the case - in my experience the optimizer will typically merge the view (as in this case) unless prevented by specific (albeit perhaps not very well documented) restrictions. In this example the view is clearly merged and removes the duplication of logic.

    Yes I agree that totally unnecessary in-line views (like pretty much any construct) are overkill. Yes the use of in-line views can affect the options open to the optimizer, as can many things. However this in-line view has a specific point - namely to reduce code redundancy and hence improve readability and maintainability and clearly does not affect the ability of the optimizer to optimise the query hence I fail to see why THIS in-line view is overkill and why it requires you to demand why 'the hell' it is used and imply that it is not the 'correct' answer.
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  12. #12
    Join Date
    Apr 2004
    Posts
    246
    > shorter, neater and easier to maintain than your version
    yes, mine is about 20 characters longer, but neater and easier to maintain are matter of opinion. IMHO, HAVING is neater and easier than an in-line with a where clause as a method of avoiding a having clause.

    >ORA-00904: "WORKEDHOURS": invalid identifier
    try to give me a little credit would you. if I was a total idiot, would we still be having this conversation. somebody might, while modifying the query in the future, decide to rewrite it. they would obviously get that error (I'm not stupid), but then they might try to force a work around, and end up doing something wrong. my point is, still is and always will be, that the having clause very explicitly states it purpose by the fact of the keyword "HAVING", thereby making it "clearer", and less likely to misunderstood by someone else.

    >my experience the optimizer will typically merge the view
    Not in my experience. In fact, as I stated, I often use in-line views because they can, may, and sometimes will be, resolved first, which can improve performance. They are not always merged. As I'm sure you know (you've proven that you read documentation, and are aware of alternate syntax offered), nothing in oracle is "always". For example, given two similar, but different, queries, one may use a merge, the other a nested loop.

    >In this example the view is clearly merged and removes the duplication of logic.
    Yes, in this "simple" example. For something this simple, Oracle will always push the predicates into the view. For more complex sql, it doesn't "always" - that's why hints exist (so we can tell it to push predicates).

    > imply that it is not the 'correct' answer.
    I never said wrong, and, yes, you can choose it to imply whatever you want.

    > Yes I agree that totally unnecessary in-line views (like pretty much any construct) are overkill
    That has been my point all along. I still believe (opinion here, so we'll never agree, as neither of us is about sway from his own personal programming guidelines) that the HAVING is clearer and easier to maintain (in this example) than the in-line view.

    Another example of "clearer and easier to maintain". Consider a positional order by:
    select col1, col2/col3 col2_pct, col5, col9
    from....
    where....
    order by 1, 2

    It is the shortest way to write this (you are the one implying that shorter is better, I am implying that redunadent, not longer, is bad). However, "order by col1, col2/col3" will prevent errors when someone decides to add another column to the beginning of the select list without checking the order by (as would "order by 1, col2_pct" in higher versions, but I have clients still using v7, so I don't assume that the syntax works for everyone). Also, an in-line view, with the order by on the outside query, would help to prevent these similar errors when some other programmer makes a change without considering the order by.

    So, unless I know (as in, I've proven via test cases) that some computation in the HAVING is truely causing performace problems (and yes, wasted resources, not just elapsed time, are problems), I prefer the having clause. After all, maybe the function is deterministic, maybe it returns values from an associative array, or something else very efficient. Again, my preference - just like never using a goto to break out of a loop (it works, it can be shorter and clearer, but I still don't use it, as I consider it to be a poor programming practice).
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by padderz
    Out of interest (let's pretend someone is still reading this) ...
    I'm still reading it!

  14. #14
    Join Date
    Oct 2003
    Posts
    51
    It was me who posted this first. I am very much reading it.
    Sampath Gowri
    sampath gowri

  15. #15
    Join Date
    Jul 2003
    Posts
    2,296
    fascinating!
    let the battle continue
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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