FAQ
I've got a query in ANSI join format that returns all employees along with
the required training courses and the ethics training. If they've completed
a course then there will be a completed date in the training_survey table.
If not, then the completed date will be blank, indicating that that employee
needs to take that course. I have an ANSI join query that returns one row
per employee per course, whether they've completed the course during the
fiscal year or not.

The ANSI join looks like:

select p.last_name, p.first_name,s.title, t.created as completed
from persons p
join required_courses s on 1=1
left join training_survey t on s.ap_code = t.survey_app and t.user_id = p.id
and trunc(t.created) >= to_date('10/01/2009', 'MM/DD/YYYY')
and trunc(t.created) <= to_date('09/30/2010', 'MM/DD/YYYY')
where s.type = 'survey'
union
select p.last_name, p.first_name, 'Ethics' AS title, created as completed
from persons p
left join ethics_training u on p.id = u.userid
and created between to_date('10/01/2009', 'MM/DD/YYYY')
and to_date('09/30/2010', 'MM/DD/YYYY')
and checkthis = 1
where 1=1
order by last_name, first_name;

My question is, what would with look like with the Oracle join (+)? I've
tried several variations but it doesn't return the rows were there is no
completed date within the fiscal year.
I want the Cartesian product of employees and courses. I can do that. The
problem is in translating

left join training_survey t on s.ap_code = t.survey_app and t.user_id = p.id
and trunc(t.created) >= to_date('10/01/2009', 'MM/DD/YYYY')
and trunc(t.created) <= to_date('09/30/2010', 'MM/DD/YYYY')

So that it pulls in a created date when one is available but, when one is
not available, it still leaves the row in the result set.

It's not a problem; I'd just like to learn how to do that.

