Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE mytable (
  id   NUMBER(5,0) PRIMARY KEY,
  name VARCHAR2(10)
);
INSERT INTO mytable (id, name)
SELECT LEVEL, 'next'
FROM   DUAL
CONNECT BY LEVEL <= 1000
ORDER BY DBMS_RANDOM.VALUE();
1000 rows affected

SELECT * FROM MYTABLE WHERE NAME LIKE '%next%' AND ROWNUM = 1

ID
NAME

526
next

EXPLAIN PLAN FOR
SELECT * FROM MYTABLE WHERE NAME LIKE '%next%' AND ROWNUM = 1;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT

Plan hash value: 1231656364

------------------------------------------------------------------------------

| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |         |     1 |    20 |     2   (0)| 00:00:01 |

|*  1 |  COUNT STOPKEY     |         |       |       |            |          |

|*  2 |   TABLE ACCESS FULL| MYTABLE |  1000 | 20000 |     2   (0)| 00:00:01 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM=1)

   2 - filter("NAME" IS NOT NULL AND "NAME" LIKE '%next%')

Note

-----

   - dynamic sampling used for this statement (level=2)