CODEWITHSUNDEEP
sqloracle-databaseindexingquery-performance
user2799601
user2799601

Reputation: 11

oracle functional index performance

I have a table with 226 million rows that has a varchar2(2000) column. The first 10 characters are indexed using a functional index SUBSTR("txtField",1,10).

I am running a query such as this:

select count(1) 
from myTable 
where SUBSTR("txtField",1,10) = 'ABCDEFGHIJ';

The value does not exist in the database so the return in "0".

The explain plan shows that the operation performed is "INDEX (RANGE SCAN)" which I would assume and the cost is 4. When I run this query it takes on average 114 seconds.

If I change the query and force it to not use the index:

select count(1) 
from myTable   
where SUBSTR("txtField",1,9) = 'ABCDEFGHI';

The explain plan shows the operation will be a "TABLE ACCESS (FULL)" which makes sense. The cost is 629,000. When I run this query it takes on average 103 seconds.

I am trying to understand how scanning an index can take longer than reading every record in the table and performing the substr function on a field.

Followup: There are 230M+ rows in the table and the query returns 17 rows; I selected a new value that is in the database. Initially I was executing with a value that was not in the database and returned zero rows. It seems to make no difference.

Querying for information on the index yields: CLUSTERING_FACTOR=201808147 LEAF_BLOCKS=1131660

I am running the query with AUTOTRACE ON and the gather_plan_statistics and will add those results when they are available.

Thanks for all the suggestions.

Upvotes: 0

Views: 221

Answers (1)

N West
N West

Reputation: 6819

There's a lot of possibilities.

You need to look at the actual execution plan, though.

You can run the query with the /*+ gather_plan_statistics */ hint, and then execute:

select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

You should also look into running a trace/tkprof to see what is actually happening - your DBA should be able to assist you with this.

Upvotes: 3

Related Questions