Index maintenance with INSERT /*+APPEND*/

 Oracle  Comments Off on Index maintenance with INSERT /*+APPEND*/
Feb 162011

(tested on
It’s Friday now, and I am watching something interesting. I will share it very briefly.

I made an empty table with the idea to fill it. Nothing uninteresting – it has just a primary key (in other words – index), a few partitions, some of them are compressed.

Then I began to fill it up with data – a few hundred million rows. I used: INSERT / * + APPEND PARALLEL (T, 8) * / INTO ...

At one point it flashed upon me: “Wow, I’ve done it wrong … I had to create the table without a primary key, to skip index maintenance during the data load”

Yes and no. My observations showed that, contrary to my expectation, the database first drew the data, all of it, then built the index. I saw this by the size of the segments – first table partitions inflated (several gigabytes), during which time the segment of the index had only its initial extent. And then the segment of the index began to grow and became a few gigabytes.

And this is not CTAS – it is INSERT.

 Posted by at 13:02