Skip to Main Content
  • Questions

  • Local index for ONE foreign key field takes 50% of table size

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Alex.

Asked: April 25, 2016 - 4:05 pm UTC

Last updated: May 07, 2016 - 8:03 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi,
I have following partitioned table with 7 fields:
Rdate date,
CalcID NUMBER(5,0) not null,
ContractID NUMBER(9,0) not null,
AqId NUMBER(1,0) not null,
Balancetype varchar2(1 byte) not null,
S1 number,
S2 number

and local non-unique b-tree index for the field ContractID.
This index takes 50% of table size.
I need this index only for fast update ContactID values in table.
Please, tell me, why this index is so big?

and Connor said...

That doesnt seem unusual. Looking at your table columns, there's maybe 10-20 bytes per row (you can look at AVG_ROW_LEN in USER_TABLES to get a better idea).

And an index will have the key (contract_id), and the rowid for each row - so that's going to be around 10 bytes per row. So 50% sounds totally reasonable...no ?

Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Alex Bud, April 26, 2016 - 6:31 pm UTC


Alex Bud, April 26, 2016 - 7:52 pm UTC

Thanks for answer. You are right.
avg_row_len for index is 16 byte (6 byte for field + 10 byte for rowid)
avg_row_len for table is 32 byte.
But, I'm using this index only for fast table update of ContractID values, and this index looks like non-effective one.

How can I do fast table update without this index?
Chris Saxon
April 27, 2016 - 4:26 am UTC

When you say "fast table update of ContractID values" do you mean you use the index to *find* rows with a given contract_id, or are *changing* contract_id's ?

Or both ?

What are indexes and primary key for this table ?

anonymous anonymous, April 27, 2016 - 7:44 am UTC

Just wondering what are indexes and primary key for the table and what is partitioning key.

In some (rare) cases changing order of primary key columns and converting table to index organized table (IOT) might help.

Providing more information does give these quys more possibilities to solve your problem.
Do You need to update with contractid all partitions or just most recent ones ? Could this local index exist only in few partitions ?

A reader, April 27, 2016 - 9:54 am UTC

I mean using the index only for fast *changing* contract_id's.

And to clarify structures:
FOR TABLE:
CREATE TABLE MIS_DM.DAILY_BS
(
RDATE DATE NOT NULL ENABLE,
CALC_BS_ID NUMBER(5,0) NOT NULL ENABLE,
CONTRACT_ID NUMBER(9,0) NOT NULL ENABLE,
aq_id number(3,0) not null,
BALANCE_TYPE VARCHAR2(1 byte) not null,
BALANCE_NOM NUMBER NOT NULL ENABLE,
BALANCE_UAH NUMBER NOT NULL ENABLE,
CONSTRAINT DAILY_BS_FK1 FOREIGN KEY (CALC_BS_ID) REFERENCES MIS_DM.CL_CALC_BS (CALC_BS_ID) ON DELETE SET NULL ENABLE,
CONSTRAINT DAILY_BS_FK2 FOREIGN KEY (CONTRACT_ID) REFERENCES MIS_DM.CL_CONTRACT_LINKS (CONTRACT_LINK_ID) ON DELETE SET NULL ENABLE,
CONSTRAINT DAILY_BS_FK3 FOREIGN KEY (AQ_ID) REFERENCES MIS_DM.CL_ASSET_QUALITY (AQ_ID) ON DELETE SET NULL ENABLE,
CONSTRAINT DAILY_BS_FK4 FOREIGN KEY (RDATE) REFERENCES MIS_DM.CL_DAILY_DATES (RDATE_FROM) ON DELETE SET NULL ENABLE,
CONSTRAINT DAILY_BS_CHK1 CHECK (BALANCE_TYPE in ('A','P')) ENABLE
)
PARTITION BY RANGE ("RDATE") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY HASH ("CALC_BS_ID")
SUBPARTITIONS 40
(PARTITION "DAILY_BS_INIT" VALUES LESS THAN (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))
STORAGE(INITIAL 1024K NEXT 1024K) TABLESPACE "MIS_DATA_TBS";

