SQL - How you will avoid your query from using indexes?

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 19 of 171

How you will avoid your query from using indexes?

SELECT * FROM emp

Where emp_no+' '=12345;

i.e you have to concatenate the column name with space within codes in the where condition.

SELECT /*+ FULL(a) */ ename, emp_no from emp

where emp_no=1234;

i.e using HINTS

Total Answers and Comments: 8

Last Update: June 11, 2009

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

February 08, 2006 08:57:20

rajaramian

RE: How you will avoid your query from using indexes?

Write a statement without WHERE clause. This will avoid a query from using indexes.

Is this answer useful? Yes | No

May 19, 2006 16:26:00

rajanipriya

Member Since: May 2006 Contribution: 19

RE: How you will avoid your query from using indexes?

I really dont understand the query that was given as answer for this question.

Please explain it in detail. Help giving references.

Is this answer useful? Yes | No

April 04, 2007 08:56:44

subbaraovangalli

Member Since: March 2007 Contribution: 17

RE: How you will avoid your query from using indexes?

this means we can retrive data with out using index.
to retrive in thatway we dont use where clause

Is this answer useful? Yes | No

May 24, 2007 10:48:27

vishal

RE: How you will avoid your query from using indexes?

you can disable the index

ALTER INDEX <index_name> [ON <table_name>] DISABLE

Is this answer useful? Yes | No

June 05, 2007 19:21:48

madhug56

Member Since: November 2005 Contribution: 13

RE: How you will avoid your query from using indexes?

If you are using any column in where clause which has index on it and you dont want to use index for it then perform some operation on that column.

If you perform operation on the objects referenced in where clause then it does not use index.

eg: select empno ename
from emp
emp where substr(ename 1 5) 'Madhu';

The above query will not use any index even if its present on ename.

Is this answer useful? Yes | No

July 12, 2007 10:15:25

Arijit

RE: How you will avoid your query from using indexes?

use the FULL hint:-
select /*+FULL*/ * from emp where ....;

Is this answer useful? Yes | No

October 10, 2007 11:31:01

Srini

RE: How you will avoid your query from using indexes?

Its so distressing that no body could provide a better answer than a novice like me.

In the where clause change the order of conditions in such a way that:
5 value

cond1 & value 5

or value + 1 5

Is this answer useful? Yes | No

June 11, 2009 03:07:24

jyothi_rayala

Member Since: June 2009 Contribution: 2

RE: How you will avoid your query from using indexes?

One way is to diable the index on the column using syntax

Alter index indexname on table_name disable.

One way is to apply /*+no_index */ hint to the query

For eg: select ename /*+no_index */
from emp
where ename 'smith'

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank