Re: Re - new datablade announced for informix

Neil Truby apparently said,on my timestamp of 23/06/2004 9:59 AM:

>>>Ummm... I read
>>>http://www.coppereye.com/coppereye_datablade.htm
>>>and it looks very interesting, indeed.
>>>

Thanks, an interesting link.

>
> I've deliberatly cross-posted in case I'm talking crap, to give experts in
> the other DBMSs the chance to correct me. Somthing tells me I will regret
> it though ...!

I don't see why. We can quite happily x-post if we stay within the technical realm in our posts. I'm all for sharing tech info. Rather than the stupid marketing "mine is bigger than yours" crap. Which rubs me and a lot of others the wrong way.

Oracle does have a feature to install and use externally-handled indexes, since I believe 9i. It was used to create the weird indexing features for the text extensions. It can be used very much for the same purpose, or so I'm told. Not to say that it has, mind you.

To stay within the realm of the large data loads and their impact on indexes, the normal way of handling this in Oracle (assuming you are using default B-tree indexes) is to:

1- partition the table and indexes so that each large load goes to its own partition (data + local indexes). This avoids any need to re-build very large indexes or the overhead in maintaining such.

2- load the data into a new partition without any local indexes defined.

3- build the local partition indexes at the end of the load, in one fast parallel create operation. They are now usable by the optimizer together with other local indexes on the same columns in other partitions of that table.

Then it's all up to the optimizer to do the right thing for you: parallel partition elimination and then local index usage. Which is an entirely different matter altogether.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam