Informix

nested-group-icon.png

DB2

Expand all | Collapse all

pb with Indexes on temporary tables

  • 1.  pb with Indexes on temporary tables

    Posted 15 days ago
    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
    ------------------------------


  • 2.  RE: pb with Indexes on temporary tables

    Posted 15 days ago

    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
    ------------------------------



  • 3.  RE: pb with Indexes on temporary tables

    Posted 15 days ago
    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
    ------------------------------



  • 4.  RE: pb with Indexes on temporary tables

    Posted 15 days ago
    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


    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.










  • 5.  RE: pb with Indexes on temporary tables

    Posted 15 days ago
    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
    ------------------------------



  • 6.  RE: pb with Indexes on temporary tables

    Posted 15 days ago
    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


    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.










  • 7.  RE: pb with Indexes on temporary tables

    Posted 15 days ago
    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
    ------------------------------



  • 8.  RE: pb with Indexes on temporary tables

    Posted 15 days ago
    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


    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.










  • 9.  RE: pb with Indexes on temporary tables

    Posted 15 days ago
    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


    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.









  • 10.  RE: pb with Indexes on temporary tables

    Posted 15 days ago

    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
    ------------------------------