You want fast access with a predicate that involves a function that checks for the first letter of the last name is 'A'. Here is the first query with the execution plan.

set autotrace traceonly explain;

select count(*) from emp_test where regexp_like( last_name, '^A' );



Execution Plan
----------------------------------------------------------
Plan hash value: 982230031

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3040 (1)| 00:00:37 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| EMP_TEST | 53500 | 365K| 3040 (1)| 00:00:37 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter( REGEXP_LIKE ("LAST_NAME",'^A'))



Without and index, a full table scan is performed. Here is a function and a function based index to improve the performance. I chose a bitmap index since the cardinality is 2, very low, either 0 or 1.

create or replace function check_name( name in varchar2 ) return number
DETERMINISTIC
is
begin
if regexp_like( name, '[A]' ) then
return 1;
end if;
return 0;
end;
/

set autotrace off

create bitmap index emp_test_ck_name_bmidx on emp_test( check_name( last_name ) );

set autotrace on traceonly explain;

select count(*) from emp_test where check_name( last_name ) = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1380382320

------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 17 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | BITMAP CONVERSION COUNT | | 10700 | 135K| 17 (0)| 00:00:01 |
|* 3 | BITMAP INDEX SINGLE VALUE| EMP_TEST_CK_NAME_BMIDX | | | | |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

3 - access("TEST"."CHECK_NAME"("LAST_NAME")=1)

Notice that the, very small, bitmap index is scanned and not the table.

There you have it.

David Bradford