How to use specific index with SELECT

I develop database applications with Delphi 3 based on Oracle 7.3.2 and local Paradox tables. I want to be able to use a definite index in SELECT clauses (I don't want an index to be automatically selected if available by DB system). Need two simple examples about syntax, one for Paradox and one for Oracle, if they are different.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


The DBMS will select the right index (or combination of indexes) for you. This works on both oracle and paradox.

AC032698Author Commented:
I said that I want to use an index determined by ME, even though it might be worse than any automatically selected index. Also, a SELECT clause with ORDER BY gives a read-only result set.

That will do the Job I guess :-)
You can also gives an index ID insteed of the name
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AC032698Author Commented:
I am sorry but it does not work. Have you ever tried it??
Well it comes from the SQL server documentation never tried with ORACLE or PARADOX :-)
SELECT  *  FROM {table_name | view_name}[(optimizer_hints)]
[, {table_name2 | view_name2}[(optimizer_hints)]



Indicate that a specific locking method, a specific index, or no index (table scan) should be used (by the optimizer) with this table for this SELECT.

INDEX = {index_name | index_id}

Specifies the index name or ID to use for that table.

This is the same answer as mine AC ;-) Maybe do you have to give the exact SQL with the right table name and the right index to make it work :-)
AC032698Author Commented:
Thank you for your interest friends. I, too,  have some documents and some URLs describing the specific index usage, quite similar to yours. But they don't work. I tried almost all kinds with Paradox, Oracle and Adabas ODBC. I don't think that I use wrong index name, number, etc. What I really need is just an example which is really tried by an experienced expert.
Hello AC !
I have the solution for  your problem.

   In ORACLE you can make use of HINTS and specify the INDEX NAME so that it
   will use the index which you've specified instead of searching. So that, it will be

  >>----------INDEX  (table  ----------------------------------------- ) -----------------><
                                      |                                |
                                      |      ------------------        |
                                      |      |                |        |
                                      ------ v--- index ---------------
     where :
     table  : specifies the name or alias of the table associated with the index to be                 scanned.
     index : Specifies an index on which an index scan is to be performed.

     You can use hints to specify :
       * the optimization approach for a SQL statement.
       * the goal of the cost-based approach for a SQL statement.
       * the access path for a table accessed by the statement.
       * the join order for a join statement.
       * a join operation in a join statement.

     The HINT may optionally specify one or more indexes.
     *  If this hint specifies a single available index, the optimizer performs a
         scan on this index. The optimizer does not consider a full table scan or
        a scan on another index on the table.
     *  If this hint specifies a list of available indexes, the optimizer considers
        the cost of a scan on each index in the list and performs the index scan
        with the lowest cost.
        The optimizer may also choose to scan multiple indexes from this list and
        merge the results, if such an access path has the lowest cost. The optimizer
        does not consider a full scan or a scan on an index not listed in the hint.
     *  If this hint specifies no indexes, the optimizer considers the cost of a scan
        on each available index on the table and then performs the index scan with
        the lowest cost. The optimizer may also choose to scan multiple indexes and
        merge the results, if such an access path has the lowest cost. The optimizer
        does notconsider a full table scan.

NOTE :    Each pair of HINTS must be separated by blank space.

         Let us assume that
         EMP = TABLE NAME
         EMPNO_INDEX = INDEX on EMP table

         Here is the SELECT statement
         SELECT /*+ INDEX(emp empno_index) USE empno_index */
         FROM emp
         WHERE  empno = 10;

         /* If you want you can include INTO clause here  in your structured language */

Sorry ' I don't have solution for PARADOX, I think it is same, I am not sure.
This works fine for ORACLE.

I hope the above solution is more than sufficient to you and it is acceptable to you.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hi sganta!
It is great answer! Thank you very much. And Jesus loves you as well; excellent grade.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.