Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2005
    Posts
    7

    Unanswered: How to use hint in an unnamed subquery

    there is a subquery without a name/alias in the where clause such as

    select * from t_a where col1 in (select col2 from table t_b)


    if there is an alias(suppose alias_v) or name for the subquery, we can use
    /*+ full(alias_v.t_b)*/, but is without such an alias, How to tell oracle use full table scan for table b?

  2. #2
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    put the hint in the subquery...

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    If you do an explain plan, you will notice it does a full table scan for that subquery (unless you add a condition).


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    And unless col2 is indexed, right LKBrwn_DBA ?

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If you put the hint in the subquery you might also need the NO_MERGE hint in case it decides to merge the subquery.

    On the other hand do you really need the hint, also a join instead of the IN might be better (if applicable). Putting the hint in may cause issues in the future if your data distribution/volumes change.

    Alan

Posting Permissions

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