Here’s a little oddity that may be waiting to catch you out – but only if you like to create indexes with very long keys.
| 07 | create index t1_i1 on t1(v1, v2); |
| 09 | alter index t1_i1 rebuild; |
| 10 | alter index t1_i1 rebuild online; |
My key value is at the limit for an 8KB block size in Oracle 9i and later – which is roughly 80% of (block size - 190 bytes). In earlier versions of Oracle the limit was roughly half that (i.e. 40% rather than 80%).
If you try to create a longer key you’ll see Oracle error ORA-01450: “maximum key length (6398) exceeded”. (If you’ve built your indexes using a different blocksize the number in brackets will be different – and you will have to adjust the demo code for 16KB and 32KB block sizes). The difference between my declared column lengths and the error limit relates to the overheads in an index entry – but seems to “forget” the one byte extra for non-unique indexes.
But this is the output you’ll (probably) get from running the script. if you’re using an 8KB block size:
| 07 | alter index t1_i1 rebuild online |
| 10 | ORA-00604: error occurred at recursive SQL level 1 |
| 11 | ORA-01450: maximum key length (3215) exceeded |
So I can create an index with a very large key, rebuild that index safely – and then fail when I try to rebuild the index with the online option!
What’s going on ?
When you use an online index rebuild Oracle has to create a log of all changes you make to the table while the rebuild is running. Once the log is set up, Oracle creates a new index using a read-consistent copy of the data in the table, then copies the logged changes into this index, and finishes off by tidying up the data dictionary, dropping temporary segments, and dropping the log.
This process locks the table twice, by the way, once while getting started and creating the log, and then again when finishing off and dopping the log. If you read Richard Foote’s blog regularly you will know that these locks are only held for a short time but can be hard to acquire and may lead to a randomly long delay in the rebuild.
For my purposes, though, the critical feature is the object that holds the logging information. This is created as an index organized table (IOT) with a name like sys_journal_NNNNN where NNNNN is the object_id of the index you are trying to rebuild. (You can check this by enabling sql_trace just before doing a rebuild.)
It’s the IOT that has the problem with the excess key length – its limit is still set to match the 40% limit used for earlier versions of Oracle, and the key to the IOT is the key defined for the index, plus the rowid for the row with that key.
It’s possible that this outdated limit is simply an oversight in the code; it’s possible that it’s a deliberate design decision that has to stay in place to allow for the different strategies for leaf-block splits adopted by standard B-tree indexes and the index structures supporting IOTs. Either way it’s a limitation that might finally catch you out months after you’ve created an unusually lengthy index key.
Just as a quick demonstration that the problem is in the IOT, we need only run a simple piece of DDL (the sample below was cut and pasted from a live session):
| 01 | SQL> create table iot1( |
| 05 | 5 constraint iot1_pk primary key(v1,v2) |
| 13 | ORA-01450: maximum key length (3215) exceeded |
Of course, when you see little oddities like this you might think: “I wouldn’t create an index like that – so I’m not worried”. But it’s worth spending a little time looking at the symptoms from different directions before dismissing the problem.
Try this experiment (starting with the same t1 heap table above) but see if you can guess what’s going to happen and why … before you read the explanation:
| 01 | create or replace function f (i_in varchar2) |
| 10 | create index t1_f1 on t1(f(v3)); |
| 12 | alter index t1_f1 rebuild; |
| 13 | alter index t1_f1 rebuild online; |
You can rebuild the index but you can’t rebuild it online because you get the same Oracle error ORA-01450.
You didn’t build an index with a large key deliberately, but a function returning a varchar2() implicitly returns a 4,000 byte string – so your index has been built on a virtual column of 4,000 bytes.
| 02 | column_name, column_length |
| 09 | COLUMN_NAME COLUMN_LENGTH |
| 10 | -------------------- ------------- |
If you want to build this type of index, and be able to rebuild it online, your index definition will have to be something like:
| 2 | create index t1_f1 on t1(substr(f(v3),1,100)); |
| 3 | alter index t1_f1 rebuild online; |
With this definition, the function-based index can be rebuilt online. (Of course, it may no longer be the index you want, and some of your SQL has to be modified so that the predicates match the new index definition.)
Footnote 1: The 40% limit on IOT keys is further constrained if you are using a 16KB block size by a “hard” limit of 3,800 bytes – so rebuilding the index with a different blocksize will only work around the problem in a limited range of cases.
Footnote 2: Although I implied at the start of the article that it would be unusual to have very long index keys there are quite a lot of applications that define tables with fairly long (e.g. 254, 1000) varchar2() columns: “just in case”. Watch out when you start creating indexes on these columns.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-697414/，如需转载，请注明出处，否则将追究法律责任。
2789
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
