Home » SQL & PL/SQL » SQL & PL/SQL » table size
table size [message #251688] Mon, 16 July 2007 01:34 Go to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Hello

I have an Oracle table P10 which is 2.36GB. The table has 10 fields, all with type number and lenght 8.

1) When I create a duplicate (pass through statement in SAS: create table dupl as select * from P10), the new table dupl has the same size (2.36GB).

2) When I create a duplicate with the SAS Software (same statement as in 1), but the software gets all data over the network to my workstation and writes back the new table to the Oracle DB). The new table is just 1.6GB! The fields are all type number and lenght 8.

Why can SAS create a table which is just 1.6GB? How can I find out what the difference is between these tables?

Thanks
Stefan

[Updated on: Mon, 16 July 2007 01:35]

Report message to a moderator

Re: table size [message #251709 is a reply to message #251688] Mon, 16 July 2007 02:57 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
How are you determining the size?
SELECT SUM(BYTES) FROM USER_SEGMENTS WHERE SEGMENT_NAME = '....'


Check the PCTFREE on USER_TABLES for the two tables. Is it different?

Are the two tables being created in the same tablespace?
Does the tablespace have compression enabled?

Ross Leishman

Re: table size [message #251740 is a reply to message #251688] Mon, 16 July 2007 07:56 Go to previous message
steffeli
Messages: 112
Registered: July 2006
Senior Member
I get the size with this code:

create table TABLESPACE as
select segment_name, 
       sum(bytes/1024/1024/1000) "GB",
	   ( select sum(bytes/1024/1024/1000) "GB_TABLES"
		 from user_extents ) as "GB_TABLES"
from user_extents 
group by segment_name
order by GB desc


Check the PCTFREE on USER_TABLES for the two tables. Is it different?

What do you mean by that? How can I check it?

Are the two tables being created in the same tablespace?

Yes

Does the tablespace have compression enabled?


No


May be SAS selects a better number type? How can I check the number type in Oracle?

Thanks
Stefan
Previous Topic: CREATING DIRECTORY
Next Topic: Store file in database tables
Goto Forum:
  


Current Time: Sat Dec 10 13:07:36 CST 2016

Total time taken to generate the page: 0.13174 seconds