Loading ...
Sorry, an error occurred while loading the content.

How do I query a

Expand Messages
  • jason_c_ellis
    Select /*RULE*/ Distinct? I can get it to run just fine but can t seem to get the procedure to compile. Help?
    Message 1 of 14 , Feb 5, 2004
    • 0 Attachment
      Select /*RULE*/ Distinct? I can get it to run just fine but can't
      seem to get the procedure to compile. Help?
    • John Warner
      Going to need a little more information. How about showing us the entire proc? John Warner mailto:john@jwarner.com ... http://www.c1tracking.com/l.asp?cid=5511
      Message 2 of 14 , Feb 6, 2004
      • 0 Attachment
        Going to need a little more information. How about showing us the entire
        proc?

        John Warner
        mailto:john@...

        > -----Original Message-----
        > From: jason_c_ellis [mailto:jason_c_ellis@...]
        > Sent: Thursday, February 05, 2004 2:42 PM
        > To: SQLQueriesNoCode@yahoogroups.com
        > Subject: [SQLQueriesNoCode] How do I query a
        >
        >
        > Select /*RULE*/ Distinct? I can get it to run just fine but can't
        > seem to get the procedure to compile. Help?
        >
        >
        >
        >
        > ------------------------ Yahoo! Groups Sponsor
        > ---------------------~--> Buy Ink Cartridges or Refill Kits
        > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
        > Free s/h on orders $50 or more to the US & Canada.
        http://www.c1tracking.com/l.asp?cid=5511
        http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
        ---------------------------------------------------------------------~->


        Yahoo! Groups Links
      • jason_c_ellis
        I did get it to compile. My question is what exactly does the /*+RULE*/ do in a procedure? ... entire ... can t ... -~-
        Message 3 of 14 , Feb 6, 2004
        • 0 Attachment
          I did get it to compile. My question is what exactly does
          the /*+RULE*/ do in a procedure?


          --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@j...>
          wrote:
          > Going to need a little more information. How about showing us the
          entire
          > proc?
          >
          > John Warner
          > mailto:john@j...
          >
          > > -----Original Message-----
          > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
          > > Sent: Thursday, February 05, 2004 2:42 PM
          > > To: SQLQueriesNoCode@yahoogroups.com
          > > Subject: [SQLQueriesNoCode] How do I query a
          > >
          > >
          > > Select /*RULE*/ Distinct? I can get it to run just fine but
          can't
          > > seem to get the procedure to compile. Help?
          > >
          > >
          > >
          > >
          > > ------------------------ Yahoo! Groups Sponsor
          > > ---------------------~--> Buy Ink Cartridges or Refill Kits
          > > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
          > > Free s/h on orders $50 or more to the US & Canada.
          > http://www.c1tracking.com/l.asp?cid=5511
          > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
          > --------------------------------------------------------------------
          -~->
          >
          >
          > Yahoo! Groups Links
        • John Warner
          Nothing the way you are posting here /* comment */ is just that, a comment. John Warner mailto:john@jwarner.com ... http://www.c1tracking.com/l.asp?cid=5511
          Message 4 of 14 , Feb 6, 2004
          • 0 Attachment
            Nothing the way you are posting here
            /* comment */ is just that, a comment.

            John Warner
            mailto:john@...

            > -----Original Message-----
            > From: jason_c_ellis [mailto:jason_c_ellis@...]
            > Sent: Friday, February 06, 2004 10:26 AM
            > To: SQLQueriesNoCode@yahoogroups.com
            > Subject: [SQLQueriesNoCode] Re: How do I query a
            >
            >
            > I did get it to compile. My question is what exactly does
            > the /*+RULE*/ do in a procedure?
            >
            >
            > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@j...>
            > wrote:
            > > Going to need a little more information. How about showing us the
            > entire
            > > proc?
            > >
            > > John Warner
            > > mailto:john@j...
            > >
            > > > -----Original Message-----
            > > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
            > > > Sent: Thursday, February 05, 2004 2:42 PM
            > > > To: SQLQueriesNoCode@yahoogroups.com
            > > > Subject: [SQLQueriesNoCode] How do I query a
            > > >
            > > >
            > > > Select /*RULE*/ Distinct? I can get it to run just fine but
            > can't
            > > > seem to get the procedure to compile. Help?
            > > >
            > > >
            > > >
            > > >
            > > > ------------------------ Yahoo! Groups Sponsor
            > > > ---------------------~--> Buy Ink Cartridges or Refill Kits
            > > > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
            > > > Free s/h on orders $50 or more to the US & Canada.
            > > http://www.c1tracking.com/l.asp?cid=5511
            > > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
            > > --------------------------------------------------------------------
            > -~->
            > >
            > >
            > > Yahoo! Groups Links
            >
            >
            >
            >
            > ------------------------ Yahoo! Groups Sponsor
            > ---------------------~--> Buy Ink Cartridges or Refill Kits
            > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
            > Free s/h on orders $50 or more to the US & Canada.
            http://www.c1tracking.com/l.asp?cid=5511
            http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
            ---------------------------------------------------------------------~->


            Yahoo! Groups Links
          • jason_c_ellis
            That is not true. Putting the + in there makes it NOT a comment. I put the /*+RULE*/ in there and it sped my query up by 8 minutes. So my question is...what
            Message 5 of 14 , Feb 6, 2004
            • 0 Attachment
              That is not true. Putting the '+' in there makes it NOT a comment.
              I put the /*+RULE*/ in there and it sped my query up by 8 minutes.

              So my question is...what does the +Rule do in SQL?


              --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@j...>
              wrote:
              > Nothing the way you are posting here
              > /* comment */ is just that, a comment.
              >
              > John Warner
              > mailto:john@j...
              >
              > > -----Original Message-----
              > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
              > > Sent: Friday, February 06, 2004 10:26 AM
              > > To: SQLQueriesNoCode@yahoogroups.com
              > > Subject: [SQLQueriesNoCode] Re: How do I query a
              > >
              > >
              > > I did get it to compile. My question is what exactly does
              > > the /*+RULE*/ do in a procedure?
              > >
              > >
              > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
              <john@j...>
              > > wrote:
              > > > Going to need a little more information. How about showing us
              the
              > > entire
              > > > proc?
              > > >
              > > > John Warner
              > > > mailto:john@j...
              > > >
              > > > > -----Original Message-----
              > > > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
              > > > > Sent: Thursday, February 05, 2004 2:42 PM
              > > > > To: SQLQueriesNoCode@yahoogroups.com
              > > > > Subject: [SQLQueriesNoCode] How do I query a
              > > > >
              > > > >
              > > > > Select /*RULE*/ Distinct? I can get it to run just fine but
              > > can't
              > > > > seem to get the procedure to compile. Help?
              > > > >
              > > > >
              > > > >
              > > > >
              > > > > ------------------------ Yahoo! Groups Sponsor
              > > > > ---------------------~--> Buy Ink Cartridges or Refill Kits
              > > > > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
              > > > > Free s/h on orders $50 or more to the US & Canada.
              > > > http://www.c1tracking.com/l.asp?cid=5511
              > > > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
              > > > ----------------------------------------------------------------
              ----
              > > -~->
              > > >
              > > >
              > > > Yahoo! Groups Links
              > >
              > >
              > >
              > >
              > > ------------------------ Yahoo! Groups Sponsor
              > > ---------------------~--> Buy Ink Cartridges or Refill Kits
              > > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
              > > Free s/h on orders $50 or more to the US & Canada.
              > http://www.c1tracking.com/l.asp?cid=5511
              > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
              > --------------------------------------------------------------------
              -~->
              >
              >
              > Yahoo! Groups Links
            • John Warner
              In T-SQL anything between /* */ is a comment. Check MSDN or books online. John Warner mailto:john@jwarner.com ... http://www.c1tracking.com/l.asp?cid=5511
              Message 6 of 14 , Feb 7, 2004
              • 0 Attachment
                In T-SQL anything between /* */ is a comment. Check MSDN or books
                online.

                John Warner
                mailto:john@...

                > -----Original Message-----
                > From: jason_c_ellis [mailto:jason_c_ellis@...]
                > Sent: Friday, February 06, 2004 3:04 PM
                > To: SQLQueriesNoCode@yahoogroups.com
                > Subject: [SQLQueriesNoCode] Re: How do I query a
                >
                >
                > That is not true. Putting the '+' in there makes it NOT a comment.
                > I put the /*+RULE*/ in there and it sped my query up by 8 minutes.
                >
                > So my question is...what does the +Rule do in SQL?
                >
                >
                > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@j...>
                > wrote:
                > > Nothing the way you are posting here
                > > /* comment */ is just that, a comment.
                > >
                > > John Warner
                > > mailto:john@j...
                > >
                > > > -----Original Message-----
                > > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
                > > > Sent: Friday, February 06, 2004 10:26 AM
                > > > To: SQLQueriesNoCode@yahoogroups.com
                > > > Subject: [SQLQueriesNoCode] Re: How do I query a
                > > >
                > > >
                > > > I did get it to compile. My question is what exactly does
                > > > the /*+RULE*/ do in a procedure?
                > > >
                > > >
                > > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
                > <john@j...>
                > > > wrote:
                > > > > Going to need a little more information. How about showing us
                > the
                > > > entire
                > > > > proc?
                > > > >
                > > > > John Warner
                > > > > mailto:john@j...
                > > > >
                > > > > > -----Original Message-----
                > > > > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
                > > > > > Sent: Thursday, February 05, 2004 2:42 PM
                > > > > > To: SQLQueriesNoCode@yahoogroups.com
                > > > > > Subject: [SQLQueriesNoCode] How do I query a
                > > > > >
                > > > > >
                > > > > > Select /*RULE*/ Distinct? I can get it to run just fine but
                > > > can't
                > > > > > seem to get the procedure to compile. Help?
                > > > > >
                > > > > >
                > > > > >
                > > > > >
                > > > > > ------------------------ Yahoo! Groups Sponsor
                > > > > > ---------------------~--> Buy Ink Cartridges or
                > Refill Kits for
                > > > > > your HP, Epson, Canon or Lexmark Printer at
                > MyInks.com. Free s/h
                > > > > > on orders $50 or more to the US & Canada.
                > > > > http://www.c1tracking.com/l.asp?cid=5511
                > > > > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                > > > > ----------------------------------------------------------------
                > ----
                > > > -~->
                > > > >
                > > > >
                > > > > Yahoo! Groups Links
                > > >
                > > >
                > > >
                > > >
                > > > ------------------------ Yahoo! Groups Sponsor
                > > > ---------------------~--> Buy Ink Cartridges or Refill Kits
                > > > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
                > > > Free s/h on orders $50 or more to the US & Canada.
                > > http://www.c1tracking.com/l.asp?cid=5511
                > > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                > > --------------------------------------------------------------------
                > -~->
                > >
                > >
                > > Yahoo! Groups Links
                >
                >
                >
                >
                > ------------------------ Yahoo! Groups Sponsor
                > ---------------------~--> Buy Ink Cartridges or Refill Kits
                > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
                > Free s/h on orders $50 or more to the US & Canada.
                http://www.c1tracking.com/l.asp?cid=5511
                http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                ---------------------------------------------------------------------~->


                Yahoo! Groups Links
              • Raed Al-Fararjeh
                Hi all, For the + Rule , using + between /* */ is to specify the index name to be used in your statement, And there are 2 methods in Cost Based and Rule Based
                Message 7 of 14 , Feb 8, 2004
                • 0 Attachment

                  Hi all,

                   

                  For the + Rule , using + between /* */ is to specify the index name to be used in your statement,

                   

                  And there are 2 methods in Cost Based and Rule Based Optimizer; at your statement you are using Rule Based Optimizer,

                   

                  Rule Based Optimizer is not supported in ORACLE 10g.

                   

                  For more information read about Rule Based and Cost Based optimizer.

                   

                  Best Regards

                   

                   

                   

                  -----Original Message-----
                  From: John Warner [mailto:john@...]
                  Sent:
                  Saturday, February 07, 2004 2:25 PM
                  To: SQLQueriesNoCode@yahoogroups.com
                  Subject: RE: [SQLQueriesNoCode] Re: How do I query a

                   

                  In T-SQL anything between /*  */  is a comment. Check MSDN or books
                  online.

                  John Warner
                  mailto:john@...

                  > -----Original Message-----
                  > From: jason_c_ellis [mailto:jason_c_ellis@...]
                  > Sent:
                  Friday, February 06, 2004 3:04 PM
                  > To: SQLQueriesNoCode@yahoogroups.com
                  > Subject: [SQLQueriesNoCode] Re: How do I query a
                  >
                  >
                  > That is not true.  Putting the '+' in there makes it NOT a comment. 
                  > I put the /*+RULE*/ in there and it sped my query up by 8 minutes.
                  >
                  > So my question is...what does the +Rule do in SQL?
                  >
                  >
                  > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@j...>
                  > wrote:
                  > > Nothing the way you are posting here
                  > > /* comment */ is just that, a comment.
                  > >
                  > > John Warner
                  > > mailto:john@j...
                  > >
                  > > > -----Original Message-----
                  > > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
                  > > > Sent:
                  Friday, February 06, 2004 10:26 AM
                  > > > To: SQLQueriesNoCode@yahoogroups.com
                  > > > Subject: [SQLQueriesNoCode] Re: How do I query a
                  > > >
                  > > >
                  > > > I did get it to compile.  My question is what exactly does
                  > > > the /*+RULE*/ do in a procedure?
                  > > >
                  > > >
                  > > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
                  > <john@j...>
                  > > > wrote:
                  > > > > Going to need a little more information. How about showing us
                  > the
                  > > > entire
                  > > > > proc?
                  > > > >
                  > > > > John Warner
                  > > > > mailto:john@j...
                  > > > >
                  > > > > > -----Original Message-----
                  > > > > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
                  > > > > > Sent:
                  Thursday, February 05, 2004 2:42 PM
                  > > > > > To: SQLQueriesNoCode@yahoogroups.com
                  > > > > > Subject: [SQLQueriesNoCode] How do I query a
                  > > > > >
                  > > > > >
                  > > > > > Select /*RULE*/ Distinct?  I can get it to run just fine but
                  > > > can't
                  > > > > > seem to get the procedure to compile.  Help?
                  > > > > >
                  > > > > >
                  > > > > >
                  > > > > >
                  > > > > > ------------------------ Yahoo! Groups Sponsor
                  > > > > > ---------------------~--> Buy Ink Cartridges or
                  > Refill Kits for
                  > > > > > your HP, Epson, Canon or Lexmark Printer at
                  > MyInks.com. Free s/h
                  > > > > > on orders $50 or more to the
                  US & Canada.
                  > > > > http://www.c1tracking.com/l.asp?cid=5511
                  > > > > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                  > > > > ----------------------------------------------------------------
                  > ----
                  > > > -~->
                  > > > >
                  > > > > 
                  > > > > Yahoo! Groups Links
                  > > >
                  > > >
                  > > >
                  > > >
                  > > > ------------------------ Yahoo! Groups Sponsor
                  > > > ---------------------~--> Buy Ink Cartridges or Refill Kits
                  > > > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
                  > > > Free s/h on orders $50 or more to the
                  US & Canada.
                  > > http://www.c1tracking.com/l.asp?cid=5511
                  > > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                  > > --------------------------------------------------------------------
                  > -~->
                  > >
                  > > 
                  > > Yahoo! Groups Links
                  >
                  >
                  >
                  >
                  > ------------------------ Yahoo! Groups Sponsor
                  > ---------------------~--> Buy Ink Cartridges or Refill Kits
                  > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
                  > Free s/h on orders $50 or more to the
                  US & Canada.
                  http://www.c1tracking.com/l.asp?cid=5511
                  http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                  ---------------------------------------------------------------------~->


                  Yahoo! Groups Links







                • John Warner
                  Thanks for the info, most interesting. As you can see my Oracle is very lacking... John Warner mailto:john@jwarner.com ... From: Raed Al-Fararjeh
                  Message 8 of 14 , Feb 9, 2004
                  • 0 Attachment
                    Message
                    Thanks for the info, most interesting. As you can see my Oracle is very lacking...
                     
                     

                    John Warner
                    mailto:john@...

                    -----Original Message-----
                    From: Raed Al-Fararjeh [mailto:rfararjeh@...]
                    Sent: Sunday, February 08, 2004 7:22 AM
                    To: SQLQueriesNoCode@yahoogroups.com
                    Subject: RE: [SQLQueriesNoCode] Re: How do I query a

                    Hi all,

                     

                    For the + Rule , using + between /* */ is to specify the index name to be used in your statement,

                     

                    And there are 2 methods in Cost Based and Rule Based Optimizer; at your statement you are using Rule Based Optimizer,

                     

                    Rule Based Optimizer is not supported in ORACLE 10g.

                     

                    For more information read about Rule Based and Cost Based optimizer.

                     

                    Best Regards

                     

                     

                     

                    -----Original Message-----
                    From: John Warner [mailto:john@...]
                    Sent:
                    Saturday, February 07, 2004 2:25 PM
                    To: SQLQueriesNoCode@yahoogroups.com
                    Subject: RE: [SQLQueriesNoCode] Re: How do I query a

                     

                    In T-SQL anything between /*  */  is a comment. Check MSDN or books
                    online.

                    John Warner
                    mailto:john@...

                    > -----Original Message-----
                    > From: jason_c_ellis [mailto:jason_c_ellis@...]
                    > Sent:
                    Friday, February 06, 2004 3:04 PM
                    > To: SQLQueriesNoCode@yahoogroups.com
                    > Subject: [SQLQueriesNoCode] Re: How do I query a
                    >
                    >
                    > That is not true.  Putting the '+' in there makes it NOT a comment. 
                    > I put the /*+RULE*/ in there and it sped my query up by 8 minutes.
                    >
                    > So my question is...what does the +Rule do in SQL?
                    >
                    >
                    > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@j...>
                    > wrote:
                    > > Nothing the way you are posting here
                    > > /* comment */ is just that, a comment.
                    > >
                    > > John Warner
                    > > mailto:john@j...
                    > >
                    > > > -----Original Message-----
                    > > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
                    > > > Sent:
                    Friday, February 06, 2004 10:26 AM
                    > > > To: SQLQueriesNoCode@yahoogroups.com
                    > > > Subject: [SQLQueriesNoCode] Re: How do I query a
                    > > >
                    > > >
                    > > > I did get it to compile.  My question is what exactly does
                    > > > the /*+RULE*/ do in a procedure?
                    > > >
                    > > >
                    > > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
                    > <john@j...>
                    > > > wrote:
                    > > > > Going to need a little more information. How about showing us
                    > the
                    > > > entire
                    > > > > proc?
                    > > > >
                    > > > > John Warner
                    > > > > mailto:john@j...
                    > > > >
                    > > > > > -----Original Message-----
                    > > > > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
                    > > > > > Sent:
                    Thursday, February 05, 2004 2:42 PM
                    > > > > > To: SQLQueriesNoCode@yahoogroups.com
                    > > > > > Subject: [SQLQueriesNoCode] How do I query a
                    > > > > >
                    > > > > >
                    > > > > > Select /*RULE*/ Distinct?  I can get it to run just fine but
                    > > > can't
                    > > > > > seem to get the procedure to compile.  Help?
                    > > > > >
                    > > > > >
                    > > > > >
                    > > > > >
                    > > > > > ------------------------ Yahoo! Groups Sponsor
                    > > > > > ---------------------~--> Buy Ink Cartridges or
                    > Refill Kits for
                    > > > > > your HP, Epson, Canon or Lexmark Printer at
                    > MyInks.com. Free s/h
                    > > > > > on orders $50 or more to the
                    US & Canada.
                    > > > > http://www.c1tracking.com/l.asp?cid=5511
                    > > > > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                    > > > > ----------------------------------------------------------------
                    > ----
                    > > > -~->
                    > > > >
                    > > > > 
                    > > > > Yahoo! Groups Links
                    > > >
                    > > >
                    > > >
                    > > >
                    > > > ------------------------ Yahoo! Groups Sponsor
                    > > > ---------------------~--> Buy Ink Cartridges or Refill Kits
                    > > > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
                    > > > Free s/h on orders $50 or more to the
                    US & Canada.
                    > > http://www.c1tracking.com/l.asp?cid=5511
                    > > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                    > > --------------------------------------------------------------------
                    > -~->
                    > >
                    > > 
                    > > Yahoo! Groups Links
                    >
                    >
                    >
                    >
                    > ------------------------ Yahoo! Groups Sponsor
                    > ---------------------~--> Buy Ink Cartridges or Refill Kits
                    > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
                    > Free s/h on orders $50 or more to the
                    US & Canada.
                    http://www.c1tracking.com/l.asp?cid=5511
                    http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                    ---------------------------------------------------------------------~->


                    Yahoo! Groups Links








                  • Damhuis Anton
                    I did a search on Google and on MSDN as well SQL BOL and found nothing. Therefore I must conclude this /*+RULE*/ must be a comment as previously stated. What
                    Message 9 of 14 , Feb 10, 2004
                    • 0 Attachment
                      I did a search on Google and on MSDN as well SQL BOL and found nothing.
                      Therefore I must conclude this /*+RULE*/ must be a comment as previously stated.
                      What happens if you run the query without the  +rule?
                       
                      Please also post the entire SQL statement.
                       
                      Has David Penton had any comments (that I missed)?
                       
                      Regards
                         Anton
                      -----Original Message-----
                      From: jason_c_ellis [mailto:jason_c_ellis@...]
                      Sent: Friday, February 06, 2004 10:04 PM
                      To: SQLQueriesNoCode@yahoogroups.com
                      Subject: [SQLQueriesNoCode] Re: How do I query a

                      That is not true.  Putting the '+' in there makes it NOT a comment. 
                      I put the /*+RULE*/ in there and it sped my query up by 8 minutes.

                      So my question is...what does the +Rule do in SQL?


                      --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@j...>
                      wrote:
                      > Nothing the way you are posting here
                      > /* comment */ is just that, a comment.
                      >
                      > John Warner
                      > mailto:john@j...
                      >
                      > > -----Original Message-----
                      > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
                      > > Sent: Friday, February 06, 2004 10:26 AM
                      > > To: SQLQueriesNoCode@yahoogroups.com
                      > > Subject: [SQLQueriesNoCode] Re: How do I query a
                      > >
                      > >
                      > > I did get it to compile.  My question is what exactly does
                      > > the /*+RULE*/ do in a procedure?
                      > >
                      > >
                      > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
                      <john@j...>
                      > > wrote:
                      > > > Going to need a little more information. How about showing us
                      the
                      > > entire
                      > > > proc?
                      > > >
                      > > > John Warner
                      > > > mailto:john@j...
                      > > >
                      > > > > -----Original Message-----
                      > > > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
                      > > > > Sent: Thursday, February 05, 2004 2:42 PM
                      > > > > To: SQLQueriesNoCode@yahoogroups.com
                      > > > > Subject: [SQLQueriesNoCode] How do I query a
                      > > > >
                      > > > >
                      > > > > Select /*RULE*/ Distinct?  I can get it to run just fine but
                      > > can't
                      > > > > seem to get the procedure to compile.  Help?
                      > > > >
                      > > > >
                      > > > >
                      > > > >
                      > > > > ------------------------ Yahoo! Groups Sponsor
                      > > > > ---------------------~--> Buy Ink Cartridges or Refill Kits
                      > > > > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
                      > > > > Free s/h on orders $50 or more to the US & Canada.
                      > > > http://www.c1tracking.com/l.asp?cid=5511
                      > > > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                      > > > ----------------------------------------------------------------
                      ----
                      > > -~->
                      > > >
                      > > > 
                      > > > Yahoo! Groups Links
                      > >
                      > >
                      > >
                      > >
                      > > ------------------------ Yahoo! Groups Sponsor
                      > > ---------------------~--> Buy Ink Cartridges or Refill Kits
                      > > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
                      > > Free s/h on orders $50 or more to the US & Canada.
                      > http://www.c1tracking.com/l.asp?cid=5511
                      > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                      > --------------------------------------------------------------------
                      -~->
                      >

                      > Yahoo! Groups Links



                      Confidentiality Warning
                      =======================
                      The contents of this e-mail and any accompanying documentation
                      are confidential and any use thereof, in what ever form, by anyone
                      other than the addressee is strictly prohibited.
                      
                    • John Warner
                      The reason you cannot find it online is Oracle does not make their documentation freely available on the Web. Thus when the Google bot catalogs the Oracle
                      Message 10 of 14 , Feb 10, 2004
                      • 0 Attachment
                        Message
                        The reason you cannot find it online is Oracle does not make their documentation freely available on the Web. Thus when the Google bot catalogs the Oracle website only a VERY limited snapshot is taken. Oracle does not want to make looking up information about using their products too easy.
                         

                        John Warner
                        mailto:john@...

                        -----Original Message-----
                        From: Damhuis Anton [mailto:DamhuisA@...]
                        Sent: Tuesday, February 10, 2004 7:34 AM
                        To: SQLQueriesNoCode@yahoogroups.com
                        Subject: RE: [SQLQueriesNoCode] Re: How do I query a

                        I did a search on Google and on MSDN as well SQL BOL and found nothing.
                        Therefore I must conclude this /*+RULE*/ must be a comment as previously stated.
                        What happens if you run the query without the  +rule?
                         
                        Please also post the entire SQL statement.
                         
                        Has David Penton had any comments (that I missed)?
                         
                        Regards
                           Anton
                        -----Original Message-----
                        From: jason_c_ellis [mailto:jason_c_ellis@...]
                        Sent: Friday, February 06, 2004 10:04 PM
                        To: SQLQueriesNoCode@yahoogroups.com
                        Subject: [SQLQueriesNoCode] Re: How do I query a

                        That is not true.  Putting the '+' in there makes it NOT a comment. 
                        I put the /*+RULE*/ in there and it sped my query up by 8 minutes.

                        So my question is...what does the +Rule do in SQL?


                        --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@j...>
                        wrote:
                        > Nothing the way you are posting here
                        > /* comment */ is just that, a comment.
                        >
                        > John Warner
                        > mailto:john@j...
                        >
                        > > -----Original Message-----
                        > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
                        > > Sent: Friday, February 06, 2004 10:26 AM
                        > > To: SQLQueriesNoCode@yahoogroups.com
                        > > Subject: [SQLQueriesNoCode] Re: How do I query a
                        > >
                        > >
                        > > I did get it to compile.  My question is what exactly does
                        > > the /*+RULE*/ do in a procedure?
                        > >
                        > >
                        > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
                        <john@j...>
                        > > wrote:
                        > > > Going to need a little more information. How about showing us
                        the
                        > > entire
                        > > > proc?
                        > > >
                        > > > John Warner
                        > > > mailto:john@j...
                        > > >
                        > > > > -----Original Message-----
                        > > > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
                        > > > > Sent: Thursday, February 05, 2004 2:42 PM
                        > > > > To: SQLQueriesNoCode@yahoogroups.com
                        > > > > Subject: [SQLQueriesNoCode] How do I query a
                        > > > >
                        > > > >
                        > > > > Select /*RULE*/ Distinct?  I can get it to run just fine but
                        > > can't
                        > > > > seem to get the procedure to compile.  Help?
                        > > > >
                        > > > >
                        > > > >
                        > > > >
                        > > > > ------------------------ Yahoo! Groups Sponsor
                        > > > > ---------------------~--> Buy Ink Cartridges or Refill Kits
                        > > > > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
                        > > > > Free s/h on orders $50 or more to the US & Canada.
                        > > > http://www.c1tracking.com/l.asp?cid=5511
                        > > > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                        > > > ----------------------------------------------------------------
                        ----
                        > > -~->
                        > > >
                        > > > 
                        > > > Yahoo! Groups Links
                        > >
                        > >
                        > >
                        > >
                        > > ------------------------ Yahoo! Groups Sponsor
                        > > ---------------------~--> Buy Ink Cartridges or Refill Kits
                        > > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
                        > > Free s/h on orders $50 or more to the US & Canada.
                        > http://www.c1tracking.com/l.asp?cid=5511
                        > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                        > --------------------------------------------------------------------
                        -~->
                        >

                        > Yahoo! Groups Links




                        Confidentiality Warning
                        =======================
                        The contents of this e-mail and any accompanying documentation
                        are confidential and any use thereof, in what ever form, by anyone
                        other than the addressee is strictly prohibited.
                        
                      • Tony Green
                        There was another post in reply to this that said that the + rule is to do with Oracle SQL ! ... From: Damhuis Anton [mailto:DamhuisA@aforbes.co.za] Sent: 10
                        Message 11 of 14 , Feb 10, 2004
                        • 0 Attachment
                          There was another post in reply to this that said that the + rule is to do
                          with Oracle SQL !

                          -----Original Message-----
                          From: Damhuis Anton [mailto:DamhuisA@...]
                          Sent: 10 February 2004 12:34
                          To: SQLQueriesNoCode@yahoogroups.com
                          Subject: RE: [SQLQueriesNoCode] Re: How do I query a


                          I did a search on Google and on MSDN as well SQL BOL and found nothing.
                          Therefore I must conclude this /*+RULE*/ must be a comment as previously
                          stated.
                          What happens if you run the query without the +rule?

                          Please also post the entire SQL statement.

                          Has David Penton had any comments (that I missed)?

                          Regards
                          Anton

                          -----Original Message-----
                          From: jason_c_ellis [mailto:jason_c_ellis@...]
                          Sent: Friday, February 06, 2004 10:04 PM
                          To: SQLQueriesNoCode@yahoogroups.com
                          Subject: [SQLQueriesNoCode] Re: How do I query a


                          That is not true. Putting the '+' in there makes it NOT a comment.
                          I put the /*+RULE*/ in there and it sped my query up by 8 minutes.

                          So my question is...what does the +Rule do in SQL?


                          --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@j...>
                          wrote:
                          > Nothing the way you are posting here
                          > /* comment */ is just that, a comment.
                          >
                          > John Warner
                          > mailto:john@j...
                          >
                          > > -----Original Message-----
                          > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
                          > > Sent: Friday, February 06, 2004 10:26 AM
                          > > To: SQLQueriesNoCode@yahoogroups.com
                          > > Subject: [SQLQueriesNoCode] Re: How do I query a
                          > >
                          > >
                          > > I did get it to compile. My question is what exactly does
                          > > the /*+RULE*/ do in a procedure?
                          > >
                          > >
                          > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
                          <john@j...>
                          > > wrote:
                          > > > Going to need a little more information. How about showing us
                          the
                          > > entire
                          > > > proc?
                          > > >
                          > > > John Warner
                          > > > mailto:john@j...
                          > > >
                          > > > > -----Original Message-----
                          > > > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
                          > > > > Sent: Thursday, February 05, 2004 2:42 PM
                          > > > > To: SQLQueriesNoCode@yahoogroups.com
                          > > > > Subject: [SQLQueriesNoCode] How do I query a
                          > > > >
                          > > > >
                          > > > > Select /*RULE*/ Distinct? I can get it to run just fine but
                          > > can't
                          > > > > seem to get the procedure to compile. Help?
                          > > > >
                          > > > >
                          > > > >
                          > > > >
                          > > > > ------------------------ Yahoo! Groups Sponsor
                          > > > > ---------------------~--> Buy Ink Cartridges or Refill Kits
                          > > > > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
                          > > > > Free s/h on orders $50 or more to the US & Canada.
                          > > > http://www.c1tracking.com/l.asp?cid=5511
                          <http://www.c1tracking.com/l.asp?cid=5511>
                          > > > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                          <http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM>
                          > > > ----------------------------------------------------------------
                          ----
                          > > -~->
                          > > >
                          > > >
                          > > > Yahoo! Groups Links
                          > >
                          > >
                          > >
                          > >
                          > > ------------------------ Yahoo! Groups Sponsor
                          > > ---------------------~--> Buy Ink Cartridges or Refill Kits
                          > > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
                          > > Free s/h on orders $50 or more to the US & Canada.
                          > http://www.c1tracking.com/l.asp?cid=5511
                          <http://www.c1tracking.com/l.asp?cid=5511>
                          > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                          <http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM>
                          > --------------------------------------------------------------------
                          -~->
                          >
                          >
                          > Yahoo! Groups Links





                          Yahoo! Groups Sponsor

                          ADVERTISEMENT

                          <http://rd.yahoo.com/SIG=12clh7o06/M=267637.4521717.5694185.1261774/D=egroup
                          web/S=1705115366:HM/EXP=1076514525/A=1945638/R=0/*http://www.netflix.com/Def
                          ault?mqso=60178383&partid=4521717> click here

                          <http://us.adserver.yahoo.com/l?M=267637.4521717.5694185.1261774/D=egroupweb
                          /S=:HM/A=1945638/rand=296287420>


                          _____

                          Yahoo! Groups Links


                          * To visit your group on the web, go to:
                          http://groups.yahoo.com/group/SQLQueriesNoCode/
                          <http://groups.yahoo.com/group/SQLQueriesNoCode/>


                          * To unsubscribe from this group, send an email to:
                          SQLQueriesNoCode-unsubscribe@yahoogroups.com
                          <mailto:SQLQueriesNoCode-unsubscribe@yahoogroups.com?subject=Unsubscribe>


                          * Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
                          <http://docs.yahoo.com/info/terms/> .



                          Confidentiality Warning

                          =======================

                          The contents of this e-mail and any accompanying documentation

                          are confidential and any use thereof, in what ever form, by anyone

                          other than the addressee is strictly prohibited.
                        • jaggs0213
                          The answer is that the /*+ RULE */ hint directs Oracle to use the Rule Based Optimizer rather than the current default Cost Based Optimizer. A well designed
                          Message 12 of 14 , Feb 11, 2004
                          • 0 Attachment
                            The answer is that the /*+ RULE */ hint directs Oracle to use the
                            Rule Based Optimizer rather than the current default Cost Based
                            Optimizer. A well designed set of rules for a particular database
                            will usually outperform Cost Based Optimization as people are still
                            smarter than software. But, if you work with a dynamic database
                            keeping the Rules current can be quite a chore.
                          • jason_c_ellis
                            Without the /*RULE*/ the query takes about 10-12 minutes. But with this in the statement the query takes 1-2 minutes. I will post the entire thing. Again
                            Message 13 of 14 , Feb 11, 2004
                            • 0 Attachment
                              Without the /*RULE*/ the query takes about 10-12 minutes. But with
                              this in the statement the query takes 1-2 minutes. I will post the
                              entire thing. Again this is for Oracle 9i using PL/SQL Developer.


                              CREATE OR REPLACE PROCEDURE GetAdminCompliance

                              (
                              RegionIN IN VARCHAR2,
                              DistrictIN IN VARCHAR2,
                              RepIDIn IN VARCHAR2,
                              BeginDateIn IN DATE,
                              EndDateIn IN DATE,
                              RPT_DATA OUT Stars_AdminCompliance.DataCursor
                              )

                              AS
                              BEGIN
                              OPEN RPT_DATA FOR
                              SELECT /*+RULE*/DISTINCT MAX(a.datecreated) LastestDate,
                              MIN(a.datecreated) EarlyDate,
                              MAX(a.creatorname) CreatorName,
                              COUNT(CASE a.subject
                              WHEN 'In Training' THEN
                              a.subject
                              ELSE
                              ''
                              END) InTraining,
                              COUNT(CASE a.subject
                              WHEN 'Sales Administration' THEN
                              a.subject
                              ELSE
                              ''
                              END) SAdmin,
                              COUNT(CASE a.subject
                              WHEN 'Presentations' THEN
                              a.subject
                              ELSE
                              ''
                              END) Presentations,
                              COUNT(CASE a.subject
                              WHEN 'Customer Reassignment' THEN
                              a.subject
                              ELSE
                              ''
                              END) CustomerReassignment,
                              COUNT(CASE a.subject
                              WHEN 'Events' THEN
                              a.subject
                              ELSE
                              ''
                              END) Event,

                              COUNT((Select count(c.netgainid)
                              FROM ngbcontact c, contact2activity c2a
                              WHERE c2a.contactid = c.netgainid and
                              c2a.activityid = a.netgainid and
                              a.subject = 'Follow-up'
                              GROUP BY c2a.activityid)) FollowUp,

                              count((Select count(c.netgainid)
                              FROM ngbcontact c, contact2activity c2a
                              WHERE c2a.contactid = c.netgainid and
                              c2a.activityid = a.netgainid and
                              a.subject = 'Sales Call (Face to Face)'
                              GROUP BY c2a.activityid)) SalesCall,

                              count((Select count(c.netgainid)
                              FROM ngbcontact c, contact2activity c2a
                              WHERE c2a.contactid = c.netgainid and
                              c2a.activityid = a.netgainid and
                              a.subject =
                              'Product Placement; Dressing Changes'
                              GROUP BY c2a.activityid)) ProdPlacement,

                              count((Select count(c.netgainid)
                              FROM ngbcontact c, contact2activity c2a
                              WHERE c2a.contactid = c.netgainid and
                              c2a.activityid = a.netgainid and
                              a.subject = 'Rounds/Patient Consult'
                              GROUP BY c2a.activityid)) RoundsPatConsult,

                              count((Select count(c.netgainid)
                              FROM ngbcontact c, contact2activity c2a
                              WHERE c2a.contactid = c.netgainid and
                              c2a.activityid = a.netgainid and
                              a.subject = 'Patient Transitions'
                              GROUP BY c2a.activityid)) PatientTrans,

                              COUNT(CASE a.activitytype
                              WHEN 'Non-Selling' THEN
                              a.activitytype
                              ELSE
                              ''
                              END) NonSelling,

                              ((Select count(t.netgainid)
                              FROM kcibtrainingsession t
                              WHERE t.trainerid = r.netgainid
                              GROUP BY t.trainerid)) traincount,

                              ((select count(e.netgainid)
                              FROM kcibevent e
                              WHERE e.repid = r.netgainid
                              GROUP BY repid)) eventcount,

                              MAX(r.kciregion),
                              MAX(r.fullrepresentativename) fullrepname,
                              r.netgainid,
                              --r.userid,
                              BeginDateIN BeginDate,
                              EndDateIN EndDate

                              FROM ngbactivity a, ngbrepresentative r

                              WHERE (r.netgainid like funct_to_search(RepIDIN) OR RepIDIN is
                              NULL) AND
                              r.netgainid = a.creatorid AND
                              (r.kciregion like funct_to_search(KCIRegionIN) OR
                              KCIRegionIN is NULL) and
                              (r.kcidistrict like funct_to_search(KCIDistrictIN) OR
                              KCIDistrictIN is NULL) and
                              /*(r.fullrepresentativename like FUNCT_TO_SEARCH
                              (SalesRepNameIn) OR SalesRepNameIN IS NULL) and*/
                              (a.datecreated between BeginDateIN and EndDateIN) and

                              a.subject IN
                              ('In-Service Training', 'Sales
                              Administration', 'Presentations',
                              'Customer Reassignment', 'Events', 'Sales Call (Face to
                              Face)',
                              'Follow-up', 'Product Placement;Dressing Change',
                              'Rounds/Patient Consult', 'Patient Transitions', 'Internal
                              Administration' ,
                              'Internal Meetings', 'Internal
                              Training' , 'Service' , 'Sick' , 'Vacation' ) AND
                              a.activitytype IN ('Selling', 'Non-Selling') AND
                              a.status = 'Completed'
                              /*r.userid = 'F721'*/

                              GROUP BY r.netgainid;

                              END;



                              --- In SQLQueriesNoCode@yahoogroups.com, "Damhuis Anton"
                              <DamhuisA@a...> wrote:
                              >
                              > I did a search on Google and on MSDN as well SQL BOL and found
                              nothing.
                              > Therefore I must conclude this /*+RULE*/ must be a comment as
                              previously stated.
                              > What happens if you run the query without the +rule?
                              >
                              > Please also post the entire SQL statement.
                              >
                              > Has David Penton had any comments (that I missed)?
                              >
                              > Regards
                              > Anton
                              >
                              > -----Original Message-----
                              > From: jason_c_ellis [mailto:jason_c_ellis@h...]
                              > Sent: Friday, February 06, 2004 10:04 PM
                              > To: SQLQueriesNoCode@yahoogroups.com
                              > Subject: [SQLQueriesNoCode] Re: How do I query a
                              >
                              >
                              > That is not true. Putting the '+' in there makes it NOT a comment.
                              > I put the /*+RULE*/ in there and it sped my query up by 8 minutes.
                              >
                              > So my question is...what does the +Rule do in SQL?
                              >
                              >
                              > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@j...>
                              > wrote:
                              > > Nothing the way you are posting here
                              > > /* comment */ is just that, a comment.
                              > >
                              > > John Warner
                              > > mailto:john@j...
                              > >
                              > > > -----Original Message-----
                              > > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
                              > > > Sent: Friday, February 06, 2004 10:26 AM
                              > > > To: SQLQueriesNoCode@yahoogroups.com
                              > > > Subject: [SQLQueriesNoCode] Re: How do I query a
                              > > >
                              > > >
                              > > > I did get it to compile. My question is what exactly does
                              > > > the /*+RULE*/ do in a procedure?
                              > > >
                              > > >
                              > > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
                              > <john@j...>
                              > > > wrote:
                              > > > > Going to need a little more information. How about showing us
                              > the
                              > > > entire
                              > > > > proc?
                              > > > >
                              > > > > John Warner
                              > > > > mailto:john@j...
                              > > > >
                              > > > > > -----Original Message-----
                              > > > > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
                              > > > > > Sent: Thursday, February 05, 2004 2:42 PM
                              > > > > > To: SQLQueriesNoCode@yahoogroups.com
                              > > > > > Subject: [SQLQueriesNoCode] How do I query a
                              > > > > >
                              > > > > >
                              > > > > > Select /*RULE*/ Distinct? I can get it to run just fine but
                              > > > can't
                              > > > > > seem to get the procedure to compile. Help?
                              > > > > >
                              > > > > >
                              > > > > >
                              > > > > >
                              > > > > > ------------------------ Yahoo! Groups Sponsor
                              > > > > > ---------------------~--> Buy Ink Cartridges or Refill Kits
                              > > > > > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
                              > > > > > Free s/h on orders $50 or more to the US & Canada.
                              > > > > http://www.c1tracking.com/l.asp?cid=5511
                              > > > > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                              > > > > --------------------------------------------------------------
                              --
                              > ----
                              > > > -~->
                              > > > >
                              > > > >
                              > > > > Yahoo! Groups Links
                              > > >
                              > > >
                              > > >
                              > > >
                              > > > ------------------------ Yahoo! Groups Sponsor
                              > > > ---------------------~--> Buy Ink Cartridges or Refill Kits
                              > > > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
                              > > > Free s/h on orders $50 or more to the US & Canada.
                              > > http://www.c1tracking.com/l.asp?cid=5511
                              > > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                              > > ------------------------------------------------------------------
                              --
                              > -~->
                              > >
                              > >
                              > > Yahoo! Groups Links
                              >
                              >
                              >
                              >
                              > Yahoo! Groups Sponsor
                              >
                              > ADVERTISEMENT
                              >
                              <http://rd.yahoo.com/SIG=12cd62lnj/M=243273.4510124.5685162.1261774/D=
                              egroupweb/S=1705115366:HM/EXP=1076200533/A=1750744/R=0/*http://servedb
                              y.advertising.com/click/site=552006/bnum=1076114133126106> Click to
                              learn more...
                              > <http://us.adserver.yahoo.com/l?
                              M=243273.4510124.5685162.1261774/D=egroupweb/S=:HM/A=1750744/rand=4871
                              07609>
                              >
                              >
                              > _____
                              >
                              > Yahoo! Groups Links
                              >
                              >
                              > * To visit your group on the web, go to:
                              > http://groups.yahoo.com/group/SQLQueriesNoCode/
                              >
                              >
                              > * To unsubscribe from this group, send an email to:
                              > SQLQueriesNoCode-unsubscribe@yahoogroups.com
                              <mailto:SQLQueriesNoCode-unsubscribe@yahoogroups.com?
                              subject=Unsubscribe>
                              >
                              >
                              > * Your use of Yahoo! Groups is subject to the Yahoo! Terms of
                              Service <http://docs.yahoo.com/info/terms/> .
                              >
                              >
                              >
                              >
                              > Confidentiality Warning
                              > =======================
                              > The contents of this e-mail and any accompanying documentation
                              > are confidential and any use thereof, in what ever form, by anyone
                              > other than the addressee is strictly prohibited.
                            • Damhuis Anton
                              I forwarded this email to an Oracle Developer work with, and this is what he had to say,and is along the same lines as what jim.jaggers
                              Message 14 of 14 , Feb 11, 2004
                              • 0 Attachment

                                I forwarded this email to an Oracle Developer  work with, and this is what he had to say,and is along the same lines as what jim.jaggers said:
                                The syntax for comments and hints look very similar and can sometimes be confused with each other.
                                The only difference is the addition of the + sign.

                                /* Text */           This is the format for a comment. Oracle will ignore these, as they do not form part of the SQL syntax.
                                /*+ Text */         Oracle will see the + sign and attempt to process this as a hint. If no logical hint entry is made, then Oracle will treat this as a comment.

                                Hints in Oracle allow the developer to alter the way that Oracle processes commands.
                                The original method that Oracle used to analyse and process commands was the Rule based processing method.
                                Rule based processing follows approximately 30 rules that allow Oracle to analyse the SQL statements and come up with a fairly efficient execution plan for the command.
                                With later versions of Oracle the Analyse command was introduced.
                                The Analyse command gathers statistics from the tables and other Oracle objects and stores the statistics in the Oracle dictionary.
                                Using these analysis statistics Oracle can now make much better, more efficient decisions on how to analyse and execute the commands.

                                Example – small tables may be read entirely into memory and used from there instead of an indexed lookup.

                                This new method is called Cost based processing.
                                The failure that may be observed in the example is that the database may be setup as Cost based processing, and the Analyse command has not been used correctly or frequently enough.
                                The result is that the Analyse statistics are not sufficient for Oracle to make the best decision using Cost based processing methods, and may make decisions that are less efficient than the Rule based processing.
                                By placing the /*+ Rule */ hint into the SQL command, the developer now tells Oracle to override the Cost based processing with the Rule based processing method.
                                This will then allow the general, reliable efficiency of the Rule based processing to take over.

                                Cheers
                                Les

                                 

                                -----Original Message-----
                                From: jason_c_ellis [mailto:jason_c_ellis@...]
                                Sent: Wednesday, February 11, 2004 6:43 PM
                                To: SQLQueriesNoCode@yahoogroups.com
                                Subject: [SQLQueriesNoCode] Re: How do I query a

                                Without the /*RULE*/ the query takes about 10-12 minutes.  But with
                                this in the statement the query takes 1-2 minutes.  I will post the
                                entire thing.  Again this is for Oracle 9i using PL/SQL Developer.


                                CREATE OR REPLACE PROCEDURE GetAdminCompliance

                                (
                                RegionIN    IN VARCHAR2,
                                DistrictIN  IN VARCHAR2,
                                RepIDIn        IN VARCHAR2,
                                BeginDateIn    IN DATE,
                                EndDateIn      IN DATE,
                                RPT_DATA       OUT Stars_AdminCompliance.DataCursor
                                )

                                  AS
                                BEGIN
                                  OPEN RPT_DATA FOR
                                  SELECT /*+RULE*/DISTINCT MAX(a.datecreated)   LastestDate,
                                                  MIN(a.datecreated)   EarlyDate,
                                                  MAX(a.creatorname)   CreatorName,
                                                  COUNT(CASE a.subject
                                                          WHEN 'In Training' THEN
                                                           a.subject
                                                          ELSE
                                                           ''
                                                        END) InTraining,
                                                  COUNT(CASE a.subject
                                                          WHEN 'Sales Administration' THEN
                                                           a.subject
                                                          ELSE
                                                           ''
                                                        END) SAdmin,
                                                  COUNT(CASE a.subject
                                                          WHEN 'Presentations' THEN
                                                           a.subject
                                                          ELSE
                                                           ''
                                                        END) Presentations,
                                                  COUNT(CASE a.subject
                                                          WHEN 'Customer Reassignment' THEN
                                                           a.subject
                                                          ELSE
                                                           ''
                                                        END) CustomerReassignment,
                                                  COUNT(CASE a.subject
                                                          WHEN 'Events' THEN
                                                           a.subject
                                                          ELSE
                                                           ''
                                                        END) Event,
                                                 
                                                  COUNT((Select count(c.netgainid)
                                                          FROM ngbcontact c, contact2activity c2a
                                                         WHERE c2a.contactid = c.netgainid and
                                                               c2a.activityid = a.netgainid and
                                                               a.subject = 'Follow-up'
                                                         GROUP BY c2a.activityid)) FollowUp,
                                                 
                                                  count((Select count(c.netgainid)
                                                          FROM ngbcontact c, contact2activity c2a
                                                         WHERE c2a.contactid = c.netgainid and
                                                               c2a.activityid = a.netgainid and
                                                               a.subject = 'Sales Call (Face to Face)'
                                                         GROUP BY c2a.activityid)) SalesCall,
                                                 
                                                  count((Select count(c.netgainid)
                                                          FROM ngbcontact c, contact2activity c2a
                                                         WHERE c2a.contactid = c.netgainid and
                                                               c2a.activityid = a.netgainid and
                                                               a.subject =
                                                               'Product Placement; Dressing Changes'
                                                         GROUP BY c2a.activityid)) ProdPlacement,
                                                 
                                                  count((Select count(c.netgainid)
                                                          FROM ngbcontact c, contact2activity c2a
                                                         WHERE c2a.contactid = c.netgainid and
                                                               c2a.activityid = a.netgainid and
                                                               a.subject = 'Rounds/Patient Consult'
                                                         GROUP BY c2a.activityid)) RoundsPatConsult,
                                                 
                                                  count((Select count(c.netgainid)
                                                          FROM ngbcontact c, contact2activity c2a
                                                         WHERE c2a.contactid = c.netgainid and
                                                               c2a.activityid = a.netgainid and
                                                               a.subject = 'Patient Transitions'
                                                         GROUP BY c2a.activityid)) PatientTrans,
                                                        
                                                         COUNT(CASE a.activitytype
                                                          WHEN 'Non-Selling' THEN
                                                           a.activitytype
                                                          ELSE
                                                           ''
                                                        END) NonSelling,
                                                 
                                                ((Select count(t.netgainid)
                                                      FROM kcibtrainingsession t
                                                     WHERE t.trainerid = r.netgainid
                                                     GROUP BY t.trainerid)) traincount,
                                                 
                                                 ((select count(e.netgainid)
                                                      FROM kcibevent e
                                                     WHERE e.repid = r.netgainid
                                                     GROUP BY repid)) eventcount,
                                                 
                                                  MAX(r.kciregion),
                                                  MAX(r.fullrepresentativename) fullrepname,
                                                  r.netgainid,
                                                  --r.userid,
                                                  BeginDateIN BeginDate,
                                                  EndDateIN EndDate
                                 
                                   FROM ngbactivity a, ngbrepresentative r
                                 
                                   WHERE (r.netgainid like funct_to_search(RepIDIN) OR RepIDIN is
                                NULL) AND
                                         r.netgainid = a.creatorid AND
                                         (r.kciregion like funct_to_search(KCIRegionIN) OR
                                KCIRegionIN is NULL) and
                                         (r.kcidistrict like funct_to_search(KCIDistrictIN) OR
                                KCIDistrictIN is NULL) and
                                         /*(r.fullrepresentativename like FUNCT_TO_SEARCH
                                (SalesRepNameIn) OR SalesRepNameIN IS NULL) and*/
                                         (a.datecreated between BeginDateIN and EndDateIN) and
                                        
                                         a.subject IN
                                         ('In-Service Training', 'Sales
                                Administration', 'Presentations',
                                          'Customer Reassignment', 'Events', 'Sales Call (Face to
                                Face)',
                                          'Follow-up', 'Product Placement;Dressing Change',
                                          'Rounds/Patient Consult', 'Patient Transitions', 'Internal
                                Administration' ,
                                           'Internal Meetings', 'Internal
                                Training' , 'Service' , 'Sick' , 'Vacation' ) AND
                                         a.activitytype IN ('Selling', 'Non-Selling') AND
                                         a.status = 'Completed'
                                  /*r.userid = 'F721'*/
                                 
                                   GROUP BY r.netgainid;
                                  
                                   END;



                                --- In SQLQueriesNoCode@yahoogroups.com, "Damhuis Anton"
                                <DamhuisA@a...> wrote:
                                >
                                > I did a search on Google and on MSDN as well SQL BOL and found
                                nothing.
                                > Therefore I must conclude this /*+RULE*/ must be a comment as
                                previously stated.
                                > What happens if you run the query without the  +rule?
                                >
                                > Please also post the entire SQL statement.
                                >
                                > Has David Penton had any comments (that I missed)?
                                >
                                > Regards
                                >    Anton
                                >
                                > -----Original Message-----
                                > From: jason_c_ellis [mailto:jason_c_ellis@h...]
                                > Sent: Friday, February 06, 2004 10:04 PM
                                > To: SQLQueriesNoCode@yahoogroups.com
                                > Subject: [SQLQueriesNoCode] Re: How do I query a
                                >
                                >
                                > That is not true.  Putting the '+' in there makes it NOT a comment.
                                > I put the /*+RULE*/ in there and it sped my query up by 8 minutes.
                                >
                                > So my question is...what does the +Rule do in SQL?
                                >
                                >
                                > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@j...>
                                > wrote:
                                > > Nothing the way you are posting here
                                > > /* comment */ is just that, a comment.
                                > >
                                > > John Warner
                                > > mailto:john@j...
                                > >
                                > > > -----Original Message-----
                                > > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
                                > > > Sent: Friday, February 06, 2004 10:26 AM
                                > > > To: SQLQueriesNoCode@yahoogroups.com
                                > > > Subject: [SQLQueriesNoCode] Re: How do I query a
                                > > >
                                > > >
                                > > > I did get it to compile.  My question is what exactly does
                                > > > the /*+RULE*/ do in a procedure?
                                > > >
                                > > >
                                > > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
                                > <john@j...>
                                > > > wrote:
                                > > > > Going to need a little more information. How about showing us
                                > the
                                > > > entire
                                > > > > proc?
                                > > > >
                                > > > > John Warner
                                > > > > mailto:john@j...
                                > > > >
                                > > > > > -----Original Message-----
                                > > > > > From: jason_c_ellis [mailto:jason_c_ellis@h...]
                                > > > > > Sent: Thursday, February 05, 2004 2:42 PM
                                > > > > > To: SQLQueriesNoCode@yahoogroups.com
                                > > > > > Subject: [SQLQueriesNoCode] How do I query a
                                > > > > >
                                > > > > >
                                > > > > > Select /*RULE*/ Distinct?  I can get it to run just fine but
                                > > > can't
                                > > > > > seem to get the procedure to compile.  Help?
                                > > > > >
                                > > > > >
                                > > > > >
                                > > > > >
                                > > > > > ------------------------ Yahoo! Groups Sponsor
                                > > > > > ---------------------~--> Buy Ink Cartridges or Refill Kits
                                > > > > > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
                                > > > > > Free s/h on orders $50 or more to the US & Canada.
                                > > > > http://www.c1tracking.com/l.asp?cid=5511
                                > > > > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                                > > > > --------------------------------------------------------------
                                --
                                > ----
                                > > > -~->
                                > > > >
                                > > > >
                                > > > > Yahoo! Groups Links
                                > > >
                                > > >
                                > > >
                                > > >
                                > > > ------------------------ Yahoo! Groups Sponsor
                                > > > ---------------------~--> Buy Ink Cartridges or Refill Kits
                                > > > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
                                > > > Free s/h on orders $50 or more to the US & Canada.
                                > > http://www.c1tracking.com/l.asp?cid=5511
                                > > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
                                > > ------------------------------------------------------------------
                                --
                                > -~->
                                > >
                                > >
                                > > Yahoo! Groups Links
                                >
                                >
                                >
                                >
                                > Yahoo! Groups Sponsor
                                >
                                > ADVERTISEMENT

                                <http://rd.yahoo.com/SIG=12cd62lnj/M=243273.4510124.5685162.1261774/D=
                                egroupweb/S=1705115366:HM/EXP=1076200533/A=1750744/R=0/*http://servedb
                                y.advertising.com/click/site=552006/bnum=1076114133126106> Click to
                                learn more...
                                >   <http://us.adserver.yahoo.com/l?
                                M=243273.4510124.5685162.1261774/D=egroupweb/S=:HM/A=1750744/rand=4871
                                07609>
                                >
                                >
                                >   _____
                                >
                                > Yahoo! Groups Links
                                >
                                >
                                > *      To visit your group on the web, go to:
                                > http://groups.yahoo.com/group/SQLQueriesNoCode/

                                >
                                > *      To unsubscribe from this group, send an email to:
                                > SQLQueriesNoCode-unsubscribe@yahoogroups.com
                                <mailto:SQLQueriesNoCode-unsubscribe@yahoogroups.com?
                                subject=Unsubscribe>

                                >
                                > *      Your use of Yahoo! Groups is subject to the Yahoo! Terms of
                                Service <http://docs.yahoo.com/info/terms/> .
                                >
                                >
                                >
                                >
                                > Confidentiality Warning
                                > =======================
                                > The contents of this e-mail and any accompanying documentation
                                > are confidential and any use thereof, in what ever form, by anyone
                                > other than the addressee is strictly prohibited.



                                Confidentiality Warning
                                =======================
                                The contents of this e-mail and any accompanying documentation
                                are confidential and any use thereof, in what ever form, by anyone
                                other than the addressee is strictly prohibited.
                                
                              Your message has been successfully submitted and would be delivered to recipients shortly.