OS: CentOS 5
Oracle 10.2.0.1
We are facing an weird issue.
The table is like
create table A (
id varchar2(200) primary key,
...
)
ID's value is a little special, as it starts with "*" (Asterisk), and its pattern is '*' + 'onestring' + 'uuid'.
We have a IN clause query with 20 arguments such as:
select * from A where id in (
'*line_item.name.uuid1.......',
'*line_item.name.uuid2.......',
'*line_item.name.uuid3.......',
'*line_item.name.uuid4.......',
'*line_item.name.uuid5.......',
'*line_item.name.uuid6.......',
'*line_item.name.uuid7.......',
'*line_item.name.uuid8.......',
'*line_item.name.uuid9.......',
'*line_item.name.uuid10.......',
'*line_item.name.uuid11.......',
....
)
The table's row count is 30M, and row count of the primary key index is also 30M.
select index_name, blevel, num_rows from user_indexes where table_name = 'A';
20 is much less than 30M, but Oracle's execution plan for this query is full table scan of A table.
I try to remove the '*' from the id, and then the execution plan is range index scan, and then get the record by row id. I think that it should work this way.
Is '*' special character in Oracle 10g?