pb with Indexes on temporary tables
John Smith's long query did not use indexes on its temporary tables. Art Kagel said a sequential scan of a small temp table that only filters a permanent table is reasonable, and the optimizer treats temp tables like permanent ones. He suggested UPDATE STATISTICS HIGH and directives such as INDEX, AVOID_INDEX, ORDERED and AVOID_FULL. Doug Lawry and Rainer von Bongartz also suggested directives.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi I have a sql statement that lasts more than two hours, it contains 2 or 3 temporary tables with some indexes for better performance but i noticed that the indexes are not used (sqlplain), it always do a seq scan on the wholes temps tables !! is there a solution or must i create a normal tables then drop them at the end ? thanks a lot ------------------------------ John Smith ------------------------------ #Informix
use Access Method Directives like : SELECT {+INDEX(your_index} ... In the example above, the access-method directive forces the optimizer to consider an execution path that scans the index on the column. Regards Rainer ------------------------------ Rainer von Bongartz ------------------------------
John: Are these temp tables large (lots of rows)? If not, then the sequential scan will be faster than using the indexes! No worries. If they are large tables, then making them permanent won't make a difference. The optimizer treats temp and permanent tables the same. Try running UPDATE STATISTICS HIGH on the tables after building the indexes. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Are you creating indexes after inserting data into the temp tables? If so, it should save distributions equivalent to UPDATE STATISTICS MEDIUM automatically on the indexed columns, and then probably make better decisions about using the indexes. If necessary, user optimizer directives to steer it: https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.perf.doc/ids_prf_554.htm ------------------------------ Doug Lawry Oninit Consulting ------------------------------
in fact, the temp table are in FROM part, but not in SELECT
create temp table tempt1 (num, name, description);
insert into tempt1 select ...........
create index temp1_num on tempt1(num);
select tab1,name, tab1.location
from tempt1, tab1
where tempt1.num = tab1.num
------------------------------John Smith
------------------------------
John: Given that the temp table is being used to filter rows from the permanent table and is not included in the projection clause, then performing a sequential scan on the temp table and using its 'num' column to look up rows in the permanent table is a perfectly reasonable query plan! But feel free to use optimizer directives to force the optimizer to drive off of the permanent table instead and look up those rows in the temp table: select {+ INDEX(tempt1 temp1_num) AVOID_INDEX(tab1) ORDERED AVOID_FULL(tempt1) } tab1,name, tab1.location from tab1, tempt1 where tempt1.num = tab1.num; Here we tell the optimizer to use the temp table index by name, not use any indexes on tab1 and I reversed the order of the tables in the FROM clause and included the ORDERED directive so that the optimizer will drive off of tab1 and perform index lookups on tempt1. Finally the AVOID_FULL directive tells the optimizer to not scan the temp table. This is a sledge hammer approach but it should force the optimizer to do things the way you imagine they should be done. Test this and see if it is faster. I suspect that it will not be. It does happen that we have a better handle on how to perform a query than the optimizer, but it is a very rare thing. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
sorry
the right starement is below :(
create temp table tempt1 (num, name, description);
insert into tempt1 select ...........
create index temp1_num on tempt1(num);
select tab1,name, tab1.location
from tempt1
inner join tab1 on (
tempt1.num = tab1.num)
------------------------------John Smith
------------------------------
John: As my grandfather was fond of saying "same difference": select {+ INDEX(tempt1 temp1_num) AVOID_INDEX(tab1) ORDERED AVOID_FULL(tempt1) } tab1,name, tab1.location from tab1 inner join tempt1 on ( tempt1.num = tab1.num); Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
ok,
let"s be some complcated :), another same statement some far
tab1 : index on column num
tab2 : index on colum col
create temp table tempt1 (num, name, description);
insert into tempt1 select ...........
create index temp1_num on tempt1(num);
select tab1,name, tab1.location , ' --' , tab2.info
from tempt1
inner join tab1 on (
tempt1.num = tab1.num)
inner join tab2 on (
tempt1.col = tab2.col)
------------------------------John Smith
------------------------------
John: For that one I would just try to suppress the scan on tempt1: select {+ AVOID_FULL( tempt1 ) } tab1,name, tab1.location , ' --' , tab2.info from tempt1 inner join tab1 on ( tempt1.num = tab1.num) inner join tab2 on ( tempt1.col = tab2.col); Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.