RE: using hints on views

From: Larry Elkins <elkinsl_at_flash.net>
Date: Fri, 04 Jan 2002 07:33:27 -0800
Message-ID: <F001.003E6E21.20020104064024@fatcity.com>

Maria,

Witold has provided some good advice so no need to repeat any of that.

But, since you are asking about hints and views it is worth mentioning the concept of GLOBAL hints. When you want to specify hints for tables that appear *inside* views you can use GLOBAL hints. So, you might find this to be a nice approach to your needs. Note that this isn't going to help with an ORDERED hint.

Anyway, following is an example of a view that was created with hints and then using GLOBAL hints to override and change the plan for the view:

Create a view and notice the use of the hint to use the PK index on the Code_Master (CM) table:

1 create or replace view v_code as
2 select /*+ ORDERED INDEX (CM CODE_MASTER_PK) */ 3 cm.code, cm.foo_date, cd.codedate, cd.t1, cd.t2 4 from code_master cm, code_detail cd 5* where cm.code = cd.code
SQL> / View created.

2) Now, when selecting from the view and not specifying criteria, the PK index is still used, doing an INDEX (FULL SCAN). Obviously, we wouldn't want this approach when not specifying criteria:

SQL> select * from v_code;

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5697 Card=299600 Bytes=8988000)

1 0 MERGE JOIN (Cost=5697 Card=299600 Bytes=8988000) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CODE_MASTER' (Cost=340Card=100000 Bytes=1100000)

3 2 INDEX (FULL SCAN) OF 'CODE_MASTER_PK' (UNIQUE) (Cost=188 Card=100000)

   4    1     SORT (JOIN) (Cost=5357 Card=299600 Bytes=5692400)
   5    4       TABLE ACCESS (FULL) OF 'CODE_DETAIL' (Cost=234 Card=299600

Bytes=5692400)

3) Now we use a GLOBAL hint to override the index hint in the view (note that I qualify the target table using view.table/alias notation, v_code.cm). Note in the plan that the index is no longer used due to the GLOBAL hint specifying FULL on CM:

1 select /*+ FULL (v_code.cm) */ *
2* from v_code
SQL> / Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6315 Card=299600 Bytes=8988000)

1 0 MERGE JOIN (Cost=6315 Card=299600 Bytes=8988000)

   2    1     SORT (JOIN) (Cost=958 Card=100000 Bytes=1100000)
   3    2       TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=65 Card=100000
Bytes=1100000)
   4    1     SORT (JOIN) (Cost=5357 Card=299600 Bytes=5692400)
   5    4       TABLE ACCESS (FULL) OF 'CODE_DETAIL' (Cost=234 Card=299600

Bytes=5692400)

4) Now, lets go ahead and force a HASH JOIN instead of the MERGE JOIN. This is accomplished once again through a GLOBAL HINT and uses the same notation as before. As you can see from the plan, a HASH JOIN instead of a MERGE JOIN is now used:

1 select /*+ FULL (v_code.cm) USE_HASH(v_code.cd) */ * 2* from v_code
SQL> / Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13319 Card=299600 Bytes=8988000)

1 0 HASH JOIN (Cost=13319 Card=299600 Bytes=8988000) 2 1 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=65 Card=100000 Bytes=1100000)

3 1 TABLE ACCESS (FULL) OF 'CODE_DETAIL' (Cost=234 Card=299600 Bytes=5692400)

Anyway, since you were asking about views and hints, I thought mentioning GLOBAL hints might be a good idea. In your case, you might find some use for this technique.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Maria
> Aurora VT de la Vega
> Sent: Thursday, January 03, 2002 8:55 PM
> To: Multiple recipients of list ORACLE-L
> Subject: using hints on views
>
>
> i find the ordered hint extremely helpful...
> so i usually find myself using this hint...
>
> what is your opinion on using hints inside views?
>
> thanks.
>
> --
> Maria Aurora VT de la Vega (OCP)
> Database Specialist
> Philippine Stock Exchange, Inc.
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Maria Aurora VT de la Vega
> INET: mtdelavega_at_pse.org.ph
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Jan 04 2002 - 09:33:27 CST