academic: search in a B+ tree secondary index
DBAsupport.com 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
    Posts
    2

    Question

    I cannot find the answer in the Oracle documentation (http://
    download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a88856/c11schem.
    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
    Posts
    534
    Hi,

    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).
    http://download-west.oracle.com/otnd...mops.htm#45076

    If you would have an index over commands(article, command) the database would do an index skip scan.
    http://download-west.oracle.com/otnd...mops.htm#51553

    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".
    http://download-west.oracle.com/otnd...3/autotrac.htm

    To get statistcs of your table, which will be used by the optimizer, I recommand you to analyze your tables:
    ANALYZE TABLE commands COMPUTE STATISTICS;


  3. #3
    Join Date
    Apr 2002
    Posts
    2
    TY,

    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