-
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.
-
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;
-
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.
