Forming table references in optimization guidelines
The term table reference is used to mean any table, view, table expression, or the table which an alias references in an SQL statement or view definition. An optimization guideline can identify a table reference using either its exposed name in the original statement or the unique correlation name that is associated with the table reference in the optimized statement.
Extended names, which are sequences of exposed names, help to uniquely identify table references that are embedded in views. An alias name cannot be used as a table reference in an optimization guideline, in such a case any guideline targeting the table reference will be ignored. Optimization guidelines that identify exposed or extended names that are not unique within the context of the entire statement are considered ambiguous and are not applied. Moreover, if more than one optimization guideline identifies the same table reference, all optimization guidelines identifying that table reference are considered conflicting and are not applied. The potential for query transformations makes it impossible to guarantee that an exposed or extended name will still exist during optimization; therefore, any guideline that identifies such table references is ignored.
Index names are specified by using the INDEX or IXNAME attribute on some optimization guideline elements. Index names must be unqualified.
Using exposed names in the original statement to identify table references
A table reference is identified by using the exposed name of the table. The exposed name is specified in the same way that a table would be qualified in an SQL statement.
select s_name, s_address, s_phone, s_comment from parts, suppliers, partsupp ps where p_partkey = ps.ps_partkey and s.s_suppkey = ps.ps_suppkey and p_size = 39 and p_type = 'BRASS'
TABLE attribute values that identify a table reference in the statement include '"Samp".PARTS', 'PARTS', 'Parts' (because the identifier is not delimited, it is converted to uppercase characters). TABLE attribute values that fail to identify a table reference in the statement include '"Samp2".SUPPLIERS', 'PARTSUPP' (not an exposed name), and 'Samp.PARTS' (the identifier Samp must be delimited; otherwise, it is converted to uppercase characters).
The exposed name can be used to target any table reference in the original statement, view, SQL function, or trigger.
Using exposed names in the original statement to identify table references in views
The IXSCAN access request element specifies that an index scan is to be used for the EMPLOYEE table reference that is embedded in the views "Gustavo".V2 and "Rick".V1. The extended syntax for identifying table references in views is a series of exposed names separated by a slash character. The value of the TABLE attribute A/"Rick".V1/A illustrates the extended syntax. The last exposed name in the sequence (A) identifies the table reference that is a target of the optimization guideline. The first exposed name in the sequence (A) identifies the view that is directly referenced in the original statement. The exposed name or names in the middle ("Rick".V1) pertain to the view references along the path from the direct view reference to the target table reference. The rules for referring to exposed names from optimization guidelines, described in the previous section, apply to each step of the extended syntax.
create view "Rick".v1 as (select * from employee a where salary > 50000) create view "Gustavo".v2 as (select * from "Rick".v1 where deptno in ('52', '53', '54') select * from "Gustavo".v2 a where v2.hire_date > '01/01/2004' <OPTGUIDELINES> <IXSCAN TABLE='A/"Rick".V1/A'/> </OPTGUIDELINES>
Had the exposed name of the EMPLOYEE table reference in the view been unique with respect to all tables that are referenced either directly or indirectly by the statement, the extended name syntax would not be necessary.
Extended syntax can be used to target any table reference in the original statement, SQL function, or trigger.
Identifying table references using correlation names in the optimized statement
An optimization guideline can also identify a table reference using the unique correlation names that are associated with the table reference in the optimized statement. The optimized statement is a semantically equivalent version of the original statement, as determined during the query rewrite phase of optimization. The optimized statement can be retrieved from the explain tables. The TABID attribute of an optimization guideline is used to identify table references in the optimized statement.
This optimization guideline shows a hash join request, where the SUPPLIERS table in the nested subselect is the outer table, as specified by the TBSCAN access request element, and where the PARTSUPP table in the nested subselect is the inner table, as specified by the IXSCAN access request element. The TBSCAN access request element uses the TABLE attribute to identify the SUPPLIERS table reference using the corresponding exposed name in the original statement. The IXSCAN access request element, on the other hand, uses the TABID attribute to identify the PARTSUPP table reference using the unique correlation name that is associated with that table reference in the optimized statement.
Original statement: select s.s_name, s.s_address, s.s_phone, s.s_comment from sm_samp.parts p, sm_samp.suppliers s, sm_samp.partsupp ps where p_partkey = ps.ps_partkey and s.s_suppkey = ps.ps_suppkey and p.p_size = 39 and p.p_type = 'BRASS' and s.s_nation in ('MOROCCO', 'SPAIN') and ps.ps_supplycost = (select min(ps1.ps_supplycost) from sm_samp.partsupp ps1, sm_samp.suppliers s1 where p.p_partkey = ps1.ps_partkey and s1.s_suppkey = ps1.ps_suppkey and s1.s_nation = s.s_nation) <OPTGUIDELINES> <HSJOIN> <TBSCAN TABLE='S1'/> <IXSCAN TABID='Q2'/> </HSJOIN> </OPTGUIDELINES> Optimized statement: select q6.s_name as "S_NAME", q6.s_address as "S_ADDRESS", q6.s_phone as "S_PHONE", q6.s_comment as "S_COMMENT" from (select min(q4.$c0) from (select q2.ps_supplycost from sm_samp.suppliers as q1, sm_samp.partsupp as q2 where q1.s_nation = 'MOROCCO' and q1.s_suppkey = q2.ps_suppkey and q7.p_partkey = q2.ps_partkey ) as q3 ) as q4, sm_samp.partsupp as q5, sm_samp.suppliers as q6, sm_samp.parts as q7 where p_size = 39 and q5.ps_supplycost = q4.$c0 and q6.s_nation in ('MOROCCO', 'SPAIN') and q7.p_type = 'BRASS' and q6.s_suppkey = q5.ps_suppkey and q7.p_partkey = q5.ps_partkey
Ambiguous table references
An optimization guideline is considered invalid and is not applied if it matches multiple exposed or extended names.
The optimizer considers the IXSCAN access request ambiguous, because the exposed name EMPLOYEE is not unique within the definition of view V1.
create view v1 as (select * from employee where salary > (select avg(salary) from employee) select * from v1 where deptno in ('M62', 'M63') <OPTGUIDE> <IXSCAN TABLE='V1/EMPLOYEE'/> </OPTGUIDE>
To eliminate the ambiguity, the view can be rewritten to use unique correlation names, or the TABID attribute can be used. Table references that are identified by the TABID attribute are never ambiguous, because all correlation names in the optimized statement are unique.
Conflicting optimization guidelines
Multiple optimization guidelines cannot identify the same table reference.
Each of the IXSCAN elements references the "Samp".PARTS table in the main subselect.
<OPTGUIDELINES> <IXSCAN TABLE='"Samp".PARTS' INDEX='I_PTYPE'/> <IXSCAN TABLE='"Samp".PARTS' INDEX='I_SIZE'/> </OPTGUIDELINES>
When two or more guidelines refer to the same table, only the first is applied; all other guidelines are ignored, and an error is returned.
<OPTGUIDELINES> <INLIST2JOIN TABLE='P' COLUMN='P_SIZE'/> <INLIST2JOIN TABLE='P' COLUMN='P_TYPE'/> </OPTGUIDELINES>