Solved

How to use specific index with SELECT

Posted on 1998-05-21
10
341 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
  • 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Read about achieving the basic levels of HRIS security in the workplace.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now