This discussion is archived
12 Replies Latest reply: Nov 21, 2012 3:22 AM by 969801 RSS

a question about hints

969801 Newbie
Currently Being Moderated
if i have a view
              create or replace force view v1 as 
               select * from t1
               union all
               select * from (select * from t2 ,t3 where t2=t3);




              i have a sql statement like :   
    

              select * from t,v1 where t.id=v1.id;


                  if i want change the order of the explain ?
how to use the hints?

Edited by: 966798 on 2012-11-20 下午11:40
  • 1. Re: a question about hints
    jeneesh Guru
    Currently Being Moderated
    966798 wrote:
    if i have a view
    create or replace force view v1 as 
    select * from t1
    union all
    select * from (select * from t2 ,t3 where t2=t3);
    
    
    
    
    i have a sql statement like :   
    
    
    select * from t,v1 where t.id=v1.id;
    
    
    if i want change the order of the explain ?
    how to use the hints?

    Edited by: 966798 on 2012-11-20 下午11:40
    you hav e a table T2 with column name as T2..? Strange..!

    between what are you trying to achieve?

    Could you elaborate?
  • 2. Re: a question about hints
    BluShadow Guru Moderator
    Currently Being Moderated
    966798 wrote:
    if i have a view
    create or replace force view v1 as 
    select * from t1
    union all
    select * from (select * from t2 ,t3 where t2=t3);
    
    
    
    
    i have a sql statement like :   
    
    
    select * from t,v1 where t.id=v1.id;
    if i want change the order of the explain ?


    how to use the hints?
    Why do you want to change the explain plan? Do you know better how to access the data from the disks and whether certain indexes should be used, especially as data is added and deleted etc. compared to letting the optimiser do it's job? I very much doubt it.

    Most hints are great for debugging performance issues to try and see where there may be a fault in the database design or a fault in the way an SQL query has been structured, but rarely should hints be used in production code (with the exception of a small number of hints such as APPEND, PARALLEL etc.)

    So, rather than ask how to use hints, why not explain what the issue is you are trying to resolve?

    If it's a performance issue, read the two threads linked to by the FAQ: {message:id=9360003}

    Also make sure you read the FAQ: {message:id=9360002} so that you can help people to understand your issue, and then you'll get better answers.
  • 3. Re: a question about hints
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    This is not the way to ask a question.

    Please read SQL and PL/SQL FAQ

    If you have a performance issue have a look at SQL and PL/SQL FAQ

    Before using a hint you should post the information regarding current explain plan and what you see wrong in that.

    Regards.
    Al
  • 4. Re: a question about hints
    969801 Newbie
    Currently Being Moderated
    sorry, it's only a example!

    because i have a sql runing in oracle 9i ,it's very slow ! but it fast on 11g;


    and i found the difference in the explain;


    so what i want to know is that :

    if i have a view,how can change the order of join ?

    if i have a view like this(example)
      
                         create view v1 as  select a.* from a ,(select b.col1 from b,c where b.col1=c.col2) where a.col=b.col1
    
                  
                  i have a sql  like this
    
    
                           select * from v1 ,d where v1.col=d.col
    
    
    
                    i try this one can do               /*+use_hash(view_name.a d) leading(view_name.a)*/  --it works ! but in another sql !  
    
                          
                  
      
    if i want b first join with a,then join with c, how to ?


    best regards!!!!
  • 5. Re: a question about hints
    969801 Newbie
    Currently Being Moderated
    this isn't a performance issue; thk you any way !



    i just want to know the way how to use hints in a view!!!
  • 6. Re: a question about hints
    969801 Newbie
    Currently Being Moderated
    thk you adv !!! i didn't post a goog question!!


    in a view ,how to use hint ! if the view is complex view?
  • 7. Re: a question about hints
    BluShadow Guru Moderator
    Currently Being Moderated
    966798 wrote:
    sorry, it's only a example!

    because i have a sql runing in oracle 9i ,it's very slow ! but it fast on 11g;


    and i found the difference in the explain;

    Yes, because the internals of the SQL engine work differently in both versions, and the optimiser is very different, likely using rule based optimisation in 9i and cost based optimisation in 11g.

    Adding hints to your 9i query to try and make the explain plans similar won't necessarily make it faster.
    If your problem is the peformance of a query in 9i, you need to identify what the cause of that is (ignoring the fact the same runs quicker in 11g) and focus on sorting out the underlying cause.
    (or just upgrade all unsupported 9i instances to 11g, after all 9i was replaced around 10 years ago by 10g, and in computing terms that's 'old technology' now.)
  • 8. Re: a question about hints
    AlbertoFaenza Expert
    Currently Being Moderated
    966798 wrote:
    this isn't a performance issue; thk you any way !
    If you are writing in another post:
    966798 wrote:
    because i have a sql runing in oracle 9i ,it's very slow ! but it fast on 11g;
    Then this is a performance issue.
    Query very slow = perfomance issue in my dictionary.
    Am I saying something wrong?

    Regards.
    Al
  • 9. Re: a question about hints
    969801 Newbie
    Currently Being Moderated
    sorry, i didn't discribe clearly!

    i had upgraded my db to 11g!

    but i have a test db that is 9i ;

    i donot need to perfomance that sql at all!




    can you help me: how to use hints to tell CBO how to join in a complex view!!

    sorry again!


    best regards!!!
  • 10. Re: a question about hints
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    as you are in Oracle 9i I will give you the link to documentation of this version:
    Optimizer Hints

    You will find at a certain point of this page:

    <h2>Using Hints Against Views</h2>
    By default, hints do not propagate inside a complex view. For example, if you specify a hint in a query that selects against a complex view, then that hint is not honored, because it is not pushed inside the view.

    Regards.
    Al
  • 11. Re: a question about hints
    BluShadow Guru Moderator
    Currently Being Moderated
    966798 wrote:
    sorry, i didn't discribe clearly!

    i had upgraded my db to 11g!

    but i have a test db that is 9i ;

    i donot need to perfomance that sql at all!
    So what's the point in mentioning that it's slow on 9i and fast on 11g?
    can you help me: how to use hints to tell CBO how to join in a complex view!!
    You still haven't explained what issue you are trying to solve, especially if it's not a performance issue.

    There's absolutely no reason to use hints to make the CBO do some join differently unless you can provide us with some evidence and justification as to why you think there is.
    If you've upgraded to 11g and the query is working performant, what are you actually trying to fix?
  • 12. Re: a question about hints
    969801 Newbie
    Currently Being Moderated
                
         this is a part of the sql and it's explain
    ---------------------------------------------------------------------------------------------
    | Id  | Operation                    |  Name           | Rows  | Bytes |TempSpc| Cost (%CPU)|
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                 |     5 |   715 |       |   891   (2)|
    |*  1 |  COUNT STOPKEY               |                 |       |       |       |            |
    |   2 |   VIEW                       |                 | 13077 |  1826K|       |            |
    |*  3 |    SORT ORDER BY STOPKEY     |                 | 13077 |  2158K|  4664K|   891   (2)|
    |*  4 |     HASH JOIN OUTER          |                 | 13077 |  2158K|       |   403   (2)|
    |*  5 |      HASH JOIN OUTER         |                 | 13077 |  1826K|       |   396   (2)|
    |*  6 |       HASH JOIN              |                 | 13077 |  1775K|       |   389   (1)|
    |*  7 |        TABLE ACCESS FULL     | T_ZHIDAO        |  2136 |   156K|       |   111   (1)|
    |   8 |        VIEW                  | V_INDEXNAME     | 11467 |   716K|       |            |
    |   9 |         UNION-ALL            |                 |       |       |       |            |
    |  10 |          NESTED LOOPS        |                 |  2845 |   236K|       |   196   (2)|
    |* 11 |           TABLE ACCESS FULL  | T_CUSTOMER      |  2845 |   133K|       |   194   (1)|
    |* 12 |           INDEX RANGE SCAN   | PK_CS_C_UID     |     1 |    37 |       |            |
    |  13 |          NESTED LOOPS        |                 |  2249 |   208K|       |    57   (4)|
    |* 14 |           TABLE ACCESS FULL  | T_BARBERSHOP    |  2249 |   127K|       |    56   (2)|
    |* 15 |           INDEX RANGE SCAN   | PK_BS_B_UID     |     1 |    37 |       |            |
    |* 16 |          TABLE ACCESS FULL   | T_OTHERMEMBER   |    36 |  1908 |       |     4  (25)|
    |  17 |          NESTED LOOPS        |                 |  6337 |   526K|       |   225   (3)|
    |* 18 |           TABLE ACCESS FULL  | T_HAIRSTYLIST   |  6337 |   297K|       |   221   (1)|
    |* 19 |           INDEX RANGE SCAN   | PK_HS_H_UID     |     1 |    37 |       |            |
    |  20 |       VIEW                   |                 |   159 |   636 |       |            |
    |  21 |        SORT GROUP BY         |                 |   159 |  1113 |       |     7  (29)|
    |* 22 |         TABLE ACCESS FULL    | T_ZHIDAOANSWER  |   278 |  1946 |       |     6  (17)|
    |* 23 |      VIEW                    |                 |   278 |  7228 |       |            |
    |* 24 |       WINDOW SORT PUSHED RANK|                 |   278 |  3336 |       |     7  (29)|
    |  25 |        TABLE ACCESS FULL     | T_ZHIDAOANSWER  |   278 |  3336 |       |     6  (17)|
    
    
    ---------------------------------------------------------------------------------------------
    
    
    
    after  i use hints ,the explain  changed , and it run much faster
    
    
     
    ------------------------------------------------------------------------------------------
    | Id  | Operation                         |  Name           | Rows  | Bytes | Cost (%CPU)|
    ------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                  |                 |  1702 |   219K|   673  (42)|
    |   1 |  SORT ORDER BY                    |                 |  1702 |   219K|   673  (42)|
    |   2 |   SORT GROUP BY                   |                 |  1702 |   219K|   673  (42)|
    |   3 |    NESTED LOOPS                   |                 |  1702 |   219K|   671  (42)|
    |*  4 |     HASH JOIN                     |                 |   278 | 18904 |   117   (2)|
    |   5 |      TABLE ACCESS FULL            | T_ZHIDAOANSWER  |   278 |  1112 |     6  (17)|
    |   6 |      TABLE ACCESS FULL            | T_ZHIDAO        |  4272 |   267K|   111   (1)|
    |   7 |     VIEW                          | V_INDEXNAME     |     6 |   384 |            |
    |   8 |      UNION-ALL PARTITION          |                 |       |       |            |
    |   9 |       NESTED LOOPS                |                 |     1 |    85 |     3  (34)|
    |* 10 |        INDEX UNIQUE SCAN          | PK_CS_C_UID     |     1 |    37 |     2  (50)|
    |* 11 |        TABLE ACCESS BY INDEX ROWID| T_CUSTOMER      |     1 |    48 |     2  (50)|
    |* 12 |         INDEX UNIQUE SCAN         | PK_C_UID        |     1 |       |            |
    |  13 |       NESTED LOOPS                |                 |     1 |    95 |     3  (34)|
    |* 14 |        INDEX UNIQUE SCAN          | PK_BS_B_UID     |     1 |    37 |     2  (50)|
    |* 15 |        TABLE ACCESS BY INDEX ROWID| T_BARBERSHOP    |     1 |    58 |     2  (50)|
    |* 16 |         INDEX UNIQUE SCAN         | PK_B_UID        |     1 |       |            |
    |* 17 |       TABLE ACCESS BY INDEX ROWID | T_OTHERMEMBER   |     1 |    53 |     2  (50)|
    |* 18 |        INDEX UNIQUE SCAN          | PK_OM_UID       |     1 |       |            |
    |  19 |       NESTED LOOPS                |                 |     1 |    85 |     3  (34)|
    |* 20 |        INDEX UNIQUE SCAN          | PK_HS_H_UID     |     1 |    37 |     2  (50)|
    |* 21 |        TABLE ACCESS BY INDEX ROWID| T_HAIRSTYLIST   |     1 |    48 |     2  (50)|
    |* 22 |         INDEX UNIQUE SCAN         | PK_H_UID        |     1 |       |            |
    ------------------------------------------------------------------------------------------
     
    and also i don't know why the CBO choose  the first  explain ! 
    
        i have gather the statistics;
    
    it may useless thing to fix the sql , but i  am hungry to know  how to use hints in a view , may be i will face that in the future~ 
    
    
    thk you!
    
           

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points