Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Unanswered: How do indexes work

    Hi
    I would like inputs from few people, who are well versed in indexes.
    I am only interested in learning how indexes are implemented in oracle.

    Suppose I create an index on a field in a table. If I insert a new row in the table, does the index automatically get regenerated. Does it happen by a trigger mechanism handled by Oracle itself.

    Also : here is something related to one query I am trying to optimize. A part of the big query I have is "select orderid from table1 where dt_placed between X1Y1Z1 to X2Y2Z2.
    Would it help to create an index on the field dt_placed ???

    Thanx and Regards
    Aruneesh

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How do indexes work

    Originally posted by aruneeshsalhotr
    Hi
    I would like inputs from few people, who are well versed in indexes.
    I am only interested in learning how indexes are implemented in oracle.

    Suppose I create an index on a field in a table. If I insert a new row in the table, does the index automatically get regenerated. Does it happen by a trigger mechanism handled by Oracle itself.

    Also : here is something related to one query I am trying to optimize. A part of the big query I have is "select orderid from table1 where dt_placed between X1Y1Z1 to X2Y2Z2.
    Would it help to create an index on the field dt_placed ???

    Thanx and Regards
    Aruneesh
    Learn all about Oracle indexes here:

    http://technet.oracle.com/docs/produ...schem.htm#3239

    I'm not sure what you mean by "regenerated". Oracle certainly maintains the index when you insert/update/delete rows, and it automatically keeps it balanced. However, that is not the same as an index "re-build".

    The optimizer could use an index scan on a column used in BETWEEN, yes. Of course, it could also decide not to use it.

  3. #3
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Indexes

    Hi
    I could relate the indexes to the B-trees at School. So I would assume with each insert, update or delete of a row, it simply does the same operation on the B(+)-tree it uses.

    Also with respect to the query I have which also does many lookups of the many 80000 rows, would creating an index be helpful. Our DBA also says that it is quite possible that the index might never be used in the query. But my theory is that since it is a lookup between two specific dates and we have an index created on the two fields, wont it speed up the process. If betwen is a problem, would doing dt_placed>X1Y1Z2 and dt_placed < X2Y2Z2 be any better.

    I would appreciate your help in this regards Andrews.
    Thanx and Regards
    Aruneesh

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Indexes

    Originally posted by aruneeshsalhotr
    Hi
    I could relate the indexes to the B-trees at School. So I would assume with each insert, update or delete of a row, it simply does the same operation on the B(+)-tree it uses.

    Also with respect to the query I have which also does many lookups of the many 80000 rows, would creating an index be helpful. Our DBA also says that it is quite possible that the index might never be used in the query. But my theory is that since it is a lookup between two specific dates and we have an index created on the two fields, wont it speed up the process. If betwen is a problem, would doing dt_placed>X1Y1Z2 and dt_placed < X2Y2Z2 be any better.

    I would appreciate your help in this regards Andrews.
    Thanx and Regards
    Aruneesh
    "x BETWEEN a AND b" is nothing more than a shorthand for "x >= a AND x <= b", so changing the syntax of your query will not affect the outcome in this case.

    Yes, it is possible the index will not be used. It is also possible that it will be used, as in this example:

    SQL> create table t as select object_id, created
    2 from all_objects
    3 where rownum < 10000;

    Table created.

    SQL> create index i on t (created);

    Index created.

    SQL> analyze table t compute statistics;

    Table analyzed.

    SQL> var d1 varchar2(11)
    SQL> var d2 varchar2(11)
    SQL> exec :d1 := '01-JAN-2003'

    PL/SQL procedure successfully completed.

    SQL> exec :d2 := '01-FEB-2003'

    PL/SQL procedure successfully completed.

    SQL> set autotrace on
    SQL> select object_id
    2 from t
    3 where created between to_date(:d1,'DD-MON-YYYY') and to_date(:d2,'DD-MON-YYYY');

    no rows selected


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=25 Bytes=275)
    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=4 Card=25 Bytes
    =275)

    2 1 INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE) (Cost=2 Card=25)



    As you can see, it did use the index - even though I used bind variables so that the optimizer has no idea what the 2 date values will be. I believe the optimizer makes an assumption about the percentage of rows that might be returned, but I'm not sure about that.

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697

    Re: Indexes

    [SIZE=1]Originally posted by andrewst
    As you can see, it did use the index - even though I used bind variables so that the optimizer has no idea what the 2 date values will be.
    Indeed, the explain plan when using bind variables is based on it not knowing the actual value of the bind variables, this can affect the actual optimisation once the values of the bind variables are known (ie at execution time).

    Originally posted by andrewst
    I believe the optimizer makes an assumption about the percentage of rows that might be returned, but I'm not sure about that.
    Yes, in the basic form based on the NUM_DISTINCT in USER_TAB_COL_STATISTICS which were populated during the analyze.

    Incidentally Tony, I'm planning to write up a second part to the SQL tuning document about histograms as per your suggestion - are you up for some proof reading again :-)

    Hth
    Bill

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Indexes

    Originally posted by billm
    Indeed, the explain plan when using bind variables is based on it not knowing the actual value of the bind variables, this can affect the actual optimisation once the values of the bind variables are known (ie at execution time).



    Yes, in the basic form based on the NUM_DISTINCT in USER_TAB_COL_STATISTICS which were populated during the analyze.

    Incidentally Tony, I'm planning to write up a second part to the SQL tuning document about histograms as per your suggestion - are you up for some proof reading again :-)

    Hth
    Bill
    No problem!

Posting Permissions

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