Re: Two tables are quite the same, but size of theirs ORA datafiles are different!

On Oct 27, 1:41�pm, Big George <jbet..._at_gmail.com> wrote:
> I've got two tables in Oracle 10.1g, Windows 2003.
>
> TableA
> 16,594,824 records
>
> CREATE TABLE TableA
> (
> � NUMDLE �NCHAR(8) � � � � � � � � � � � � � � �NOT NULL,
> � DIGVER �NCHAR(1) � � � � � � � � � � � � � � �NOT NULL,
> � NUMLIB �NCHAR(6) � � � � � � � � � � � � � � �NOT NULL,
> � CODDEP �NCHAR(2) � � � � � � � � � � � � � � �NOT NULL,
> � CODPRO �NCHAR(2) � � � � � � � � � � � � � � �NOT NULL,
> � CODDIS �NCHAR(2) � � � � � � � � � � � � � � �NOT NULL,
> � APEPAT �VARCHAR2(40 BYTE),
> � APEMAT �VARCHAR2(40 BYTE),
> � NOMBRE �VARCHAR2(35 BYTE),
> � FECNAC �NCHAR(8),
> � CODSEX �NCHAR(1),
> � CODGRI �NCHAR(2),
> � CODSTR �NCHAR(1),
> � TIPDOC �NCHAR(1),
> � CONSTRAINT NUMDLETABLEA
> �PRIMARY KEY
> �(NUMDLE)
> )
> ORGANIZATION INDEX
> LOGGING
> TABLESPACE TableSpaceA
> PCTFREE � �10
> INITRANS � 2
> MAXTRANS � 255
> STORAGE � �(
> � � � � � � INITIAL � � � � �64K
> � � � � � � MINEXTENTS � � � 1
> � � � � � � MAXEXTENTS � � � 2147483645
> � � � � � � PCTINCREASE � � �0
> � � � � � � BUFFER_POOL � � �DEFAULT
> � � � � � �)
> NOPARALLEL
> MONITORING;
>
> TableB
> 16,494,906 records
>
> CREATE TABLE TableB
> (
> � NUMDLE �NCHAR(8) � � � � � � � � � � � � � � �NOT NULL,
> � DIGVER �NCHAR(1) � � � � � � � � � � � � � � �NOT NULL,
> � NUMLIB �NCHAR(6) � � � � � � � � � � � � � � �NOT NULL,
> � CODDEP �NCHAR(2) � � � � � � � � � � � � � � �NOT NULL,
> � CODPRO �NCHAR(2) � � � � � � � � � � � � � � �NOT NULL,
> � CODDIS �NCHAR(2) � � � � � � � � � � � � � � �NOT NULL,
> � APEPAT �VARCHAR2(40 BYTE),
> � APEMAT �VARCHAR2(40 BYTE),
> � NOMBRE �VARCHAR2(35 BYTE),
> � FECNAC �NCHAR(8),
> � CODSEX �NCHAR(1),
> � CODGRI �NCHAR(2),
> � CODSTR �NCHAR(1),
> � TIPDOC �NCHAR(1),
> � CONSTRAINT NUMDLETABLEB
> �PRIMARY KEY
> �(NUMDLE)
> )
> ORGANIZATION INDEX
> LOGGING
> TABLESPACE TableSpaceB
> PCTFREE � �10
> INITRANS � 2
> MAXTRANS � 255
> STORAGE � �(
> � � � � � � INITIAL � � � � �64K
> � � � � � � MINEXTENTS � � � 1
> � � � � � � MAXEXTENTS � � � 2147483645
> � � � � � � PCTINCREASE � � �0
> � � � � � � BUFFER_POOL � � �DEFAULT
> � � � � � �)
> NOPARALLEL
> MONITORING;
>
> Both tables have quite the same number of records.
>
> TableA is set in TableSpaceA.
> Primary Key NUMDLETABLEA is set in TableSpaceA.
> TableSpaceA has only TableA and its PK.
>
> CREATE TABLESPACE TableSpaceA DATAFILE
> � '...\ORACLE\PRODUCT\10.1.0\ORADATA\OPINT2\TableSpaceDataFileA.ORA'
> SIZE 1864M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
> LOGGING
> ONLINE
> PERMANENT
> EXTENT MANAGEMENT LOCAL AUTOALLOCATE
> BLOCKSIZE 8K
> SEGMENT SPACE MANAGEMENT AUTO
> FLASHBACK ON;
>
> In O/S Windows: TableSpaceDataFileA.ORA = 1,908,744 KB
> Data Filename = TableSpaceDataFileA.ORA
> Used% = 99.57
> Size = 1864 MB
> Used = 1856.06 MB
> Free = 7.94 MB
> Blocks = 238592
> AutoExtend = On
> Next Extent = 1 MB
> Max Size = UNLIMITED
> Max Blocks = UNLIMITED
> Status = AVAILABLE
> Fragmentation Index = 78.97
>
> What intrigues me is why DataFileTableSpaceB size takes much more
> megabytes than DataFileTableSpaceA size.
>
> TableB is set in TableSpaceB.
> Primary Key NUMDLETABLEB is set in TableSpaceB.
> TableSpaceB has only TableB and its PK.
>
> CREATE TABLESPACE TableSpaceB DATAFILE
> � '...\ORACLE\PRODUCT\10.1.0\ORADATA\OPINT2\TableSpaceB.ORA' SIZE
> 2964M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
> LOGGING
> ONLINE
> PERMANENT
> EXTENT MANAGEMENT LOCAL AUTOALLOCATE
> BLOCKSIZE 8K
> SEGMENT SPACE MANAGEMENT AUTO
> FLASHBACK ON;
>
> In O/S Windows: TableSpaceDataFileB.ORA = 3,035,144 KB
> Data Filename = TableSpaceDataFileB.ORA
> Used% = 99.33
> Size = 2964 MB
> Used = 2944.06 MB
> Free = 19.94 MB
> Blocks = 379392
> AutoExtend = On
> Next Extent = 50 MB
> Max Size = UNLIMITED
> Max Blocks = UNLIMITED
> Status = AVAILABLE
> Fragmentation Index = 100
>
> Both tables are quite the same.
>
> How can I determine why TableB size takes much more megabytes than
> TableA size ?

Is tableB bigger than tableA or is the datafile for tableB bigger than the datafile used for tableA?

I ask cause I note that the first datafile will grow in 1M chunks while the second will take 50M chunks. This could result in the datafile for tableB being 49M larger than the datafile for tableA when both tables were the same size depending on the exact number of autoallocate extents necessary to hold the table. In general a 1M auto-extend file size is not a practical size for an auto-allocate tabelspace.

HTH -- Mark D Powell -- Received on Tue Oct 28 2008 - 08:45:07 CDT