?
Solved

How to use specific index with SELECT

Posted on 1998-05-21
10
Medium Priority
?
349 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:AC032698
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
10 Comments
 

Expert Comment

by:jhunt
ID: 1025465
try:
SELECT * FROM EXAMPLE_TABLE
ORDER BY FIELD1+FIELD2

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

John
0
 

Author Comment

by:AC032698
ID: 1025466
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.
0
 
LVL 1

Expert Comment

by:raky
ID: 1025467
SELECT * FROM TABLE INDEX=NAME

That will do the Job I guess :-)
You can also gives an index ID insteed of the name
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:AC032698
ID: 1025468
I am sorry but it does not work. Have you ever tried it??
0
 
LVL 1

Expert Comment

by:raky
ID: 1025469
Well it comes from the SQL server documentation never tried with ORACLE or PARADOX :-)
0
 
LVL 2

Expert Comment

by:Ezhil
ID: 1025470
SELECT  *  FROM {table_name | view_name}[(optimizer_hints)]
[, {table_name2 | view_name2}[(optimizer_hints)]

where

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



0
 
LVL 1

Expert Comment

by:raky
ID: 1025471
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 :-)
0
 

Author Comment

by:AC032698
ID: 1025472
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.
0
 
LVL 4

Accepted Solution

by:
sganta earned 880 total points
ID: 1025473
Hello AC !
I have the solution for  your problem.

ORACLE :
   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
  faster.

     
  SYNTAX :
  >>----------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.

EXAMPLE :
         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 */
                empno,ename,deptno
         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.
JESUS LOVES YOU - sganta

0
 
LVL 4

Expert Comment

by:sganta
ID: 1025474
Hi sganta!
It is great answer! Thank you very much. And Jesus loves you as well; excellent grade.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question