Home » SQL & PL/SQL » SQL & PL/SQL » Adding millions of rows to a new table in 11i with unique index
Adding millions of rows to a new table in 11i with unique index [message #190430] Wed, 30 August 2006 13:04 Go to next message
mdamm
Messages: 5
Registered: August 2006
Location: Camp Hill, PA
Junior Member
I'm creating two new Oracle tables each with 8 columns. Both tables are going to get 2,500,000 records and each table has an unique index on the first six columns. These tables will probably never be updated, or added to. We have the latest and greatest version of 10g database. To load these tables I have text files each with 500,000 records and I'm using SQL*Loader to load these records into the tables. What is the BEST way to load these two tables to minimize the effects on the size of the database? Should I create the unique indexes last? Should I be defining the table parameters or should I just be defining the tablespace name and let everything else default? HELP! Doing this wrong has greatly added to the database size and our DBA is in the hospital and the network guy is losing patience with me. Thanks!
Re: Adding millions of rows to a new table in 11i with unique index [message #190437 is a reply to message #190430] Wed, 30 August 2006 13:58 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
What is the great concern over size here for? A 2.5M row table, even two of them, with only 8 columns should not take that much disk space. What are the specifics of disk consumption that you are seeing? What are the storage attributes of the tablespace?

I would create the index after the load, but for performance, not size, reasons.
Re: Adding millions of rows to a new table in 11i with unique index [message #190438 is a reply to message #190437] Wed, 30 August 2006 14:11 Go to previous messageGo to next message
mdamm
Messages: 5
Registered: August 2006
Location: Camp Hill, PA
Junior Member
Somehow adding these millions of records is adding gigabytes of size to the table space. At lest that's what they are telling me. Because I'm copying so many custom tables (40 or so) from our old 10.7 7.3.4 database into our new 10g database, I'm just copying the script generated in TOAD from 7.3.4 and using that to create the same in 10g. Probably very bad but hasn't been a problem unitl now. Is this the storage attributes for the tablespace?
XXA_DATA kbytes = 27,648,000 used = 27,589,504 Free = 58,496 Used = 99.8 Largest = 58,240 MaxPoss Databytes = 33,554,416 Max Used = 82.
Re: Adding millions of rows to a new table in 11i with unique index [message #190442 is a reply to message #190438] Wed, 30 August 2006 14:23 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
What is the actual DDL for the tablespace? This can be seen in TOAD by selecting the tablespace in the Schema Browser and clicking the Script tab on the righthand side, or through a call to:

select dbms_metadata.get_ddl('TABLESPACE', 'TABLESPACE_NAME') from dual;
Re: Adding millions of rows to a new table in 11i with unique index [message #190444 is a reply to message #190442] Wed, 30 August 2006 14:37 Go to previous messageGo to next message
mdamm
Messages: 5
Registered: August 2006
Location: Camp Hill, PA
Junior Member
Todd, When I run your SQL I get:

DBMS_METADATA.GET_DDL('TABLESPACE','XXAMES_DATA')

CREATE TABLESPACE "XXAMES_DATA" DATAFILE
'/TESTData/datafiles/xxames_data01.dbf' SIZE 1048576000
AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

I have to go to a stupid meeting for an hour. I'll be back. thanks,
Re: Adding millions of rows to a new table in 11i with unique index [message #190448 is a reply to message #190444] Wed, 30 August 2006 15:34 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Well, the initial datafile size for this tablespace is 1GB and then it will autoextend 512MB at a time.

Are there other tables associated with this TS or just the two you are trying to load?
Re: Adding millions of rows to a new table in 11i with unique index [message #190450 is a reply to message #190448] Wed, 30 August 2006 16:01 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Also, what is the definition of the table? Like Todd said, 8 columns for 2.5 millsion rows is not that big. Unless all columns are VARCHAR2(4000), then it would be roughly 256 Gigabytes. That's not to mention a unique index on 6 columns.

Is it two tables or 40 tables? You give conflicting information.
Re: Adding millions of rows to a new table in 11i with unique index [message #190451 is a reply to message #190450] Wed, 30 August 2006 16:52 Go to previous messageGo to next message
mdamm
Messages: 5
Registered: August 2006
Location: Camp Hill, PA
Junior Member
Here is the table:

CREATE TABLE xxAMES_FREIGHT_RATES
(
RATE_TABLE_ID VARCHAR2(15) NOT NULL,
ORIGIN_ZIP VARCHAR2(3) NOT NULL,
DESTINATION_ZIP VARCHAR2(3) NOT NULL,
ARBITRARY_CODE VARCHAR2(2),
FREIGHT_CLASS NUMBER(4,1),
FREIGHT_WEIGHT NUMBER(6),
FREIGHT_CHARGE NUMBER(6,2)
)
TABLESPACE xxames_data

Looking at the 57 custom tables in this tablespace, this and its partner (which is esentially the same table with different data)are what's causing the problem.
Re: Adding millions of rows to a new table in 11i with unique index [message #190504 is a reply to message #190451] Thu, 31 August 2006 02:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A quick back of the envelope calculation suggests that for the table you've shown, the total size of the data should be less than 200Mb, plus probably another 100Mb or so of index space.

What are the extent specifications for the tables (do a DDL_METADATA on them)
If they've got an initial extent specified that is much larger than required, then that could be the cause of your problem.
Re: Adding millions of rows to a new table in 11i with unique index [message #190627 is a reply to message #190504] Thu, 31 August 2006 08:07 Go to previous messageGo to next message
mdamm
Messages: 5
Registered: August 2006
Location: Camp Hill, PA
Junior Member
What should the extents be for these two tables (because I don't know how to calculate them) and how can I do this on my own in the future? Any (quick) recommended reading? I'm sure the way the extents set up now are wrong. Thanks for helping!!!
Re: Adding millions of rows to a new table in 11i with unique index [message #190629 is a reply to message #190627] Thu, 31 August 2006 08:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Off the top of my head, Oracle recommend using 3 sizes for locally managed tablespace extents - 128K, 4M and 128M.
Most tables will fit into one of these withouh having more than 100 or so extents.
It's a toss up whether I'd put these into the Medium or Large size.
Re: Adding millions of rows to a new table in 11i with unique index [message #190646 is a reply to message #190627] Thu, 31 August 2006 09:18 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
mdamm wrote on Thu, 31 August 2006 09:07

What should the extents be for these two tables (because I don't know how to calculate them) and how can I do this on my own in the future?


After all, you did ask this in SQL Experts forum, so we assume you know how to do all of this.
Re: Adding millions of rows to a new table in 11i with unique index [message #190666 is a reply to message #190627] Thu, 31 August 2006 11:34 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
The tablespace is set to AUTOALLOCATE the extents (it will start with small extents and move up in size as needed), so the problem is not with overly-large extent sizes. I think it is perhaps more related to the large initial and autoextend sizes on the datafiles.
Previous Topic: Max Record Count and Date Range
Next Topic: Trigger
Goto Forum:
  


Current Time: Sat Dec 10 20:51:40 CST 2016

Total time taken to generate the page: 0.08010 seconds