OraFAQ Forum: Server Administration » creation of larger table

Home » RDBMS Server » Server Administration » creation of larger table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

creation of larger table [message #229893]

Tue, 10 April 2007 04:49

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

I have one large table with size of 18GB. I wanted to create a duplicate table in another schema using this large table. But, It does not create this table. Moreover, it does not show any error messages. I am very much confused about this creation of large tables. How to investigate about the wrong thing. What would be the causes for not creation of tables.

I used the below statements but remain fail to create this large table.

CREATE TABLE ARADMARCH.j95 nologging
AS
SELECT *
FROM j95
WHERE integerdate1 ( c3 ) < add_months( sysdate, -36 )

integerdat1 is a function name.

CREATE TABLE ARADMARCH.j95 nologging
AS
SELECT *
FROM j95
WHERE c3 < 86400*(add_months(sysdate,-36)-date '1970-01-01')

What is the best way to create thsi big table quckly?

Your help would be highly appreciated.

Report message to a moderator

Re: creation of larger table [message #229895 is a reply to message #229893]

Tue, 10 April 2007 04:54

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

But, It does not create this table. Moreover, it does not show any error messages.

Very strange, are you sure you executed the statement?
In the exemples you give, the statements are not complete, a terminator is missing (; or /).

Regards
Michel

Report message to a moderator

Re: creation of larger table [message #229899 is a reply to message #229895]

Tue, 10 April 2007 05:00

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

I have included the semicolon at the end of statement. I though it would be assumed. Anyway, Please suggest the most appropriate way to create the 18GN lasrger table.

Report message to a moderator

Re: creation of larger table [message #229905 is a reply to message #229899]

Tue, 10 April 2007 05:06

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

CTAS is the fatest way.

Report message to a moderator

Re: creation of larger table [message #229911 is a reply to message #229905]

Tue, 10 April 2007 05:12

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

What is the meaning of CTAS? Please explain it.

Report message to a moderator

Re: creation of larger table [message #229913 is a reply to message #229911]

Tue, 10 April 2007 05:28

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Create Table As Select

So enhance your select statement, you enhance your table creation.

Report message to a moderator

Re: creation of larger table [message #229919 is a reply to message #229913]

Tue, 10 April 2007 05:46

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

Can you please verify and let me know whether it is corect or not.

Previously, it was generating lot of I/O and response time was too slow

You wanted to suggest something index hint or oreder by clause still with this CTAS.

Our hardware is having multiple CPU's.
CREATE TABLE j56 nologging
tablespace ararch
parallel (degree 4)
unrecoverable
AS
SELECT *
FROM A.j56
WHERE integerdate1 ( c3 ) < add_months( sysdate, -36 )

Hope thins info would be helpful you to provide me the appropriate solution.

Report message to a moderator

Re: creation of larger table [message #229928 is a reply to message #229919]

Tue, 10 April 2007 05:59

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Add a parallel degree on your select if your system support it.
But take care of IO. Most of the time this is not number of CPU that limits the parallel degree but the number of disks and io bandwidth.

Regards
Michel

Report message to a moderator

Re: creation of larger table [message #229935 is a reply to message #229928]

Tue, 10 April 2007 06:15

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

I have included the paraleel degree 4 in the previou statement. But you are correct about DISK i/o and its bandwidth. How and what to take corrective action to aviod heavy I/O? How to measure the DISK I/O and its bandwidth?

Your help would be highly appreciated.

Report message to a moderator

Re: creation of larger table [message #229942 is a reply to message #229935]

Tue, 10 April 2007 06:36

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

iostat

Report message to a moderator

Re: creation of larger table [message #229997 is a reply to message #229942]

Tue, 10 April 2007 08:50

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

I'll go through it and get back to you.

Report message to a moderator

Re: creation of larger table [message #230560 is a reply to message #229919]

Thu, 12 April 2007 03:57

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

Can you please verify the statement suggested by you?

CREATE TABLE j56 parallel nologging
tablespace ararch
AS
SELECT *
FROM A.j56 parallel (degree 4)
WHERE integerdate1 ( c3 ) < add_months( sysdate, -36 )

Please correct it it is wrong.

Report message to a moderator