FOR INDEX:
CREATE INDEX MIS_DM.DAILY_BS_INDX1 ON MIS_DM.DAILY_BS (CONTRACT_ID ASC)
LOCAL
LOGGING
TABLESPACE MIS_INDX_TBS
STORAGE (INITIAL 512K NEXT 512K);
Connor McDonald
April 27, 2016 - 10:09 am UTC

"How can I do fast table update without this index? "

Why do you not want the index ? Is this just a space issue ? There's no such thing as a free lunch. If you need fast access to a particular contract_id in a large table, then you index the column.

Otherwise, you'd need to look at a significant restructure of table...but that seems high risk just to save some space.

A reader, April 27, 2016 - 9:56 am UTC

And I can't change table type to IOT, because this is VERY BIG actuals table

A reader, April 27, 2016 - 12:10 pm UTC

This is just a space issue.

Of course I can create global partitioned compressed index instead of existing local.
In this case I will solve index space problem (it will be about 30% of table size), BUT all my regualar inserts will be significant slower
Connor McDonald
April 28, 2016 - 6:18 am UTC

I'd like to see your test case showing the inserts being slower.

Hash Partitions - Power of 2

Daniel B, April 28, 2016 - 8:23 am UTC

Additionally, your table definition shows your table is sub partitioned by hash into 40 sub-partitions. Shouldn't this be a power of 2 for optimal distribution. ie; 32 or 64.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1001181800346992268
Connor McDonald
April 28, 2016 - 10:53 am UTC

Yes, nice catch.

Power is 2 is definitely where you want to be.

A reader, May 04, 2016 - 8:05 am UTC

Thanks for "Power of 2 recommendation"

and about my test cases (inserting of ~6'500'000 rows into actuals table).
1. with local parititioned index it takes ~250 seconds
2. with global compressed parititioned index it takes ~1000 seconds


global compressed partitioned index created by next sql:
CREATE INDEX MIS_DM.DAILY_BS_INDX1 ON MIS_DM.DAILY_BS (CONTRACT_LINK_ID ASC)
GLOBAL PARTITION BY RANGE (CONTRACT_LINK_ID)
(
PARTITION DAILY_BS_INDX1_01 VALUES LESS THAN (2000000),
PARTITION DAILY_BS_INDX1_02 VALUES LESS THAN (4000000),
PARTITION DAILY_BS_INDX1_03 VALUES LESS THAN (6000000),
PARTITION DAILY_BS_INDX1_04 VALUES LESS THAN (8000000),
PARTITION DAILY_BS_INDX1_05 VALUES LESS THAN (10000000),
PARTITION DAILY_BS_INDX1_06 VALUES LESS THAN (12000000),
PARTITION DAILY_BS_INDX1_07 VALUES LESS THAN (14000000),
PARTITION DAILY_BS_INDX1_08 VALUES LESS THAN (16000000),
PARTITION DAILY_BS_INDX1_09 VALUES LESS THAN (18000000),
PARTITION DAILY_BS_INDX1_10 VALUES LESS THAN (20000000),
PARTITION DAILY_BS_INDX1_11 VALUES LESS THAN (22000000),
PARTITION DAILY_BS_INDX1_12 VALUES LESS THAN (24000000),
PARTITION DAILY_BS_INDX1_13 VALUES LESS THAN (26000000),
PARTITION DAILY_BS_INDX1_14 VALUES LESS THAN (28000000),
PARTITION DAILY_BS_INDX1_15 VALUES LESS THAN (30000000),
PARTITION DAILY_BS_INDX1_16 VALUES LESS THAN (32000000),
PARTITION DAILY_BS_INDX1_17 VALUES LESS THAN (34000000),
PARTITION DAILY_BS_INDX1_18 VALUES LESS THAN (36000000),
PARTITION DAILY_BS_INDX1_19 VALUES LESS THAN (38000000),
PARTITION DAILY_BS_INDX1_20 VALUES LESS THAN (40000000),
PARTITION DAILY_BS_INDX1_21 VALUES LESS THAN (42000000),
PARTITION DAILY_BS_INDX1_22 VALUES LESS THAN (44000000),
PARTITION DAILY_BS_INDX1_23 VALUES LESS THAN (46000000),
PARTITION DAILY_BS_INDX1_24 VALUES LESS THAN (48000000),
PARTITION DAILY_BS_INDX1_25 VALUES LESS THAN (50000000),
PARTITION DAILY_BS_INDX1_26 VALUES LESS THAN (52000000),
PARTITION DAILY_BS_INDX1_27 VALUES LESS THAN (54000000),
PARTITION DAILY_BS_INDX1_28 VALUES LESS THAN (56000000),
PARTITION DAILY_BS_INDX1_29 VALUES LESS THAN (58000000),
PARTITION DAILY_BS_INDX1_30 VALUES LESS THAN (60000000),
PARTITION DAILY_BS_INDX1_def VALUES LESS THAN (maxvalue)
)
NOLOGGING
TABLESPACE MIS_INDX_TBS
STORAGE (INITIAL 1024K NEXT 1024K)
COMPRESS;
Connor McDonald
May 05, 2016 - 12:00 pm UTC

