SQL Query : should return Single Record if Search Condition met, otherwise return Multiple Records

I have table with Billions of Records, Table structure is like :

ID NUMBER PRIMARY KEY,
MY_SEARCH_COLUMN NUMBER,

MY_SEARCH_COLUMN will have Numeric value upto 15 Digit in length.

What I want is, if any specific record is matched, I will have to get that matched value only,

i.e. : If I enter WHERE MY_SEARCH_COLUMN = 123454321 and table has value 123454321 then this only should be returned.

But if exact value is not matched, I will have to get next 10 values from the table.

i.e. : if I enter WHERE MY_SEARCH_COLUMN = 123454321 and column does not have the value 123454321 then it should return 10 values from the table which is greater than 123454321

Both the case should be covered in single SQL Query, and I have have to keep in mind the Performance of the Query. I have already created Index on the MY_SEARCH_COLUMN columns, so other suggestions are welcome to improve the Performance.

Solution

This could be tricky to do without using a proc or maybe some dynamic SQL, but we can try using ROW_NUMBER here:

WITH cte AS (
    SELECT ID, MY_SEARCH_COLUMN,
        ROW_NUMBER() OVER (ORDER BY MY_SEARCH_COLUMN) rn
    FROM yourTable
    WHERE MY_SEARCH_COLUMN >= 123454321
)

SELECT *
FROM cte
WHERE rn <= CASE WHEN EXISTS (SELECT 1 FROM yourTable WHERE MY_SEARCH_COLUMN = 123454321)
                THEN 1
                ELSE 10 END;

The basic idea of the above query is that we assign a row number to all records matching the target or greater. Then, we query using either a row number of 1, in case of an exact match, or all row numbers up to 10 in case of no match.