.jpg)
Schema: HR
DB Version: Oracle AI Database 26ai Free Release 23.26.1.0.0 - Develop, Learn, and Run for Free
I was recently preparing a presentation called “Why the f*** Oracle does not use my index! [*fabulous]. I was working on demo scripts and at some point I got frustrated — a query that was supposed to use an INDEX SCAN path stubbornly kept doing a FULL TABLE SCAN.
Actually, it was a pretty nice case, fitting the topic — but I was supposed to be the one surprising the audience with insightful examples, not the examples surprising me!
Step by step:
I created an index I on the JOB_TITLE column in the JOBS table.
create index IF NOT EXISTS i on jobs(job_title); Index I created.
select job_id,
job_title
from jobs
where job_title = 'Programmer';
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| JOBS | 1 | 27 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
exec dbms_stats.GATHER_TABLE_STATS('HR', 'JOBS'); PL/SQL procedure successfully completed.
select job_id,
job_title
from jobs
where job_title = 'Programmer';
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| JOBS | 1 | 27 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Maybe the clustering factor messed things up? Let’s take a look.
select clustering_factor from user_indexes where index_name = 'I'; CLUSTERING_FACTOR ----------------- 7
Clustering factor is low. That’s not it.
We live in the AI era, right? Let’s ask the Agent!
Why does the query:
select job_id,
job_title
from jobs
where job_title = 'Programmer';
not use the index? Please analyze the script that creates the index and the db26_hr database.
The query does not use the index because:
-
The JOBS table has only 19 rows (it is very small).
The Oracle optimizer decides that a full table scan is faster than using an index for such a small dataset. - There are no visible indexes on the JOB_TITLE column in the metadata (USER_IND_COLUMNS returns an empty result).
- The index named I or TEST_IDX_JOB_TITLE is not visible in USER_INDEXES — it was likely not created or is not visible to the optimizer.
Summary: Oracle does not use the index because the table is very small and/or the index does not exist or is not visible in the metadata. This is typical optimizer behavior for small tables.
Look at point 2. NO visible indexes? What do you mean there is no index? I clearly created it and even got the message “Index I created”. Plus we already checked it — the clustering factor of that index!
Let’s check it ourselves.
select * from user_ind_columns where table_name = 'JOBS' and column_name = 'JOB_TITLE'; no rows selected
So it exists, but it doesn’t.
Let’s ask the AI again.
The index named I was not created because it does not appear in the metadata (USER_INDEXES, USER_IND_COLUMNS). This means the statement:
-
did not execute successfully. Most likely causes:
- The IF NOT EXISTS syntax was not recognized by the SQL parser (even in version 23c, it may be disabled or require additional settings).
- An index named I was previously created and dropped, and its metadata was not properly refreshed.
- An error occurred during index creation but was not reported in the session (e.g. missing commit, privilege issue, name conflict).
- The index was created in a different schema or on a different table.
Summary: Oracle did not create index I because the statement was not properly executed or is not supported in your environment. Use the classic syntax without IF NOT EXISTS and check whether the index appears.
Points 1–3 are not very convincing, but point 4… there might be something to it. Let’s check.
select table_name,
clustering_factor
from user_indexes
where index_name = 'I';
TABLE_NAME CLUSTERING_FACTOR
------------------------
EMPLOYEES 7
And bingo!
The index was not created on the JOBS table because an index named I already existed — but on a different table!
The IF [NOT] EXISTS syntax does not verify the “correctness” of the statement. If an index with the given name already exists — even on another table — the statement completes successfully. And we happily assume we now have an index on the JOBS table, while in reality our queries keep doing a FULL TABLE SCAN.
This functionality is very useful, but more than ever we need to be careful with applying best practices — especially proper object naming. If we adopt a naming standard and consistently create index names with a fixed structure, we can avoid this kind of trap.
One of the most common conventions is to include the table name (or alias) and the column names in the index name. For example, our index could be called: JOB_TITLE_IDX. With such a structure, mistakes like the one in our example would simply be eliminated.
create index IF NOT EXISTS job_title_idx on jobs(job_title);
Index I created.
select job_id,
job_title
from jobs
where job_title = 'Programmer';
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| JOBS | 1 | 27 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Komentarze
Prześlij komentarz