how to pass a hint for query which fetches data from multiple tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: how to pass a hint for query which fetches data from multiple tables

  1. #1
    Join Date
    Jun 2003
    Location
    australia
    Posts
    74

    how to pass a hint for query which fetches data from multiple tables

    Hi all

    Can any one tell me how to pass a hint for a query, which fetches data from multiple tables.

    for example ::

    select e.ename, d.dname, b.comm from
    emp e, dept d, bonus b
    where e.deptno=d.deptno
    and e.ename=b.ename
    and b.comm<2000;

    Lets say we have the following indexes ::

    Table Index
    ------- -------
    EMP EMPNO
    BONUS ENAME
    DEPT DEPTNO

    Thanks in Advance.

    raj
    rajorcl

  2. #2
    Join Date
    Jun 2000
    Posts
    295
    select /*+ your_hint */ ...
    from a_table_or_10_tables
    ...

  3. #3
    Join Date
    Jun 2003
    Location
    australia
    Posts
    74
    very nice, but if i use empno index then there is no empno index on bonus table so it does a full table scan on bonus table.
    I'm usnig something like this:
    ex:
    select /*+ index (emp EMPNO)*/e.ename, d.dname, b.comm from
    emp e, dept d, bonus b
    where e.deptno=d.deptno
    and e.ename=b.ename
    and b.comm<2000;

    It does a index scan on emp but full scan on bonus.

    Regards

    raj
    rajorcl

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    select /*+ index(emp EMPNO) index(bonus comm) */
    e.ename, d.dname, b.comm from
    emp e, dept d, bonus b
    where e.deptno=d.deptno
    and e.ename=b.ename
    and b.comm<2000;


    Not that i think it's a good idea, mind.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jun 2003
    Location
    australia
    Posts
    74
    thnx Slim. Can I know why its not a good idea???? I'm a chicken in this industry, hope you understand.

    Thnx once again
    rajorcl

  6. #6
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    I guess what Slimdave is trying to say is, that hints are only valid for a specific moment. If the load of the table changes, the hint must be altered. This makes your life as a dba harder (in other words: you must work harder )
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I would do more investigation into why performance is not-so-good without the index hints before resorting to them -- I'm assuming there that you have verfified that the hinted query really is better than the non-hinted one, and you have gathered statistics on the tables, indexed columns, and indexes.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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


Click Here to Expand Forum to Full Width