1 Reply Latest reply on Jul 9, 2020 8:58 AM by Anand Chandak-Oracle

    indexing performance question

    Eric Jing

      Dear sir,

       

      I have 2 questions regarding indexing performance.

       

      question 1:

      Suppose there is a table like this:

      create table userTable

      (

      userid integer GENERATED BY DEFAULT AS IDENTITY

          (START WITH 1 INCREMENT BY 1),

      loginname string,

      companyid integer,

      workemail string,

          personalemail string,

          workyear integer,

      primary key (userid)

      )

      create index idx_loginname on userTable (loginname);

       

      Based on above table, the oracle nosql db is going to regenerate all the indexes whenever there is a new row inserted? even if there is only one new row inserted?

      I think, a better way is to recalculate all the indexes for above table when the total number of unsorted  new data accumulated to a specified number.

       

      question 2:

      if above table is updated like this, add a sharding key:

       

      create table userTable

      (

      userid integer GENERATED BY DEFAULT AS IDENTITY

          (START WITH 1 INCREMENT BY 1),

      loginname string,

      companyname string,

      workemail string,

          personalemail string,

          workyear integer,

      primary key (shard(companyid),userid)

      )

      create index idx_loginname on userTable (loginname);

       

      then, I'm wondering that the indexing engine of the nosql db will recalculate the index on per shard basis seperately? or recalculate the index for the entire table as a whole? I think the first way will have a higher performance when the table gets bigger and bigger.

       

      Awaiting yours with thanks.

        • 1. Re: indexing performance question
          Anand Chandak-Oracle

          #2 Once you have designated one or more fields as primary key, they cannot be changed. So, you can update that primary key in the fashion you described. Also, on the shard key, if you don't explicitly specify the shard key, by default all the fields of primary key are part of the shard key.

           

          #1, Your question isn't clear. The index gets updated after any update, delete or insert operation.