Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Location
    Paris
    Posts
    48

    Unanswered: How does an index increment ?

    here is the script for the creation of the index :

    CREATE UNIQUE INDEX CST_ACC_NAME_ALREADY_DEFINED ON CV_ACCOUNT
    (MARKET, OWNERNAME, NAME)
    LOGGING
    TABLESPACE USERS
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
    )
    NOPARALLEL;

    ALTER TABLE CV_ACCOUNT ADD (
    UNIQUE (IDXACCOUNT)
    USING INDEX
    TABLESPACE USERS
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    ));

    this index points on the triplet (MARKET, OWNERNAME, NAME), primary key of my table CV_ACCOUNT.
    I'd like to know how it is incremented.

    thanks for your help.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I'm not sure what you mean by "incremented". Do you mean like an loop index?:

    FOR index IN 1..100 LOOP

    If so, you don't understand what a table index is at all. It is not a "counter" that gets incremented by anything. It is more like the index in the back of a book: a sorted list of keys and pointers to the information in the table.

  3. #3
    Join Date
    Apr 2004
    Location
    Paris
    Posts
    48
    I apologize for my awkwardness

    I haven't written this code but I need to do a small prog above it.

    I don't know the notion of index.
    I think it is used here because it is declared as UNIQUE
    And indeed I would like to know how the value of the index changes.

    for more precision, I place at the disposal 2 screenshots which shows that sometimes increment is 1 and sometimes 20.
    I precise that meanwhile, there was no deletion.


    http://mapage.noos.fr/elitol/CV/scrshot-cv_account.JPG
    http://mapage.noos.fr/elitol/CV/scrshot-cv_seqt.JPG

    thanks for your help
    Last edited by elitol; 07-19-04 at 05:10.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    OK, I see what you are asking now - but is has nothing to do with the INDEX. It is the way the IDXACCOUNT column is being populated, which is probably from a SEQUENCE like this one:

    CREATE SEQUENCE idxaccount_seq;

    In the application, or possibly in a BEFORE INSERT trigger, the next sequence value is being obtained like this:

    SELECT idxaccount_seq.NEXTVAL INTO :NEW.idxaccount FROM DUAL;

    - or -

    INSERT INTO cv_account (idxaccount, ... ) VALUES ( idxaccount_seq.NEXTVAL, ... );

    The reason there are sometimes gaps of 20 between the numbers is because sequence values are usually "cached", and the default cache size is 20 - i.e. when you first ask for a new number using NEXTVAL, Oracle allocates 20 numbers to your session. If you call NEXTVAL again, Oracle gives you the next number from your cache. But if you don't call NEXTVAL again, the other 19 numbers are "lost".

    For more info, read about sequences in the documentation.

  5. #5
    Join Date
    Apr 2004
    Location
    Paris
    Posts
    48

    Smile

    thank you
    this will help a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •