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