Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Performance of an sql query with 16 million rows

879540Sep 6 2011 — edited Sep 7 2011

Hello,

I have about 16 million records in a project table, lets call it the ENCOUNTER table.

I have an ENCOUNTER_NUMBER to be searched for which is VARCHAR2(30) and has a unique constraint on it. The results are to be ordered by ENCOUNTER_NUMBER.

The search string that the user enters is always considered to be a contains search and so a % is prefixed and suffixed to the search string.

I have created a functional index on UPPER(ENCOUNTER_NUMBER)

More the number of matches to the search query, the less the time taken. If there are 100 matches or more, the query returns within a second.
If there is only one match it takes atleast 10 seconds.

I am not sure at this stage why there is huge difference. Our client would be happy with 5 seconds.

Can anyone help me with this please, if at all it is possible to get the search quicker

Any help would be greatly appreciated.

Many thanks
Marilyn

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 5 2011

Added on Sep 6 2011

#general-database-discussions

10 comments

669 views