I have a table partitioned daily and subpartitioned by list on which there are two local indexes and two global unique indexes.
The maintenance job for this table runs at the weekend getting rid of the older partitions and adding some new ones for the future.
This maintenance is done with "UPDATE GLOBAL INDEXES"
Every day, an insert .. select statement populates the next day's partitions.
I'm investigating several aspects of a performance problem with this process but I have been distracted by what may be a tangential issue.
There is a reporting environment taken at a fixed point at end of play the day in which I can play with this process and investigate.
I can run this insert .. select, roll back the transaction, run it again, roll it back, etc.
There's no other DML being run on this reporting environment and certainly none which affects the objects affected by by insert ... select.
Whilst I've been making 10046 traces, I've also been capturing session stats from v$mystat and I'm intrigued by an observation.
Every day, the first time I run this statement with this new data I will get some 3000 "leaf node splits" of which 1020 are "leaf node 90-10 splits" then I roll it back.
Every subsequent time I repeat this statement and roll back, I insert the exact same data in the insert .. select but I get 1020 @"leaf node splits" and "1020 leaf node 90-10 splits'".
Any thoughts on the difference?