Yes, I’m back…but on an adhoc basis as I still don’t have enough time for this really! Yeah – I know, you’re all in the same boat 😉
I came across this problem on the warehouse the other day and was slightly surprised by what was going on…
We have an OWB mapping which does a TRUNCATE and then INSERT APPEND into a target table. The target table has some bitmap indexes on it. We run a little preprocess before calling the mapping to mark the bitmaps as unusable so it doesn’t maintain them and then we (hope to) get better performance during the INSERT APPEND…we then rebuild the indexes afterwards more efficiently than maintaining them on the fly…sounds simple enough…except that performance was dragging…so I had a quick shufty and observed that the indexes were still marked as USABLE. Finally, no raindogscine.com viagra active place is the placebo effect stronger then is the area of libido, but we won’t go there… You may find lot of herbal pills in the cheapest brand cialis loved this denomination of 240, 180, 60 and 120 capsules. viagra prescription Normal blood flow to the penile area can be relaxed which is crucial to solve the annoying problem of impotency. If the heart beats too quickly, blood pressure may fall because there isn’t enough time for the heart cheap viagra mastercard to refill in between each beat (diastole). I asked the guy running the stuff to check the run script and he said “No, we marked them as UNUSABLE before we ran the mapping – I’m sure of it”.
That’s strange I thought…time for some debug…which proved that we were setting the indexes UNUSABLE but somehow by the time the mapping was running they had miraculously been marked as USABLE.
I decided to create a simple test script to run in SQL*Plus which would be doing the same things as the OWB mapping – to try and repeat it at the SQL level and also to prove/disprove whether OWB was having some affect.
It’s a useful thing to do this in any case as it’s helpful if you’re going to end up:
A) Testing it on other platforms/versions
B) Sending it to Oracle Support via an SR.
(Excuse formatting – either Blogger is crap or I don’t understand how to get this to look nice -take your pick)
drop table j4134_test_bmi;
create table j4134_test_bmi(col1 number not null
,col2 number not null);
create unique index jtb_pk_i on j4134_test_bmi(col1);
alter table j4134_test_bmi add constraint jtb_pk primary key(col1) using index;
create bitmap index jtb_bmi on j4134_test_bmi(col2);
select index_name,index_type,status,partitioned from user_indexes where table_name=’J4134_TEST_BMI’;
alter index jtb_bmi unusable;
alter table j4134_test_bmi disable constraint jtb_pk;
alter index jtb_pk_i unusable;
select index_name,index_type,status,partitioned from user_indexes where table_name=’J4134_TEST_BMI’;
truncate table j4134_test_bmi;
select index_name,index_type,status,partitioned from user_indexes where table_name=’J4134_TEST_BMI’;
insert /*+ append */ into j4134_test_bmi(col1,col2)
select rownum
, 100
from (select level l from dual connect by level
commit;
select index_name,index_type,status,partitioned from user_indexes where table_name=’J4134_TEST_BMI’;
A section of the output of the script follows:
Index IndexName Type STATUS PAR
—————————— —— ——– —
JTB_PK_I NORMAL UNUSABLE NO
JTB_BMI BITMAP UNUSABLE NO
2 rows selected.
Elapsed: 00:00:00.06
Table truncated.
Elapsed: 00:00:00.11
Index IndexName Type STATUS PAR
—————————— —— ——– —
JTB_PK_I NORMAL VALID NO
JTB_BMI BITMAP VALID NO
Which shows that immediately after the TRUNCATE command was issued, the previously UNUSABLE indexes were suddenly marked as USABLE!
Why ? It’s not documented anywhere that this would happen…so I created an SR with Oracle Support who initially created a Bug for it…but Development came back to say that it is “Expected behaviour”. The reasoning Support give for this being expected is that when the Table is truncated, the associated Index segments are also truncated…and if they have no data in them then they can’t possibly be UNUSABLE so they get marked as USABLE as a side effect of the process.
It’s actually a reasonable assumption for them (Oracle development) to make to be fair – but it would have helped if it was documented – they’re going to create a Metalink support note to document the issue.
Thanks to Doug for pushing me to blog this – I’ll have to buy you a drink at the OUG Scotland Conference 2006!
You been reading my draft posts??? 😉
I was going to mention this in my post tonight! still might though – but with a link to here
…yes, telepathy is great once you master it!
It’s actually a reasonable assumption for them (Oracle development) to make to be fair – but it would have helped if it was documented – they’re going to create a Metalink support note to document the issue I’m not to sure it is reasonable! Do disabled indexes rebuild on a commit or even an insert? No!
I have a feeling it was someone being ‘helpful’ – there’s no data in this table, let’s clear the index out too.
I take your point but I’m not sure I agree with your example Pete – When they truncate the data segment, they also truncate the index segments – that sounds reasonable since if you’re telling Oracle you don’t want the data there is no point in keeping the index information either.
Given that, the assumption that they then make in saying “well, the index is now empty and an empty index can’t possibly be unusable” so we’ll mark it usable whatever it might have been before is “reasonable”…but as I say, it’s a gotcha if you didn’t realise it was going to happen…and even if you read the manual you would not have known it would happen since it’s not documented behaviour – at least they’ll be fixing that.
The issue doesn’t really involve index rebuilds per se in my view. You’re also comparing an example of DML/DCL to one of DDL operation which is perhaps unfair.
I would say it’s probably “more reasonable” to leave the USABLE attribute of the index to what is was prior to the TRUNCATE operation…but I can see their reasoning.
Empty index segments _can_ be unusable:
SQL> create table unusable_idx_tab (a integer);
Table created.
SQL> create index idx_unusable_idx_tab on unusable_idx_tab (a);
Index created.
SQL> alter index idx_unusable_idx_tab unusable;
Index altered.
SQL> select index_name, status from user_indexes where table_name = ‘UNUSABLE_IDX_TAB’;
INDEX_NAME STATUS
—————————— ——–
IDX_UNUSABLE_IDX_TAB UNUSABLE
TRUNCATE has resetting side-effects on tables. No reason it couldn’t happen on indexes, too. But they can’t say it’s a side-effect of an empty segment. It’s a side-effect of TRUNCATE; TRUNCATE means more than just emptying the segment.
It is documented under the TRUNCATE, but the sentence looks pretty garbled :
“Oracle also automatically truncates and resets any existing UNUSABLE indicators for the following indexes on table: range and hash partitions of local indexes and subpartitions of local indexes.”
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_107a.htm#2067573
Thanks for the comments guys.
Gary – that’s for (sub)partitioned indexes – my example was for a non partitioned index/table where I originally found the issue and that statement doesn’t therefore apply – as it reads currently.
Interestingly, the statement below that one in the documentation (your link was 9i but same is seen in 10gR2 docs) reads as if it will leave the partitions UNUSABLE…irrespective of prior state…but that’s the opposite to what I’ve seen/reported:
“If table is not empty, then Oracle marks UNUSABLE all nonpartitioned indexes and all partitions of global partitioned indexes on the table.”
Mr Ed – thanks for the sample and I understand your point but I guess their (Oracle support) point is that why would an empty index be unusable in any circumstance – so they set it to USABLE.
Some interesting points have been raised here and I guess I’m now torn now between it being reasonable behaviour or not – either way, I still don’t think the documentation is particularly clear/correct/helpful here so hopefully the SR I’ve raised will help to get this clarified at least.
We were bittne by the same issue quite some time ago — loads slowed to a crawl and it turned out that “someone” (not me!) had reversed the order of the truncate and the alter-iondex-unusable statements in our standard pre-load procedure, hence we were loading (through conventional path at the time) into a table with 16 bitmap indexes on it (if memory serves). I believe there was a hypothesis floated and discussed that truncating an unusable index would be faster tyhan truncating a usable one — a hypothesis never actually tested, of course.