0

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.