I'm not seeing anything like that kind of degradation on my machine


SQL> drop table DAILY_BS  purge;

Table dropped.

SQL>
SQL> CREATE TABLE DAILY_BS
  2  (
  3  RDATE DATE NOT NULL ENABLE,
  4  CALC_BS_ID NUMBER(12,0) NOT NULL ENABLE,
  5  CONTRACT_ID NUMBER(12,0) NOT NULL ENABLE,
  6  aq_id number(12,0) not null,
  7  BALANCE_TYPE VARCHAR2(1 byte) not null,
  8  BALANCE_NOM NUMBER NOT NULL ENABLE,
  9  BALANCE_UAH NUMBER NOT NULL ENABLE
 10  )
 11  PARTITION BY RANGE ("RDATE") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
 12  SUBPARTITION BY HASH ("CALC_BS_ID")
 13  SUBPARTITIONS 32
 14  (PARTITION "DAILY_BS_INIT" VALUES LESS THAN (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))
 15  ;

Table created.

SQL>
SQL> CREATE INDEX DAILY_BS_INDX1 ON DAILY_BS (CONTRACT_ID ASC) LOCAL ;

Index created.

SQL> drop table t purge;

Table dropped.

SQL> create table t ( id int, s date, e date );

Table created.

SQL>
SQL> begin
  2    for i in 0 .. 30 loop
  3      insert into daily_bs values (
  4      date '2014-01-01'+i,
  5      i,
  6      i,
  7      1,
  8      'X',
  9      i,
 10      i);
 11    end loop;
 12    commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> create or replace
  2  procedure hammer(p_id int, p_iter int default 50) is
  3    type rnd_list is table of number index by pls_integer;
  4    l_rnd rnd_list;
  5  begin
  6    select dbms_random.value(1,60000000)
  7    bulk collect into l_rnd
  8    from
  9      ( select 1 from dual connect by level <= 2000 ),
 10      ( select 1 from dual connect by level <= 2000 )
 11    where rownum <= p_iter;
 12
 13    insert into t values(p_id,sysdate,null);
 14    commit;
 15
 16    for i in 1 .. p_iter loop
 17      insert into daily_bs values (
 18      date '2014-01-01'+i/p_iter*30,
 19      i,
 20      l_rnd(i),
 21      1,
 22      'X',
 23      i,
 24      i);
 25      commit;
 26    end loop;
 27    update t set e = sysdate where id = p_id;
 28    commit;
 29  end;
 30  /

