Oracle table acting like a queue

Create an index on (PRIORITY, ID) and then the query can use an INDEX FULL SCAN to read the data in order instead of scanning the whole table.

Sample table and data

drop table my_table;create table my_table(    key varchar2(100) not null,    id number not null,    priority number not null,    constraint my_table_pk primary key (key));insert into my_tableselect level, level, levelfrom dual connect by level <= 100000;begin    dbms_stats.gather_table_stats(user, 'MY_TABLE');end;/

Normal explain plan with FULL TABLE SCAN

explain plan forselect keyfrom my_tableorder by priority, id;select * from table(dbms_xplan.display);Plan hash value: 3656711297---------------------------------------------------------------------------------------| Id  | Operation          | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |---------------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |          |   100K|  1562K|       |   637   (1)| 00:00:01 ||   1 |  SORT ORDER BY     |          |   100K|  1562K|  2760K|   637   (1)| 00:00:01 ||   2 |   TABLE ACCESS FULL| MY_TABLE |   100K|  1562K|       |   103   (1)| 00:00:01 |---------------------------------------------------------------------------------------

Create index for better index access plan

The cost does not look better at first. But the real version should be much faster because it will stop processing quickly.

create index my_table_idx on my_table(priority, id);explain plan forselect keyfrom my_tableorder by priority, id;select * from table(dbms_xplan.display);Plan hash value: 2209255802--------------------------------------------------------------------------------------------| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |              |   100K|  1562K|   577   (1)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| MY_TABLE     |   100K|  1562K|   577   (1)| 00:00:01 ||   2 |   INDEX FULL SCAN           | MY_TABLE_IDX |   100K|       |   292   (1)| 00:00:01 |--------------------------------------------------------------------------------------------