Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Posts
    18

    Unanswered: Indexing on Insert Query

    Dear masters,

    I want to know about indexing on insert query. I've read Database Concepts but I don't find what I want. This is the case.

    I have a table that only have an index. We know that when an insert query is executed, Oracle will insert it on that table AND its index. My table just contain an index.

    What Oracle do to an index that newly inserted? When Oracle split a leaf that becoming full? How Oracle maintain that insert? I want for more detail explanation.

    Code:
    http://en.wikipedia.org/wiki/B-tree#Insertion
    Oracle don't do an insert like this one (wikipedia). So, how Oracle do to an insert query? do to 1000 insert queries? And last question, how to see it? I used event 10046, but I just got very little information about index.

    Thx for your expertise.

    Regards,
    Wiwin.
    Last edited by kiman; 02-14-09 at 23:35.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post DDL which creates table (& index).
    Post DML to populate table with sample data; in sufficient quantity to force leaf split.
    using SQPLUS along with CUT & PASTE, provide SQL which proves leaf split.

    What Operating System name & version?
    Which Oracle RDBMS version to 4 decimal places?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2009
    Posts
    18
    Windows Professional Version 2002 SP3

    Oracle 10g Express for Windows 10.2.0.1

    DDL:

    create table insertdata1 (col1 varchar2(30), col2 number(4));
    create index idx_inserdata1_col1 on insertdata1(col1);

    when i insert, i used PLSQL:

    begin
    for i in 1 .. 1000
    loop
    insert into abcd_test
    values (i,'mydata_'||i);
    end loop;
    commit;
    end;
    /

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    How does
    >insert into abcd_test
    >values (i,'mydata_'||i);
    succeed
    against previous created table
    >create table insertdata1 (col1 varchar2(30), col2 number(4));
    Wrong table name & wrong data types!

    >using SQPLUS along with CUT & PASTE, provide SQL which proves leaf split.
    Which you declined to answer/do.

    I decline to waste more time on this.
    Last edited by anacedent; 02-15-09 at 01:06.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2009
    Posts
    18
    Quote Originally Posted by anacedent
    How does
    >insert into abcd_test
    >values (i,'mydata_'||i);
    succeed
    against previous created table
    >create table insertdata1 (col1 varchar2(30), col2 number(4));
    Wrong table name & wrong data types!

    >using SQPLUS along with CUT & PASTE, provide SQL which proves leaf split.
    Which you declined to answer/do.

    I decline to waste more time on this.
    lil bit wrong information that I put in here.

    abcd_test is a test table, that contain number(4) and varchar2(30). I'm using a lot of tables. sorry about it.

    I will correct my DDL:
    create table abcd_test (col1 number(4), col2 varchar2(30));
    create index idx_abcd_col2 on abcd_test(col2);

    the SQL just PL/SQL (insert into abcd_test bla3), and it split from trace table:
    Code:
    EXEC #8:c=0,e=253,p=0,cr=0,cu=3,mis=0,r=1,dep=1,og=1,tim=13908128184
    BINDS #8:
    kkscoacd
     Bind#0
      oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
      oacflg=03 fl2=206001 frm=00 csi=00 siz=48 off=0
      kxsbbbfp=06df4104  bln=22  avl=02  flg=05
      value=64
     Bind#1
      oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
      oacflg=03 fl2=206001 frm=00 csi=00 siz=0 off=24
      kxsbbbfp=06df411c  bln=22  avl=02  flg=01
      value=64
    splitting leaf,dba 0x010000e6,time 12:20:13.218
    kdisnew_bseg_srch_cbk reject block -mark full,dba 0x010000e6,time 12:20:13.218
    kdisnew_bseg_srch_cbk rejecting block ,dba 0x010000e6,time 12:20:13.218
    so more specific question:
    what kind of situation that make a leaf split? full, but what's the condition of "full" in this case?
    You're right about:
    From where I sit, you are wasting your time looking for answers to your questions.
    You can NOT control how Oracle implements indexes.
    You have to live with the results regardless how internally indexes are implemented.
    Yes, I'm searching for the results, more detailed results, where I can conclude something about Oracle Indexing. I know my questions are too "whatever", but this is my job. I hate my boss sometimes, very stubborn.

    And I try to find out. I just want to know from this forum, that may give me information how Oracle implementing their index. I'm just searching in here. Peace.

    Regards,
    Wiwin.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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