Procedure created.

SQL> sho err
No errors.
SQL>
SQL>
SQL> declare
  2    j int;
  3  begin
  4  for i in 1 .. 4 loop
  5    dbms_job.submit(j,'hammer('||i||',500000);');
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> delete from t;

0 rows deleted.

SQL> select what from user_jobs;

WHAT
------------------------------------------------------------
hammer(2,500000);
hammer(3,500000);
hammer(1,500000);
hammer(4,500000);

SQL> commit;

Commit complete.

SQL> select avg(e-s)*86400 from t;

AVG(E-S)*86400
--------------
        130.75


versus


SQL> drop table DAILY_BS  purge;

Table dropped.

SQL>
SQL> CREATE TABLE DAILY_BS
  2  (
  3  RDATE DATE NOT NULL ENABLE,
  4  CALC_BS_ID NUMBER(12,0) NOT NULL ENABLE,
  5  CONTRACT_ID NUMBER(12,0) NOT NULL ENABLE,
  6  aq_id number(12,0) not null,
  7  BALANCE_TYPE VARCHAR2(1 byte) not null,
  8  BALANCE_NOM NUMBER NOT NULL ENABLE,
  9  BALANCE_UAH NUMBER NOT NULL ENABLE
 10  )
 11  PARTITION BY RANGE ("RDATE") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
 12  SUBPARTITION BY HASH ("CALC_BS_ID")
 13  SUBPARTITIONS 32
 14  (PARTITION "DAILY_BS_INIT" VALUES LESS THAN (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))
 15  ;

Table created.

SQL>
SQL> CREATE INDEX DAILY_BS_INDX1 ON DAILY_BS (CONTRACT_ID ASC)
  2  GLOBAL PARTITION BY RANGE (CONTRACT_ID)
  3  (
  4  PARTITION DAILY_BS_INDX1_01 VALUES LESS THAN (2000000),
  5  PARTITION DAILY_BS_INDX1_02 VALUES LESS THAN (4000000),
  6  PARTITION DAILY_BS_INDX1_03 VALUES LESS THAN (6000000),
  7  PARTITION DAILY_BS_INDX1_04 VALUES LESS THAN (8000000),
  8  PARTITION DAILY_BS_INDX1_05 VALUES LESS THAN (10000000),
  9  PARTITION DAILY_BS_INDX1_06 VALUES LESS THAN (12000000),
 10  PARTITION DAILY_BS_INDX1_07 VALUES LESS THAN (14000000),
 11  PARTITION DAILY_BS_INDX1_08 VALUES LESS THAN (16000000),
 12  PARTITION DAILY_BS_INDX1_09 VALUES LESS THAN (18000000),
 13  PARTITION DAILY_BS_INDX1_10 VALUES LESS THAN (20000000),
 14  PARTITION DAILY_BS_INDX1_11 VALUES LESS THAN (22000000),
 15  PARTITION DAILY_BS_INDX1_12 VALUES LESS THAN (24000000),
 16  PARTITION DAILY_BS_INDX1_13 VALUES LESS THAN (26000000),
 17  PARTITION DAILY_BS_INDX1_14 VALUES LESS THAN (28000000),
 18  PARTITION DAILY_BS_INDX1_15 VALUES LESS THAN (30000000),
 19  PARTITION DAILY_BS_INDX1_16 VALUES LESS THAN (32000000),
 20  PARTITION DAILY_BS_INDX1_17 VALUES LESS THAN (34000000),
 21  PARTITION DAILY_BS_INDX1_18 VALUES LESS THAN (36000000),
 22  PARTITION DAILY_BS_INDX1_19 VALUES LESS THAN (38000000),
 23  PARTITION DAILY_BS_INDX1_20 VALUES LESS THAN (40000000),
 24  PARTITION DAILY_BS_INDX1_21 VALUES LESS THAN (42000000),
 25  PARTITION DAILY_BS_INDX1_22 VALUES LESS THAN (44000000),
 26  PARTITION DAILY_BS_INDX1_23 VALUES LESS THAN (46000000),
 27  PARTITION DAILY_BS_INDX1_24 VALUES LESS THAN (48000000),
 28  PARTITION DAILY_BS_INDX1_25 VALUES LESS THAN (50000000),
 29  PARTITION DAILY_BS_INDX1_26 VALUES LESS THAN (52000000),
 30  PARTITION DAILY_BS_INDX1_27 VALUES LESS THAN (54000000),
 31  PARTITION DAILY_BS_INDX1_28 VALUES LESS THAN (56000000),
 32  PARTITION DAILY_BS_INDX1_29 VALUES LESS THAN (58000000),
 33  PARTITION DAILY_BS_INDX1_30 VALUES LESS THAN (60000000),
 34  PARTITION DAILY_BS_INDX1_def VALUES LESS THAN (maxvalue)
 35  )
 36  COMPRESS;

