Index with <> '' works, with = guid doesn't work

[Former Member]

I've got SAP with Oracle 10.2.0.2.0.

I have a table with the field GUID. There is a secondary index on the fields GUID and MANDT. In the table are 22.000.000 entries, but the field GUID is never filled.

When i do a select like the following, it tooks a very long time (about 2 minutes):

select * from table where guid = lv_guid.

(lv_guid is a guid)

The following select however is extremly quick:

select * from table where guid <> ''.

The following select is also very quick:

select * from table where guid <> '' and
                                      guid = lv_guid.

Can anybody tell me why this could be the case?

[Former Member · Accepted solution]

Michael

Have a look on explain plan for select * from table where guid = lv_guid.Then u can identify statement is using index or not If it is accessing triough full table scan then u need to recerate index.

However this query is exact match.hope u have latest stats generated for the table.

Once u know executionm plan u can do somthing.

Let me know r u a DBA?

Regards

Vinod

[Former Member]

Sorry for not answering so long, I was a bit stressed. We found a solution:

When deleting the statistics from the table index, the secondary index was used and the SELECT was very fast. Now we forced SAP to use the index with an index hint like

%_HINTS ORACLE 'INDEX("table","table~indexname")'

(we used the correct syntax as described in SAP-Note 130480, the above is just an example).

Now the index is used, and the SELECT is very fast.

Thanks for you help!

[lbreddemann]

Hi Michael,

this looks like caching effects.

Please check if the first query still takes that long after you ran it once.

From the description i don't thing that for query 2 a index can be used when there is none for query 1.

So I <b>guess</b>: with query 1 you pump all the table into the buffer cache and every subsequent statement on it will be much faster due to lower physical I/O.

For deeper analysis much more information would be needed:

- table structure

- indexes

- stats for table, columns, indexes

- explain plan for each statement

- db parameter setup ...

Would probably better to use a support message for this then.

KR Lars