Home » RDBMS Server » Server Administration » creation of larger table
creation of larger table [message #229893] Tue, 10 April 2007 04:49 Go to next message
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.
Re: creation of larger table [message #229895 is a reply to message #229893] Tue, 10 April 2007 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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
Re: creation of larger table [message #229899 is a reply to message #229895] Tue, 10 April 2007 05:00 Go to previous messageGo to next message
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.
Re: creation of larger table [message #229905 is a reply to message #229899] Tue, 10 April 2007 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

CTAS is the fatest way.
Re: creation of larger table [message #229911 is a reply to message #229905] Tue, 10 April 2007 05:12 Go to previous messageGo to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

What is the meaning of CTAS? Please explain it.
Re: creation of larger table [message #229913 is a reply to message #229911] Tue, 10 April 2007 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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.
Re: creation of larger table [message #229919 is a reply to message #229913] Tue, 10 April 2007 05:46 Go to previous messageGo to next message
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.

Re: creation of larger table [message #229928 is a reply to message #229919] Tue, 10 April 2007 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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

Re: creation of larger table [message #229935 is a reply to message #229928] Tue, 10 April 2007 06:15 Go to previous messageGo to next message
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.
Re: creation of larger table [message #229942 is a reply to message #229935] Tue, 10 April 2007 06:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
iostat

Re: creation of larger table [message #229997 is a reply to message #229942] Tue, 10 April 2007 08:50 Go to previous messageGo to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

I'll go through it and get back to you.
Re: creation of larger table [message #230560 is a reply to message #229919] Thu, 12 April 2007 03:57 Go to previous messageGo to next message
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.
Re: creation of larger table [message #230567 is a reply to message #230560] Thu, 12 April 2007 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Seems correct as far as your hardware can support it.

Regards
Michel
Re: creation of larger table [message #230598 is a reply to message #230567] Thu, 12 April 2007 04:52 Go to previous message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

Thanks for your answer.
Previous Topic: oracle 10gr2 companion
Next Topic: shared memory error
Goto Forum:
  


Current Time: Sat Dec 03 17:52:30 CST 2016

Total time taken to generate the page: 0.16914 seconds