Results 1 to 5 of 5

Thread: Using Hints in SQL

  1. #1
    Join Date
    Mar 2009
    Location
    San Francisco, CA
    Posts
    6

    Using Hints in SQL

    Does any have any idea on how to use the USE_HASH hint?

    I'm confused on how to put the tables in the proper syntax. For example: I would use this format /*+ USE_HASH (s i) */
    on the driving tables, but in this case there is only 2 tables.

    select distinct s.server_id
    from servrs s, serv_inst i
    where s.svr_id = i.svr_id

    Questions:
    1. what about the case when there are mulitple tables like 5tables with 5 joins, would each table be used in the hint?

    2. What is the difference in writing the hint as follows:

    /*+ USE_HASH (s) USE_HASH(i) */


    Girly1972

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You can check EXPLAIN PLAN output to see if hint made any difference. Specifying two tables in USE_HASH means use HASH only when joining those two tables.

  3. #3
    Join Date
    Mar 2009
    Location
    San Francisco, CA
    Posts
    6
    I should see the cost decrease correct?


    girly1972

  4. #4
    Join Date
    Mar 2009
    Location
    San Francisco, CA
    Posts
    6
    See all those nested loops below in my explain plan? That is why I want to use the use_hash hint. See attached explain plan.


    girly1972
    Attached Files Attached Files

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Cost may increase or decrease, that's why you need to generate plan with hints and compare.

Posting Permissions

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