解决办法：不对列进行连接操作，而是对要比较的字面量应用函数，从而满足列的特定要求。

点击(此处)折叠或打开

--创建表
hr@TESTDB11>create table employees1 as select * from employees;
Table created.
--在first_name和last_name列上分别创建索引
hr@TESTDB11>create index idx_employees1_fn on employees1(first_name);
Index created.
hr@TESTDB11>create index idx_employees1_ln on employees1(last_name);
Index created.
--对列进行字符串的连接,没有走索引
hr@TESTDB11>select e.first_name, e.last_name from employees1 e where first_name || \' \' || last_name = \'Douglas Grant\';

点击(此处)折叠或打开

--调整之后走索引的可取的做法
hr@TESTDB11>select e.first_name, e.last_name from employees1 e where first_name = \'Douglas\' and last_name = \'Grant\';

点击(此处)折叠或打开

--姓名一起存储在一个变量中的解决办法
hr@TESTDB11>select first_name, last_name from employees1
2 where first_name = substr(\'&&name\', 1, instr(\'&name\', \' \') - 1)
3 AND last_name = SUBSTR(\'&name\', INSTR(\'&name\', \' \') + 1);
old 2: where first_name = substr(\'&&name\', 1, instr(\'&name\', \' \') - 1)
new 2: where first_name = substr(\'Douglas Grant\', 1, instr(\'Douglas Grant\', \' \') - 1)
old 3: AND last_name = SUBSTR(\'&name\', INSTR(\'&name\', \' \') + 1)
new 3: AND last_name = SUBSTR(\'Douglas Grant\', INSTR(\'Douglas Grant\', \' \') + 1)

ITPub博客

对列进行连接操作会影响索引的使用