Search Discussions

  • Paul Drake at May 4, 2011 at 1:19 pm
    Thomas,

    Why would anyone ever apply a TRUNC ( ) function to a date column in a where
    clause?
    Is there no possibility that one might want to index that column at some
    point?
    Are you that fond of having to create function based indexes just because
    devs can't write decent SQL statements?

    Paul
    On Wed, May 4, 2011 at 9:10 AM, Thomas Day wrote:

    I've got a query in ANSI join format that returns all employees along with
    the required training courses and the ethics training. If they've completed
    a course then there will be a completed date in the training_survey table.
    If not, then the completed date will be blank, indicating that that employee
    needs to take that course. I have an ANSI join query that returns one row
    per employee per course, whether they've completed the course during the
    fiscal year or not.

    The ANSI join looks like:

    select p.last_name, p.first_name,s.title, t.created as completed
    from persons p
    join required_courses s on 1=1
    left join training_survey t on s.ap_code = t.survey_app and t.user_id =
    p.id
    and trunc(t.created) >= to_date('10/01/2009', 'MM/DD/YYYY')
    and trunc(t.created) <= to_date('09/30/2010', 'MM/DD/YYYY')
    where s.type = 'survey'
    union
    select p.last_name, p.first_name, 'Ethics' AS title, created as completed
    from persons p
    left join ethics_training u on p.id = u.userid
    and created between to_date('10/01/2009', 'MM/DD/YYYY')
    and to_date('09/30/2010', 'MM/DD/YYYY')
    and checkthis = 1
    where 1=1
    order by last_name, first_name;


    My question is, what would with look like with the Oracle join (+)? I've
    tried several variations but it doesn't return the rows were there is no
    completed date within the fiscal year.
    I want the Cartesian product of employees and courses. I can do that. The
    problem is in translating

    left join training_survey t on s.ap_code = t.survey_app and t.user_id =
    p.id
    and trunc(t.created) >= to_date('10/01/2009', 'MM/DD/YYYY')
    and trunc(t.created) <= to_date('09/30/2010', 'MM/DD/YYYY')

    So that it pulls in a created date when one is available but, when one is
    not available, it still leaves the row in the result set.

    It's not a problem; I'd just like to learn how to do that.
    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at May 4, 2011 at 1:29 pm
    Based on the where 1=1 and lack if between I'd expect this to be machine
    written

    On 4 May 2011 14:23, "Paul Drake" wrote:

    Thomas,

    Why would anyone ever apply a TRUNC ( ) function to a date column in a where
    clause?
    Is there no possibility that one might want to index that column at some
    point?
    Are you that fond of having to create function based indexes just because
    devs can't write decent SQL statements?

    Paul
    On Wed, May 4, 2011 at 9:10 AM, Thomas Day wrote:

    I've got a query in A...
    --
    http://www.freelists.org/webpage/oracle-l
  • TESTAJ3_at_nationwide.com at May 4, 2011 at 1:31 pm
    to truncate the time component?

    Joe

    Joe Testa, Oracle Certified Professional
    Senior Engineering & Administration Lead
    (Work) 614-677-1668
    (Cell) 614-312-6715

    From:
    Paul Drake
    To:
    tomdaytwo_at_gmail.com
    Cc:
    oracle-l
    Date:
    05/04/2011 09:23 AM
    Subject:
    Re: Just as a learning exercise
    Sent by:
    oracle-l-bounce_at_freelists.org

    Thomas,

    Why would anyone ever apply a TRUNC ( ) function to a date column in a
    where clause?
    Is there no possibility that one might want to index that column at some
    point?
    Are you that fond of having to create function based indexes just because
    devs can't write decent SQL statements?

    Paul

    On Wed, May 4, 2011 at 9:10 AM, Thomas Day wrote:
    I've got a query in ANSI join format that returns all employees along with
    the required training courses and the ethics training. If they've
    completed a course then there will be a completed date in the
    training_survey table. If not, then the completed date will be blank,
    indicating that that employee needs to take that course. I have an ANSI
    join query that returns one row per employee per course, whether they've
    completed the course during the fiscal year or not.

    The ANSI join looks like:

    select p.last_name, p.first_name,s.title, t.created as completed
    from persons p
    join required_courses s on 1=1
    left join training_survey t on s.ap_code = t.survey_app and t.user_id =
    p.id
    and trunc(t.created) >= to_date('10/01/2009', 'MM/DD/YYYY')
    and trunc(t.created) <= to_date('09/30/2010', 'MM/DD/YYYY')
    where s.type = 'survey'
    union
    select p.last_name, p.first_name, 'Ethics' AS title, created as completed
    from persons p
    left join ethics_training u on p.id = u.userid
    and created between to_date('10/01/2009', 'MM/DD/YYYY')
    and to_date('09/30/2010', 'MM/DD/YYYY')
    and checkthis = 1
    where 1=1
    order by last_name, first_name;

    My question is, what would with look like with the Oracle join (+)? I've
    tried several variations but it doesn't return the rows were there is no
    completed date within the fiscal year.
    I want the Cartesian product of employees and courses. I can do that.
    The problem is in translating

    left join training_survey t on s.ap_code = t.survey_app and t.user_id =
    p.id
    and trunc(t.created) >= to_date('10/01/2009', 'MM/DD/YYYY')
    and trunc(t.created) <= to_date('09/30/2010', 'MM/DD/YYYY')

    So that it pulls in a created date when one is available but, when one is
    not available, it still leaves the row in the result set.

    It's not a problem; I'd just like to learn how to do that.
  • Niall Litchfield at May 4, 2011 at 1:37 pm
    In the example given the logic is the same with and without the trunc the
    execution plan likely won't be if there is an idex on the column and the
    query is fairly selective (which I doubt) .

    On 4 May 2011 14:33, wrote:

    to truncate the time component?

    Joe

    Joe Testa, Oracle Certified Professional
    Senior Engineering & Administration Lead
    (Work) 614-677-1668
    (Cell) 614-312-6715

    From:

    Paul Drake
    To: tomdaytwo_at_gmail.com Cc: oracle-l Date: 05/04/2011
    09:23 AM Subject: Re: Just as a learning exercise Sent by:
    oracle-l-bounce_at_freelists.org

    Thomas,

    Why would anyone ever apply a TRUNC ( ) function to a date column in a where
    clause?
    Is...
  • Rich Jesse at May 4, 2011 at 1:43 pm

    Joe writes:

    to truncate the time component?
    ...which not only unnecessary in the comparison, but a performance drain.
    Also, although it defaults to midnight, I'd add the time component in the
    TO_DATE calls.

    As far as the "1=1", when I started my current job, consultants used that in
    every statement. A distant hop-soaked memory thinks it was a hack to
    attempt to remind the optimizer that the first table specified in the FROM
    was the driving table (or something like that?) in pre-9i days.
    From:
    Paul Drake ...
    Why would anyone ever apply a TRUNC ( ) function to a date column in a
    where clause?
    Rich
  • Amaral, Rui at May 4, 2011 at 1:50 pm
    "1=1" - I seem to remember the same thing (tho not hop-soaked) and eventually got phased out as being not helpful at all.

    Rui Amaral
    Database Administrator
    ITS - SSG

    TD Bank Financial Group
    220 Bay St., 11th Floor
    Toronto, ON, CA, M5K1A2
    (bb) (647) 204-9106


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Rich Jesse
    Sent: Wednesday, May 04, 2011 9:44 AM
    To: oracle-l@freelists.org
    Subject: Re: Just as a learning exercise

    Joe writes:
    to truncate the time component?
    ...which not only unnecessary in the comparison, but a performance drain.
    Also, although it defaults to midnight, I'd add the time component in the TO_DATE calls.

    As far as the "1=1", when I started my current job, consultants used that in every statement. A distant hop-soaked memory thinks it was a hack to attempt to remind the optimizer that the first table specified in the FROM was the driving table (or something like that?) in pre-9i days.
    From:
    Paul Drake ...
    Why would anyone ever apply a TRUNC ( ) function to a date column in a
    where clause?
    Rich

    --
    http://www.freelists.org/webpage/oracle-l

    NOTICE: Confidential message which may be privileged. Unauthorized use/disclosure prohibited. If received in error, please go to www.td.com/legal for instructions.
    AVIS : Message confidentiel dont le contenu peut être privilégié. Utilisation/divulgation interdites sans permission. Si reçu par erreur, prière d'aller au www.td.com/francais/avis_juridique pour des instructions.
    --
    http://www.freelists.org/webpage/oracle-l
  • Ric Van Dyke at May 4, 2011 at 2:12 pm
    I'm pretty sure that 1=1 has never improved performance of a query; it's like adding "TURE" to a statement. Certainly since about 9 or so the optimizer removes it as a predicate, notice the 1=1 predicate isn't applied at all in the plan below (yes I truncated the plan so as to fit better):

    SQL> set autotrace traceonly explain
    SQL> select * from emp where 1=1 and deptno = 10;

    Execution Plan

    Plan hash value: 919097248
    Id | Operation | Name | Rows |
    ------------------------------------------------------------
    0 | SELECT STATEMENT | | 4 |
    1 | TABLE ACCESS BY INDEX ROWID| EMP | 4 |
    * 2 | INDEX RANGE SCAN | EMP_DEPT_IDX | 4 |
    ------------------------------------------------------------

    Predicate Information (identified by operation id):

    2 - access("DEPTNO"=10)

    As to the query with an outer join in Oracle syntax:

    Think of the + as a grave marker, and think that you need to put the grave marker on the side of the query where you need the ghost records from. So which table will there not be a match on that you want records from? That is the side you put the + on.

    I hope that helps.

    Ric Van Dyke
    Hotsos Enterprises
    Cell 248-705-0624



    The 10th Hotsos Symposium
    4-8 March 2012 Start making plans now!



    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Amaral, Rui
    Sent: Wednesday, May 04, 2011 9:50 AM
    To: 'rjoralist2_at_society.servebeer.com'; oracle-l_at_freelists.org
    Subject: RE: Just as a learning exercise

    "1=1" - I seem to remember the same thing (tho not hop-soaked) and eventually got phased out as being not helpful at all.

    Rui Amaral
    Database Administrator
    ITS - SSG

    TD Bank Financial Group
    220 Bay St., 11th Floor
    Toronto, ON, CA, M5K1A2
    (bb) (647) 204-9106


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Rich Jesse
    Sent: Wednesday, May 04, 2011 9:44 AM
    To: oracle-l@freelists.org
    Subject: Re: Just as a learning exercise

    Joe writes:
    to truncate the time component?
    ...which not only unnecessary in the comparison, but a performance drain.
    Also, although it defaults to midnight, I'd add the time component in the TO_DATE calls.

    As far as the "1=1", when I started my current job, consultants used that in every statement. A distant hop-soaked memory thinks it was a hack to attempt to remind the optimizer that the first table specified in the FROM was the driving table (or something like that?) in pre-9i days.
    From:
    Paul Drake ...
    Why would anyone ever apply a TRUNC ( ) function to a date column in a
    where clause?
    Rich

    --
    http://www.freelists.org/webpage/oracle-l

    NOTICE: Confidential message which may be privileged. Unauthorized use/disclosure prohibited. If received in error, please go to www.td.com/legal for instructions.
    AVIS : Message confidentiel dont le contenu peut être privilégié. Utilisation/divulgation interdites sans permission. Si reçu par erreur, prière d'aller au www.td.com/francais/avis_juridique pour des instructions.
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Michael McMullen at May 4, 2011 at 2:33 pm
    We use 1=1 a lot for on the fly report generation with different conditions
    that the user can specify, this way "and" statements can just be tacked on
    at the end and then the query run, it's all done via pl/sql.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Ric Van Dyke
    Sent: Wednesday, May 04, 2011 10:12 AM
    To: Rui.Amaral_at_tdsecurities.com; rjoralist2_at_society.servebeer.com;
    oracle-l@freelists.org
    Subject: RE: Just as a learning exercise

    I'm pretty sure that 1=1 has never improved performance of a query; it's
    like adding "TURE" to a statement. Certainly since about 9 or so the
    optimizer removes it as a predicate, notice the 1=1 predicate isn't applied
    at all in the plan below (yes I truncated the plan so as to fit better):

    --
    http://www.freelists.org/webpage/oracle-l
  • Ric Van Dyke at May 4, 2011 at 2:46 pm
    Roger that. That *IS* where it's useful as a place holder, but nothing
    more. The good old programming "sub", like writing a PL/SQL procedure
    with just

    BEGIN

    NULL;

    END;

    As the code, it's there and a call to the procedure will always work,
    because it does nothing! And it runs really fast too!

    But would adding a "NULL" to the top of every PL/SQL block make it run
    faster? I think we can all agree that it wouldn't, and logically that is
    about the same thing that adding 1=1 to your query.

    Ric Van Dyke
    Hotsos Enterprises
    Cell 248-705-0624



    The 10th Hotsos Symposium
    4-8 March 2012 Start making plans now!



    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Michael McMullen
    Sent: Wednesday, May 04, 2011 10:34 AM
    To: oracle-l@freelists.org
    Subject: RE: Just as a learning exercise

    We use 1=1 a lot for on the fly report generation with different
    conditions
    that the user can specify, this way "and" statements can just be tacked
    on
    at the end and then the query run, it's all done via pl/sql.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org

    On Behalf Of Ric Van Dyke
    Sent: Wednesday, May 04, 2011 10:12 AM
    To: Rui.Amaral_at_tdsecurities.com; rjoralist2_at_society.servebeer.com;
    oracle-l@freelists.org
    Subject: RE: Just as a learning exercise

    I'm pretty sure that 1=1 has never improved performance of a query; it's
    like adding "TURE" to a statement. Certainly since about 9 or so the
    optimizer removes it as a predicate, notice the 1=1 predicate isn't
    applied
    at all in the plan below (yes I truncated the plan so as to fit better):

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Vit Spinka at May 4, 2011 at 7:40 pm
    Actually, 1=1 can do harm. If it is used in a join condition in outer
    join, the CBO cannot rewrite to (+) syntax and this limits its choice of
    join paths. This is not limited to "1=1", other conditions where the
    second table is not involved are affected as well (ie. when there is
    nothing where to put the "(+)").

    I hit this on 10.2, and the disastrous results of 1=1 manifest even in
    11.2.0.1.
    (See http://vitspinka.blogspot.com/2009/07/latteral-view-quirk.html for
    an example.)

    Vit

    Dne 4.5.2011 16:46, Ric Van Dyke napsal(a):
    Roger that. That *IS* where it's useful as a place holder, but nothing
    more. The good old programming "sub", like writing a PL/SQL procedure
    with just

    BEGIN
    NULL;
    END;
    As the code, it's there and a call to the procedure will always work,
    because it does nothing! And it runs really fast too!

    But would adding a "NULL" to the top of every PL/SQL block make it run
    faster? I think we can all agree that it wouldn't, and logically that is
    about the same thing that adding 1=1 to your query.

    -----------------------
    Ric Van Dyke
    Hotsos Enterprises
    Cell 248-705-0624
    -----------------------

    The 10th Hotsos Symposium
    4-8 March 2012 Start making plans now!

    --
    http://www.freelists.org/webpage/oracle-l
  • Rumpi Gravenstein at May 4, 2011 at 2:37 pm
    Isn't the 1=1 there because someone doesn't want to use the proper ANSI
    syntax?

    I would think

    SELECT P.last_name, P.first_name, s.title, T.created AS completed
    FROM persons P
    JOIN required_courses s
    ON 1 = 1
    ...

    is better written as

    SELECT P.last_name, P.first_name, s.title, T.created AS completed
    FROM persons P
    CROSS JOIN required_courses s
    On Wed, May 4, 2011 at 10:12 AM, Ric Van Dyke wrote:

    I'm pretty sure that 1=1 has never improved performance of a query; it's
    like adding "TURE" to a statement. Certainly since about 9 or so the
    optimizer removes it as a predicate, notice the 1=1 predicate isn't applied
    at all in the plan below (yes I truncated the plan so as to fit better):

    SQL> set autotrace traceonly explain
    SQL> select * from emp where 1=1 and deptno = 10;

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 919097248

    ------------------------------------------------------------
    Id | Operation | Name | Rows |
    ------------------------------------------------------------
    0 | SELECT STATEMENT | | 4 |
    1 | TABLE ACCESS BY INDEX ROWID| EMP | 4 |
    * 2 | INDEX RANGE SCAN | EMP_DEPT_IDX | 4 |
    ------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    2 - access("DEPTNO"=10)


    As to the query with an outer join in Oracle syntax:

    Think of the + as a grave marker, and think that you need to put the grave
    marker on the side of the query where you need the ghost records from. So
    which table will there not be a match on that you want records from? That
    is the side you put the + on.

    I hope that helps.

    -----------------------
    Ric Van Dyke
    Hotsos Enterprises
    Cell 248-705-0624
    -----------------------

    The 10th Hotsos Symposium
    4-8 March 2012 Start making plans now!


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Amaral, Rui
    Sent: Wednesday, May 04, 2011 9:50 AM
    To: 'rjoralist2_at_society.servebeer.com'; oracle-l@freelists.org
    Subject: RE: Just as a learning exercise

    "1=1" - I seem to remember the same thing (tho not hop-soaked) and
    eventually got phased out as being not helpful at all.


    Rui Amaral
    Database Administrator
    ITS - SSG
    TD Bank Financial Group
    220 Bay St., 11th Floor
    Toronto, ON, CA, M5K1A2
    (bb) (647) 204-9106


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Rich Jesse
    Sent: Wednesday, May 04, 2011 9:44 AM
    To: oracle-l@freelists.org
    Subject: Re: Just as a learning exercise

    Joe writes:
    to truncate the time component?
    ...which not only unnecessary in the comparison, but a performance drain.
    Also, although it defaults to midnight, I'd add the time component in the
    TO_DATE calls.

    As far as the "1=1", when I started my current job, consultants used that
    in every statement. A distant hop-soaked memory thinks it was a hack to
    attempt to remind the optimizer that the first table specified in the FROM
    was the driving table (or something like that?) in pre-9i days.
    From:
    Paul Drake ...
    Why would anyone ever apply a TRUNC ( ) function to a date column in a
    where clause?
    Rich

    --
    http://www.freelists.org/webpage/oracle-l



    NOTICE: Confidential message which may be privileged. Unauthorized
    use/disclosure prohibited. If received in error, please go to
    www.td.com/legal for instructions.
    AVIS : Message confidentiel dont le contenu peut être privilégié.
    Utilisation/divulgation interdites sans permission. Si reçu par erreur,
    prière d'aller au www.td.com/francais/avis_juridique pour des
    instructions.
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l

    --
    Rumpi Gravenstein

    --
    http://www.freelists.org/webpage/oracle-l
  • Dominic.Brooks_at_barclayscapital.com at May 4, 2011 at 2:50 pm
    Sent previously - possible unsuccessfully due to format? Attempt 2.

    BTW, some example data would have been nice.

    1=1 can often be found in "frameworks" generating dynamic sql to save having logic to determine whether your predicate is "WHERE ." or "AND ."

    In the top half of the query at least, I believe Thomas has used 1=1 because the JOIN clause requires an ON, therefore a dummy 1=1 is required to force the cartesian join. Using CROSS JOIN would be clearer perhaps.

    Anyway, ignoring distractions :)

    ANSI syntax makes this sort of thing easy because it distinguishes between join predicates and filter predicates.
    There is not really such a distinction with the traditional Oracle syntax.

    So, for example whereas with ANSI syntax you can conditionally join two tables via the ON clause
    e.g.
    from    inner_table
    left outer join

    outer_table
    on      outer_table.join_col       = inner_table.join_col
    and     inner_table.some_inner_col = some_condition;

    With Oracle syntax, a conditional outer join like this could be implemented using a DECODE
    e.g.
    from    inner_table, outer_table
    where  decode(inner_table.some_inner_col,

    some_condition,
    inner_table.join_col,
    NULL) = outer_table.join_col (+);

    Having said all this. that is not really what you're trying to do here.

    For your specific example, Thomas, you're looking to filter t and then outer join to two tables (p to s and t to s).
    To this in Oracle you normally use an inline view or variation thereof (subquery factoring / with).

    Just looking at the top half to keep things "simple", with my version of the data..
    ANSI might look like this:
    WITH persons

    (id, first_name, last_name)
    AS
    (SELECT 1,'James','Jamieson' FROM dual

    UNION ALL

    SELECT 2,'Jeff','Jefferson' FROM dual),    required_courses
    (ap_code, title, type)
    AS
    (SELECT 1,'Fraud Prevention' ,'Training' FROM dual

    UNION ALL

    SELECT 2,'Employee Happiness','Survey'   FROM dual
    UNION ALL

    SELECT 3,'Some other survey','Survey'   FROM dual),    training_survey
    (user_id, survey_app, created)
    AS
    (SELECT 1,1,TO_DATE('01-MAY-2010 12:12','DD-MON-YYYY HH24:MI')

    FROM   DUAL

    UNION  ALL

    SELECT 1,2,TO_DATE('01-MAY-2011 09:38','DD-MON-YYYY HH24:MI')

    FROM   DUAL

    UNION  ALL

    SELECT 2,1,TO_DATE('01-JAN-2010 10:00','DD-MON-YYYY HH24:MI')

    FROM   DUAL

    UNION  ALL

    SELECT 2,3,TO_DATE('01-FEB-2011 10:00','DD-MON-YYYY HH24:MI')

    FROM   DUAL)

    SELECT *

    FROM   persons p
    CROSS JOIN

    required_courses s
    LEFT JOIN

    training_survey t
    ON     s.ap_code   = t.survey_app
    AND    t.user_id   = p.id
    AND    t.created  >= TO_DATE('01-JAN-2011','DD-MON-YYYY')

    WHERE  s.type      = 'Survey';

    Traditional syntax like this:
    WITH ... same as above
    SELECT *

    FROM   (SELECT *
    FROM   persons          p
    ,      required_courses s
    WHERE  s.type = 'Survey') ps,      (SELECT t.*
    FROM   training_survey  t
    WHERE  t.created >= TO_DATE('01-JAN-2011','DD-MON-YYYY')) t

    WHERE  t.survey_app (+) = ps.ap_code
    AND    t.user_id    (+) = ps.id;

    This is one way, probably not the only way, maybe not the best way.
    Hope it helps.

    Cheers,
    Dominic

    This e-mail may contain information that is confidential, privileged or otherwise protected from disclosure. If you are not an intended recipient of this e-mail, do not duplicate or redistribute it by any means. Please delete it and any attachments and notify the sender that you have received it in error. Unless specifically indicated, this e-mail is not an offer to buy or sell or a solicitation to buy or sell any securities, investment products or other financial product or service, an official confirmation of any transaction, or an official statement of Barclays. Any views or opinions presented are solely those of the author and do not necessarily represent those of Barclays. This e-mail is subject to terms available at the following link: www.barcap.com/emaildisclaimer. By messaging with Barclays you consent to the foregoing. Barclays Capital is the investment banking division of Barclays Bank PLC, a company registered in England (number 1026167) with its registered offic
    e at 1 Churchill Place, London, E14 5HP. This email may relate to or be sent from other members of the Barclays Group.
  • Grzegorz Goryszewski at May 4, 2011 at 3:18 pm
    Hi,
    I'm on 4-node Linux RAC 10.2.0.3, recently I've notice that only undo
    segments are showed in V$GCSPFMASTER_INFO
    I know that because object_id is like 4294950913 and so .
    Thats strange because I'm expecting other objects to be remastered,
    there is :
    _gc_affinity_time 10
    _gc_affinity_limit 50

    and I can see :
    DRM(29) ignoring dissolve of 18504741
    * kjdrchkdrm: found an RM request in the request queue
    Dissolve pkey 18504742
    DRM(29) ignoring dissolve of 18504742
    * kjdrchkdrm: found an RM request in the request queue
    Dissolve pkey 18504743
    DRM(29) ignoring dissolve of 18504743
    * kjdrchkdrm: found an RM request in the request queue
    Dissolve pkey 18504744
    in lmd0 trace .
    So how can I check DRM is on or off ?:)
    Regards.
    GregG

    Zapytaj wrozke!
    Sprawdz >> http://linkint.pl/f29a2
  • K Gopalakrishnan at May 4, 2011 at 3:30 pm
    Greg,
    V$GCSPFMASTER_INFO is not very reliable to monitor DRM till 11.1.
    Check the bug 5649377.

    -Gopal
    On Wed, May 4, 2011 at 10:18 AM, Grzegorz Goryszewski wrote:

    Hi,
    I'm on 4-node Linux RAC 10.2.0.3, recently I've notice that only undo
    segments are showed in V$GCSPFMASTER_INFO
    I know that because object_id is like 4294950913 and so .
    Thats strange because I'm expecting other objects to be remastered,
    there is :
    _gc_affinity_time 10
    _gc_affinity_limit 50

    and I can see :
    DRM(29) ignoring dissolve of 18504741
    * kjdrchkdrm: found an RM request in the request queue
    Dissolve pkey 18504742
    DRM(29) ignoring dissolve of 18504742
    * kjdrchkdrm: found an RM request in the request queue
    Dissolve pkey 18504743
    DRM(29) ignoring dissolve of 18504743
    * kjdrchkdrm: found an RM request in the request queue
    Dissolve pkey 18504744


    in lmd0 trace .
    So how can I check DRM is on or off ?:)
    Regards.
    GregG

    -------------------------------------------------
    Zapytaj wrozke!
    Sprawdz >> http://linkint.pl/f29a2

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Grzegorz Goryszewski at May 4, 2011 at 3:34 pm

    On 2011-05-04 17:30, K Gopalakrishnan wrote:
    Greg,
    V$GCSPFMASTER_INFO is not very reliable to monitor DRM till 11.1.
    Check the bug 5649377.

    -Gopal
    Thank You, but in my case all rows are undo segments related, which is
    kind of strange :).
    Regards
    GregG

    Najlepsze oferty na mieszkania i domy!
    Odwiedz >> http://linkint.pl/f29a9
  • Anonymous at May 5, 2011 at 3:52 am
    If you use "oradebug lkdebug -m pkey" command to manually remaster the
    object, can you find it in v$gcspfmaster_info?

    --
    Kamus

    Visit my blog for more : http://www.dbform.com
    Join ACOUG: http://www.acoug.org

    On Wed, May 4, 2011 at 11:18 PM, Grzegorz Goryszewski
    wrote:
    Hi,
    I'm on 4-node Linux RAC 10.2.0.3, recently I've notice that only undo
    segments are showed in V$GCSPFMASTER_INFO
    I know that because object_id is like 4294950913 and so .
    Thats strange because I'm expecting other objects to be remastered,
    there is :
    _gc_affinity_time                             10
    _gc_affinity_limit                            50

    and I can see :
    DRM(29) ignoring dissolve of 18504741
    * kjdrchkdrm: found an RM request in the request queue
    Dissolve pkey 18504742
    DRM(29) ignoring dissolve of 18504742
    * kjdrchkdrm: found an RM request in the request queue
    Dissolve pkey 18504743
    DRM(29) ignoring dissolve of 18504743
    * kjdrchkdrm: found an RM request in the request queue
    Dissolve pkey 18504744


    in lmd0 trace .
    So how can I check DRM is on or off ?:)
    Regards.
    GregG

    -------------------------------------------------
    Zapytaj wrozke!
    Sprawdz >> http://linkint.pl/f29a2

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at May 4, 2011 at 11:28 pm

    On Wed, May 4, 2011 at 6:10 AM, Thomas Day wrote:
    select p.last_name, p.first_name,s.title, t.created as completed
    from persons p
    join required_courses s on 1=1
    left join training_survey t on s.ap_code = t.survey_app and t.user_id =
    p.id
    and trunc(t.created) >= to_date('10/01/2009', 'MM/DD/YYYY')
    and trunc(t.created) <= to_date('09/30/2010', 'MM/DD/YYYY')
    This bit is I realize not what you asked for, but it valuable enough
    I thought it worth pointing out.

    Converting the date portion to this makes is possible to use
    an index where one could not be used before.
    (should the index be of benefit)

    t.created between to_date('10/01/2009', 'MM/DD/YYYY')
    and to_date('09/30/2010', 'MM/DD/YYYY')

    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    Oracle Blog: http://jkstill.blogspot.com
    Home Page: http://jaredstill.com
  • Jared Still at May 5, 2011 at 3:04 pm

    On Wed, May 4, 2011 at 4:28 PM, Jared Still wrote:
    t.created between to_date('10/01/2009', 'MM/DD/YYYY')
    and to_date('09/30/2010', 'MM/DD/YYYY')
    Oops.

    That should be t.created between to_date('10/01/2009', 'MM/DD/YYYY')
    and to_date('10/01/2010', 'MM/DD/YYYY') - (1/86400)

    Eagle eyed Brendan Fleming caught that.

    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    Oracle Blog: http://jkstill.blogspot.com
    Home Page: http://jaredstill.com

Related Discussions

People

Translate

site design / logo © 2022 Grokbase