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

Comments embedded.

On Oct 27, 4:48 pm, Big George <jbet..._at_gmail.com> wrote:
> '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.
>

They may not have the same data, though, and the data in TableB may be chained/migrated causing Oracle to consume a larger number of data blocks to store the information.

> 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
>

I am not a fan of autoextend, although I understand it has its place. Setting autoextend without restricting the file size, though, is simply asking for trouble. If you're planning on using autoextend you should set a MAXSIZE to something far less than UNLIMITED.

> What intrigues me is why DataFileTableSpaceB size takes much more
> megabytes than DataFileTableSpaceA size.
>

SQL> @?/rdbms/admin/utlchain

Table created.

SQL> analyze table tableb list chained rows;

Table analyzed.

SQL> select owner, table_name, count(*)

   2     from chained_rows
   3     group by owner, table_name;

...

You may be surprised at what you find if you do this.

> 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.
>

But the data in them is not.

> How can I determine why TableB size takes much more megabytes than
> TableA size ?
>

I've shown you, in code above. Run that, as I've shown you, and see what 'falls out'.

> Thanks a lot!

David Fitzjarrell Received on Tue Oct 28 2008 - 09:12:29 CDT