academic: search in a B+ tree secondary index Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: academic: search in a B+ tree secondary index

  1. #1
    Join Date
    Apr 2002


    I cannot find the answer in the Oracle documentation (http://
    htm#3239) or in a search with google. And this must be easy to solve for
    most of you experts.

    I need to do an applet that make some SQL queries to a Oracle db. One of
    them is to query an indexed table. It is a secondary index and the key is on
    multiples columns: {command, article}. I guess the index was created with
    something like:

    create index i on commands(command, article);

    I need to do a query of the form:

    select * from commands where command = 10;

    And i need to know if the index will be internally used to perform the query
    or all the table will be scanned. In other words, is it possible to use a
    single column to perform a search in a multiple columns index?

    Thanks, X.

  2. #2
    Join Date
    Mar 2002

    When you have an index over commands(command, article) and execute
    select * from commands where command = 10;
    the database will do something called an index range scan. The optimizer uses a range scan when the optimizer finds one or more leading columns of an index (command in your case) in conditions (where command = 10 in your case).

    If you would have an index over commands(article, command) the database would do an index skip scan.

    How the query will realy get executed will be decided by the Oracle optimizer. If you want to see the explain plan of your query you can, in SQL*Plus, use the command "SET AUTOTRACE ON".

    To get statistcs of your table, which will be used by the optimizer, I recommand you to analyze your tables:

  3. #3
    Join Date
    Apr 2002

    As i understand, this mean the database will find the first node with command=10 in the index, and then subsequent nodes may also contain pointers to the matching records. The database will walk recursively into the nodes until command!=10 is found.

Posting Permissions

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

Click Here to Expand Forum to Full Width