Informix Dynamic Server 11: Advanced Functionality for Modern Business

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