CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle-apex
Mina
Mina

Reputation: 315

Use an index in a SELECT statement in Oracle

I created an index called Z7 and I want to use it in a SELECT statement like this:

SELECT * FROM Employees WITH (INDEX(Z7)) WHERE First_Name NOT LIKE ('J%');

But it shows me this error:

ORA-00933: SQL command not properly ended

Upvotes: 1

Views: 9257

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21053

Contrary to the LIKE predicate that can be resolved with a index range scan there is no index access path in Oracle that would implement a NOT LIKE that would need to do two range scans before and after your LIKE value.

Let's illustrate it in this setup with a table that has only one row satisfying your not like predicate.

create table Employees as
select 'J'||rownum as First_Name, lpad('x',1000,'y') pad from dual connect by level <= 10000
union all 
select 'Xname', 'zzzzz' from dual;

create index Z7 on Employees (First_Name);

The query

SELECT * FROM Employees  WHERE First_Name NOT LIKE ('J%');

performs as expected a FULL TABLE SCAN

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |    37 | 37259 |   398   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |    37 | 37259 |   398   (1)| 00:00:01 |
-------------------------------------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("FIRST_NAME" NOT LIKE 'J%')

You may hint Oracle to use index as follows (note that your syntax FROM Employees WITH (INDEX(Z7)) WHERE is wrong and causing your error!)

SELECT /*+ index(Employees Z7) */ * FROM Employees  WHERE First_Name NOT LIKE ('J%');

This will indeed cause the index usage, but not in the sence you probablly intended.

If you examines the execution plan you'll see the INDEX FULL SCAN, that means, Oracle walks through the complete index from A to Z, filter the entires in the index that are not like your predicates (see the FILTER in the plan below) and for the selected keys access the table.

So generally you'll be not happy with this plan for a large index as it will take substantially longer that the full table scan.

-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |  9433 |  9276K|  2912   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES |  9433 |  9276K|  2912   (1)| 00:00:01 |
|*  2 |   INDEX FULL SCAN                   | Z7        |  9433 |       |    25   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("FIRST_NAME" NOT LIKE 'J%')

See here how to produce the execution plan for the query

Upvotes: 3

Related Questions