Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Location
    USA-CA
    Posts
    36

    Question Unanswered: /*+ Use_hash ... */

    Can anybody explain me if the following query
    with hint USE_HASH will work faster?
    If there any precautions?

    Thanks in advance.


    SELECT /*+ USE_HASH (BILLING_ACCOUNT PRODUCT) */
    *.BAN,
    *.PRD
    FROM
    BILLING_ACCOUNT BAN,
    PRODUCT PRD
    WHERE
    PRD.PRODUCT_ID = '111'
    AND PRD.PRODUCT_TYPE = 'T'
    AND PRD.PROD_STATUS = 'A'
    AND PRD.EFFECTIVE_DATE - 0 <=
    TO_DATE(sysdate,'YYYYMMDD')
    AND BAN.CUSTOMER_ID = PRD.CUSTOMER_ID
    AND BAN.BAN = PRD.CUSTOMER_ID
    Last edited by _John Smith; 07-15-03 at 07:35.
    John Smith

  2. #2
    Join Date
    Apr 2002
    Location
    USA-CA
    Posts
    36

    Re: /*+ Use_hash ... */

    Originally posted by _John Smith
    Can anybody explain me if the following query
    with hint USE_HASH will work faster?
    If there any precautions?

    Thanks in advance.


    SELECT /*+ USE_HASH (BILLING_ACCOUNT PRODUCT) */
    *.BAN,
    *.PRD
    FROM
    BILLING_ACCOUNT BAN,
    PRODUCT PRD
    WHERE
    PRD.PRODUCT_ID = '111'
    AND PRD.PRODUCT_TYPE = 'T'
    AND PRD.PROD_STATUS = 'A'
    AND PRD.EFFECTIVE_DATE - 0 <=
    TO_DATE(sysdate,'YYYYMMDD')
    AND BAN.CUSTOMER_ID = PRD.CUSTOMER_ID
    AND BAN.BAN = PRD.CUSTOMER_ID
    John Smith

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: /*+ Use_hash ... */

    Originally posted by _John Smith
    Can anybody explain me if the following query
    with hint USE_HASH will work faster?
    If there any precautions?

    Thanks in advance.


    SELECT /*+ USE_HASH (BILLING_ACCOUNT PRODUCT) */
    *.BAN,
    *.PRD
    FROM
    BILLING_ACCOUNT BAN,
    PRODUCT PRD
    WHERE
    PRD.PRODUCT_ID = '111'
    AND PRD.PRODUCT_TYPE = 'T'
    AND PRD.PROD_STATUS = 'A'
    AND PRD.EFFECTIVE_DATE - 0 <=
    TO_DATE(sysdate,'YYYYMMDD')
    AND BAN.CUSTOMER_ID = PRD.CUSTOMER_ID
    AND BAN.BAN = PRD.CUSTOMER_ID
    No, nobody can tell you that. If USE_HASH made every query faster, it would be built in and there wouldn't be a hint for it.

    The main precaution I would say is: don't add such hints to queries AT ALL unless you have a very good reason for doing so. Don't consider adding hints to queries to be an everyday kind of action: by doing so you hobble the optimizer and prevent it making a free choice to get the best performance.

Posting Permissions

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