Index created.

SQL>
SQL> drop table t purge;

Table dropped.

SQL> create table t ( id int, s date, e date );

Table created.

SQL>
SQL> begin
  2    for i in 0 .. 30 loop
  3      insert into daily_bs values (
  4      date '2014-01-01'+i,
  5      i,
  6      i,
  7      1,
  8      'X',
  9      i,
 10      i);
 11    end loop;
 12    commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> create or replace
  2  procedure hammer(p_id int, p_iter int default 50) is
  3    type rnd_list is table of number index by pls_integer;
  4    l_rnd rnd_list;
  5  begin
  6    select dbms_random.value(1,60000000)
  7    bulk collect into l_rnd
  8    from
  9      ( select 1 from dual connect by level <= 2000 ),
 10      ( select 1 from dual connect by level <= 2000 )
 11    where rownum <= p_iter;
 12
 13    insert into t values(p_id,sysdate,null);
 14    commit;
 15
 16    for i in 1 .. p_iter loop
 17      insert into daily_bs values (
 18      date '2014-01-01'+i/p_iter*30,
 19      i,
 20      l_rnd(i),
 21      1,
 22      'X',
 23      i,
 24      i);
 25      commit;
 26    end loop;
 27    update t set e = sysdate where id = p_id;
 28    commit;
 29  end;
 30  /

Procedure created.

SQL> sho err
No errors.
SQL>
SQL>
SQL> declare
  2    j int;
  3  begin
  4  for i in 1 .. 4 loop
  5    dbms_job.submit(j,'hammer('||i||',500000);');
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> delete from t;

0 rows deleted.

SQL> select what from user_jobs;

WHAT
------------------------------------------------------------
hammer(1,500000);
hammer(2,500000);
hammer(3,500000);
hammer(4,500000);

SQL> commit;

Commit complete.

SQL> select avg(e-s)*86400 from t;

AVG(E-S)*86400
--------------
         140.5

SQL>


A reader, May 06, 2016 - 8:03 am UTC

Thanks for answer.


Is it possible, what I have so big defference (~250 sec vs ~1000 sec) due to next reasons:
1. Server with DB 12c is virtualized.
2. Actuals table contains over 2'184'000'000 rows
3. CONTRACT_ID values are distributed unevenly through index partitions (most of them are placed in 5 partitions).

A reader, May 06, 2016 - 9:47 am UTC

It looks like in my case the problem not in "USING INDEX COMPRESSION"

Inserts goes slower when i'm using global partitioned index instead of local partitioned
Connor McDonald
May 07, 2016 - 8:03 am UTC

Just coming back to the original premise of the question - if you want to access a small set of data fast within a large set of data, then there is a strong change you will need an index to do so.

Alex Bud, May 07, 2016 - 5:54 pm UTC

OK, thanks
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.