Oracle Database Tuning – Table and Primary Key Sizes

database-sizedatabase-tuningindexoracle

I am analyzing the size of some tables in Oracle, and I noticed that the primary key occupies almost the same size of its related table.

To be clear, my statistics are the following (approx.):

NAME        | TYPE  | SIZE
----------------------------
MYTABLE     | TABLE | 21 GiB
PK_MYTABLE  | INDEX | 20 GiB

MYTABLE has four columns, and three of them composes the primary key. So the sizes should be correct.

But why do Oracle stores this so inefficiently? (a separated structure for the primary key)
Do Oracle has something similar to MS SQL's clustered index, that makes the index part of the related table?
Is there something I can do to avoid this?

Best Answer

But why do Oracle stores this so inefficiently? (a separated structure for the primary key)

It is inefficient for this use case.

Do Oracle has something similar to MS SQL's clustered index, that makes the index part of the related table?

It is called IOT (Index-organized Table) in Oracle.

Overview of Index-Organized Tables

For example:

CREATE TABLE mytable
(
 c1 number,
 c2 number,
 c3 number,
 c4 number,
 CONSTRAINT pk_mytable PRIMARY KEY (c1,c2,c3)
)
ORGANIZATION INDEX 
;

With this, you will have only one segment caled PK_MYTABLE.

Is there something I can do to avoid this?

See above.

Related Solutions

Function based index as a conditional unique key

I'm not completely sure that I understand what you mean by "every FK_ID should be unique for that value of OBJ_TYPE".

If you are trying to say that each FK_ID has to be unique within each OBJ_TYPE, then it sounds like you just want a composite index on (OBJ_TYPE, FK_ID).

CREATE UNIQUE INDEX idx_uniq_metadata
   ON metadata( obj_type, fk_id );

If, on the other hand, you are trying to say that if OBJ_TYPE = 1 then FK_ID must be unique but you can have duplicate FK_ID values if the OBJ_TYPE is something else, then you'd want a function-based unique index.

CREATE UNIQUE INDEX idx_uniq_metadata2
   ON metadata( (case when obj_type = 1 
                      then fk_id 
                      else null 
                   end) );

Sql-server – SQL Server Table Index To big

Your problem is not the primary key. It is a misunderstanding of what a clustered index is. If you look at your structure you will see that the primary key is clustered. This is the default and fairly normal but is not always the case so it pays to check.

The clustered index is basically the table itself. The data pages of the clustered index are the data pages of the table. So your 450mb for the primary key is mostly the data of the table itself. At 446264K that makes it about 1k per row which is not unreasonable depending on the amount of data in the AuthorURL and Comment columns.

Best Answer

Related Solutions

Function based index as a conditional unique key

Sql-server – SQL Server Table Index To big

Related Question