I was unable to modify Rich's query in such a way that it could return the
EMAIL column without resorting to a full table scan. Then I came up with
this:
SELECT sm.employee_id, sm.upper_last_name,email
FROM (SELECT employee_id,
upper_last_name,
INSTR (upper_last_name,'KIN',1,1) AS instring_match,email
FROM (SELECT /*+ and_equal(em EMP_EMP_ID_PK EMP_NAME_IX) */
em.employee_id, UPPER (em.last_name) AS upper_last_name,email
FROM qsn_app.employees em)) sm
WHERE instring_match > 0;
I have no idea why it works.
Regards,
Mike
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1176990071
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 60 | 2
(0)| 00:00:22 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 5 | 60 | 2
(0)| 00:00:22 |
|* 2 | INDEX FULL SCAN | EMP_NAME_IX | 5 | | 1
(0)| 00:00:11 |
-------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5C160134 / EM@SEL$3
2 - SEL$5C160134 / EM@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(INSTR(UPPER("EM"."LAST_NAME"),'KIN',1,1)>0)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EM"."EMPLOYEE_ID"[NUMBER,22], "EM"."LAST_NAME"[VARCHAR2,25]
2 - "EM".ROWID[ROWID,10], "EM"."LAST_NAME"[VARCHAR2,25]
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
