In your query:
SELECT *
FROM Customer
WHERE Custname like ‘%WE%’;
You are searching for some characters in the middle of a string. In such cases, Oracle is unable to use an index and therefore the resulting execution plan will be a full table scan.
Given the performance characteristics you described, you are most like contending for IO bandwidth with other queries on your system in the afternoon.
Assuming your search criteria is actually selective (will return a small subset of the 4 million records in your table), and an index exists on the CUSTNAME column, it is possible to force an index access, which could be beneficial.
SELECT /*+ index (CUSTOMER CUSTNAME_IDX) */ *
FROM Customer
WHERE Custname like ‘%WE%’;
An example of where this was useful can be seen in an early askTOM question:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1434815000346190059 An alternative option would be to create a context index, which will allow you to do a more IO friendly full text searches of large amounts of data.
First you would need to create the context index. For example:
CREATE INDEX search_idx
on Customers(custname)
INDEXTYPE is CTXSYS.CONTEXT
/
To utilize the context index you would need to change your query to be something like:
SELECT *
FROM Customer
WHERE contains(Custname ‘WE’) > 0
/
To read more about it, see
https://docs.oracle.com/cd/B28359_01/text.111/b28303/ind.htm - g1020588