Skip to main content

Search

Welcome to Tibero GTS!

Controlling View Execution Plans Using Hints

Document Type | Technical Information

Category | Tuning

Applicable Product Versions | 6FS05, 6FS06, 6FS07, 6FS07PS, 7FS01, 7FS02, 7FS02PS

Document Number | TTUTI013

 

Overview

In queries that call a view, hints can be used for tables with aliases defined within the view. (Global hints)
This guide explains how to control the execution plan of a view using hints.
 
 

Method

Example of Writing Hints

  • leading([view alias].[table alias within the view])
  • index([view alias].[table alias within the view] [index name]) */
 

Example of Using Hints

1.  Creating Test Objects

create table tb1(c1 number, c2 number, c3 number);
create unique index tb1_pk on tb1(c1);
alter table tb1 add constraint tb1_pk primary key(c1);
create table tb2(c1 number, c2 number, c3 number);
create unique index tb2_pk on tb2(c1);
alter table tb2 add constraint tb2_pk primary key(c1);
create index tb_idx1 on tb1(c1, c2);
create view v_test as
select t1.c1 t1c1, t1.c2 t1c2, t2.c1 t2c1, t2.c2 t2c2
from tb1 t1, tb2 t2
where t1.c1 = t2.c1;

Original SQL Execution Plan
SELECT * FROM V_TEST VT WHERE VT.T1C1 = 1;

Execution Plan
------------------------------------------------------------------
  1  NESTED LOOPS (Cost:5, %%CPU:0, Rows:1)
  2    TABLE ACCESS (ROWID): TB2 (Cost:3, %%CPU:0, Rows:1)
  3      INDEX (UNIQUE SCAN): TB2_PK (Cost:2, %%CPU:0, Rows:1)
  4    INDEX (RANGE SCAN): TB_IDX1 (Cost:2, %%CPU:0, Rows:1)
Predicate Information
--------------------------------------------------------------------------------
  1 - access: ("T1"."C1" = "T2"."C1") (1.000)
  3 - access: ("T1"."C1" = 1) (0.010)
  4 - access: ("T1"."C1" = 1) (0.010)

 

2.  Applying Hints

(1) leading Hint
The execution plan changes so that the T1 alias table within the view is processed first.
SELECT /*+ leading(VT.T1) */ * FROM V_TEST VT WHERE VT.T1C1 = 1;
Execution Plan
--------------------------------------------------------------------------------
  1  NESTED LOOPS (Cost:5, %%CPU:0, Rows:1)
  2    INDEX (RANGE SCAN): TB_IDX1 (Cost:2, %%CPU:0, Rows:1)
  3    TABLE ACCESS (ROWID): TB2 (Cost:3, %%CPU:0, Rows:1)
  4      INDEX (UNIQUE SCAN): TB2_PK (Cost:2, %%CPU:0, Rows:1)
Predicate Information
--------------------------------------------------------------------------------
  1 - access: ("T1"."C1" = "T2"."C1") (1.000)
  2 - access: ("T1"."C1" = 1) (0.010)
  4 - access: ("T1"."C1" = 1) (0.010)

(2) Index Hint
The execution plan changes so that the T1 alias table within the view uses the TB1_PK index.
SELECT /*+ index(VT.T1 TB1_PK) */ * FROM V_TEST VT WHERE VT.T1C1 = 1;
Execution Plan
--------------------------------------------------------------------------------
  1  NESTED LOOPS (Cost:6, %%CPU:0, Rows:1)
  2    TABLE ACCESS (ROWID): TB2 (Cost:3, %%CPU:0, Rows:1)
  3      INDEX (UNIQUE SCAN): TB2_PK (Cost:2, %%CPU:0, Rows:1)
  4    TABLE ACCESS (ROWID): TB1 (Cost:3, %%CPU:0, Rows:1)
  5      INDEX (UNIQUE SCAN): TB1_PK (Cost:2, %%CPU:0, Rows:1)
Predicate Information
--------------------------------------------------------------------------------
  1 - access: ("T1"."C1" = "T2"."C1") (1.000)
  3 - access: ("T1"."C1" = 1) (0.010)
  5 - access: ("T1"."C1" = 1) (0.010)
Caution
Do not assign the same alias inside the view when creating the view.
 
 
 

Related to