STORAGE Parameters

Hello everybody there.

It's the first time I post and read in this group, so I think I should, at first, ask if there's any kind of FAQ avaiable somewhere.

Now let's jump to the important thing.

I've got a bit of confusion with STORAGE settings of a table or tablespace, but the problem is not a "definition" one, but mostly a pratical one.

Let's define the environment first. We are talking about a good old Oracle 7.3.4.

My problem is with the INITIAL and NEXT settings of a table.

I'm loading a 50meg text file with SQL*Loader and it will result in a 21megs or something table (there are a lot of VARCHAR fields in it).

I wanted to optimize something so I tought about creating the table with an Initial of 50MB and a next of 10MB. The table gets dropped and reloaded every month of work (it is a text file exported from DB2 and we use it as the start point of some elaborations). Because the table could somewhat get bigger or smaller I tought them to be good values.

So, create table bla bla bla INITIAL 50M, NEXT 10M.

The table appears and it is using 1 Extent 50MB big.

That's right.

Let's fire up SQL*Loader... Load in Direct mode... it allocates a 10MB "temporary" segments (at least Enterprise Manager says it is so) and starts loading... but... after the load completes instead of having 1 Extent half filled, I find 4 extents. 1 is 50MB and 3 are 10MB each, and they exacly are the ones that were defined as temporary.

I read trough all the documentation in various section and right now I have the feeling that when SQL*Loader is working in Direct mode, it's way of "skipping" the Oracle Layer actually skips various controls for INITIAL emptiness and just pops up another NEXT and start filling it.

Is this correct? The documentation doesn't say anything like this, and it eventually states that "SQL*Loader automatically adds extents to the table if necessary, but this process takes time. For faster loads into new table, allocate the required extents when the table is created" (Oracle 7 Server Utilities, chapter 8-13, "Pre-allocating Storage for Faster Loading" in the "Maximizing Performances of Direct Path Loads" section).

So what's up? I preallocated 1 extent 50MB big. Why the hell isn't it using it!?

Previous DBA used a strange technique. He had ALL the INITIALS of ALL the tables 40K and just worked with the NEXT settings, but I find this to be somewhat stupid, in particular because this 50mb table is just a part of a 50GB load we have to do, and working with just the NEXT is making things really painfull, because tables fluctuates in size and we have to size down the next because sometime we run out of tablespace's space, but having a 100MB NEXT on a 5GB table seems stupid to me.

What's wrong?

Thanks for your help!

• MR2Ice Andrea Brancatelli http://andrea.brancatelli.it/ Super Keyby http://skeyby.2y.net/ andrea_at_brancatelli.it