CODEWITHSUNDEEP
oracle-databaseoptimization
michael nesterenko
michael nesterenko

Reputation: 14439

prevent full table scan

I have following query:

select id,
       c1,
       c2,
       c3 
from tbl t1
join 
(select id 
   from tbl t2
   where upper(replace(c5, ' ', '')) like upper(?)
) j 
on j.id = t1.id

? is some wildcard parameter string like %test%.

c5 column has index on the function used to access it:

create index tbl_c5_idx on tbl(upper(replace(c5, ' ', '')))

When I run just inner query it uses tbl_c5_idx, however when I run the whole query it turns into full table scan which is much slower.

Are there any way to avoid full table scans? Hints or rewrite join condition. I can not rewrite whole query as inner query is constructed dynamically depending on the input conditions.

Upvotes: 0

Views: 7721

Answers (3)

Sebas
Sebas

Reputation: 21532

  • if you're running the subquery only, it doesn't use the id column in the filters the way the parent query does, therefore the index can be used. In the parent query you are using the id as well, which prevents the index from being used. Maybe adding an index on (id, upper(replace(c5, ' ', ''))) would solve the problem.

  • Gaurav Soni is right: you don't need a subquery to achieve your goal.

  • always check performances rather than the explain plan. Performances might just be worst with your hint than without. Oracle is NOT stupid.

Upvotes: 1

Gaurav Soni
Gaurav Soni

Reputation: 6336

A very basic example to test your functionality 

create table test(id number,value varchar2(200));

insert into test values(1,'gaurav is bad guy');
insert into test values(2,'gaurav is good guy');

SELECT *
FROM test
WHERE UPPER (REPLACE (VALUE, ' ', '')) LIKE UPPER ('%gauravisbad%');

before creating index this is doing a full table scan for obvious reason ,because no index get created.

create index tbl_c5_idx on test(upper(replace(value, ' ', '')));

after

The reason why i am asking you to avoid inner join on the same table because you're using the table twice once to get your records from your filter condition where your index are used and then join on the basis of id which is preventing of using index ,because you dont have index on id column,this can be done with a simple filter condition. Please let me know if you're again finding out the same issue of full table scan ,or you're not getting the same result from this query .

Upvotes: 1

michael nesterenko
michael nesterenko

Reputation: 14439

Seems I found solution, or at least a thing that helps.

I used index hint, so access is done with tbl_c5_idx.

That is how final query looks now:

select /*+ index(t1) */ id,
       c1,
       c2,
       c3 
from tbl t1
join 
(select id 
   from tbl t2
   where upper(replace(c5, ' ', '')) like upper(?)
) j 
on j.id = t1.id

Upvotes: 0

Related Questions