Chapter 9. SQL Language 283
returning int;
LET n.col1 = n.col1 * 1.1 ; -- increment the inserted value 10%
end procedure;
-- use the proc1 inside for each row trigger.
create trigger ins_trig_tab1 INSERT on tab1 referencing new as post
for each row(execute procedure proc1() with trigger references);
-- use the proc1 inside for each row trigger.
create trigger ins_trig_tab2 INSERT on tab2 referencing new as n
for each row (execute procedure proc2() with trigger references);
insert into tab1 values (111,222);
The above statement will execute ins_trigger trig_tab1 and therefore will
execute procedure proc1(). The procedure will increment the value of col1
by 1. So, the value inserted will be (112, 222).
insert into tab2 values (100);
The above statement will execute ins_trigger trig_tab2 and therefore will
execute procedure proc2(). The procedure will increment the value of col1
by 10%. So, the value inserted into tab2 will be 110.
A procedures created with a REFERENCING clause can only be called from a
FOR EACH ROW trigger and has to be executed using the WITH TRIGGER
REFERENCES modifier to execute procedure statement. IDS raises an error for
all other forms of invocation.
9.13 Index self-join access method
Traditionally, an index scan allows you to scan a single range (based on the
start/stop key) of an index. The
index self-join access method lets you scan many
mini-ranges instead of a large single range, based on filters on non-leading keys
of an index.
The index self-join is a new type of index scan where the table is logically joined
to itself, such that for each unique combination of the leading key columns of an
index, additional filters on non-leading key columns of the index are used to
perform a more efficient mini-index scan. Results from multiple mini-index scans
are then combined to generate the result set of the query.
284 Informix Dynamic Server 11: Advanced Functionality for Modern Business
Figure 9-9 is a before and after illustration of how a sample query is handled.
This is the example query:
SELECT * FROM tab
WHERE c1 >= 1 and c1 <= 3 and c2 >= 10 and c2 <= 11 and c3 >= 100 and c3 <=
102
Figure 9-9 View of index on C1, C2, and C3
In prior releases of IDS, you were only allowed to use filters on c1 (c1 >= 1 and
c1 <= 3) for positioning of the index scan, as depicted in Figure 9-10 on
page 284.
Figure 9-10 Index scan in prior releases of IDS
With this feature, we can use filters on c2 and c3 for positioning of the index
scan, which allows us to skip unnecessary index keys at the two ends of the
leafpage leafpage leafpage leafpage leafpageleafpageleafpage
Root
Leaf level
Index Scan Region
Eliminated
range on c1
Chapter 9. SQL Language 285
index, and IDS only scans pockets of the index that are relevant, therefore,
avoiding scanning a large portion of the index. This strategy improves the query
performance by reducing the portion of the index IDS has to scan.
In Figure 9-11, we depict the scenario where:
Lead Keys: C1 and C2
Lower filter C1 = C1 and C2 = C2 and C3 >= 100
Upper filter C3 =< 102
Figure 9-11 Index self-join
Example 9-12 shows the optimizer choosing the index self-join for a query.
Example 9-12 Index self-join
QUERY:
------
select c1, c7, c11 from tab1
where c1 between 15 and 18 and c7 between 'OOOOOO' and 'SSSSSS'
and c3 > 2356 and c3 <= 2400
order by 1 desc, 2 desc
Estimated Cost: 38
Estimated # of Rows Returned: 2
1) sqlqa.tab1: INDEX PATH
(1) Index Keys: c1 c7 c3 c6 (Key-First) (Serial, fragments: ALL)
Index Self Join Keys (c1 c7 )
Lower bound: sqlqa.tab1.c1 <= 18 AND (sqlqa.tab1.c7 <= 'SSSSSS' )
Upper bound: sqlqa.tab1.c1 >= 15 AND (sqlqa.tab1.c7 >= 'OOOOOO' )
Index Scan Regions
Regions eliminated by
Index Self Join strategy

Get Informix Dynamic Server 11: Advanced Functionality for Modern Business now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.