At the end of part 2 of Index Explosion I left you with three questions:
- how can you set maxtrans on an index in 10g (and above)
- what strategies exist for minimising the potential for damage if you actually have to face this problem
- why haven’t I noticed the space-wasting phenomenon more often despite seeing indexes where it could have been happening
In this post I’ll address the maxtrans issue. If you try to set maxtrans on an index in 10g it simply doesn’t work, and here’s a quick cut-n-paste from SQL*Plus running 10.2.0.3 to demonstrate the point:
SQL> create index ord_placed on orders(date_placed) 2 tablespace test_8k 3 initrans 9 4 maxtrans 9 5 / Index created. SQL> select ini_trans, max_trans 2 from user_indexes 3 where index_name = 'ORD_PLACED' 4 / INI_TRANS MAX_TRANS ---------- ---------- 9 255
Of course if you’re still running 9i (or 8i), you’ll see max_trans showing the value you set, but 10g silently ignores your attempt to set maxtrans and leaves it at 255. If you really need to set maxtrans in 10g, and if you get approval from Oracle support, you can do something like the following (again, a cut-n-paste from SQL*Plus):
SQL> define m_maxt = 9 SQL> SQL> column object_id new_value m_objn SQL> column data_object_id new_value m_objd SQL> SQL> select 2 object_id, 3 data_object_id 4 from 5 user_objects 6 where 7 object_name = 'ORD_PLACED' 8 and object_type = 'INDEX' 9 ; OBJECT_ID DATA_OBJECT_ID ---------- -------------- 100466 100466 1 row selected. SQL> SQL> execute sys.fix_index_maxtrans( &m_objn , &m_objd, &m_maxt ) PL/SQL procedure successfully completed. SQL> SQL> select ini_trans, max_trans 2 from user_indexes 3 where index_name = 'ORD_PLACED' 4 / INI_TRANS MAX_TRANS ---------- ---------- 9 9 1 row selected.
The trick (and the requirement to talk to Oracle support) is in the procedure sys.fix_index_maxtrans(), which I created in the sys schema as follows:
create or replace procedure fix_index_maxtrans ( i_objn in number, i_objd in number, i_maxt in number ) as begin execute immediate 'alter system flush shared_pool'; execute immediate 'alter system flush shared_pool'; update ind$ set maxtrans = i_maxt where obj# = i_objn and dataobj# = i_objd ; execute immediate 'alter system flush shared_pool'; execute immediate 'alter system flush shared_pool'; end; /
There is an oddity in this procedure – you’ll notice I’ve flushed the shared_pool twice before and after updating the data dictionary. The flushes are there in the first case because Oracle sometimes updates the data dictionary from the dictionary cache; I then repeat them because there is some evidence that some items in the library cache don’t get flushed properly the first time around in some releases of Oracle. The total number of flushes may be unnecessary – but this hack for maxtrans isn’t something that you have to do often – so you may be prepared to live with the disruption.
Once the maxtrans is set it seems to persist even across an index rebuild (if you ever feel the need to use the rebuild command on an index, that is) – but if you want to take advantage of this approach, I’ll leave you to test all the boundary conditions and special cases (e.g. partitioned indexes, etc.) for yourselves.
I’ll address the other two questions in later posts but, as a brief introdution to other options for addressing the issue, here’s a list of four (or possibly five, depending on your point of view) which may be appropriate.
- Use a coalesce when necessary – possibly on a very regular basis
- Create the index as a reverse-key index
- Create the index as global hash partitioned index
- Get rid of (or change) the index
Each option has its good points, bad points, advantages and limitations – which will be the topic of discussion in the next installment.