Solved

How to use specific index with SELECT

Posted on 1998-05-21
10
346 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
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

 

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 220 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

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!

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

705 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