In SQL Developer for VS Code 25.1.1, the following query is being used to retrieve procedure names, and populate the auto-completion popup:
Select Object_Name
From Sys.Dba_Procedures
Where Owner In ( 'PUBLIC','SYS',User )
And Object_Name Like :1
And Object_Type In ( 'PROCEDURE','FUNCTION' )
And Rownum < 50
Order By Procedure_Name;
That is not a new query; we've been there before [in the SQLcl / SQL Developer forums], but I'll say it again:
The above query (and possibly other, similar queries, using the same ROWNUM < limit + ORDER BY pattern) suffer from the following defect: the filter on the count of rows is applied before the SORT ORDER BY operation.
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 206 | 2 (100)| 00:00:01 | | |
| 1 | SORT ORDER BY | | 1 | 206 | 2 (100)| 00:00:01 | | |
|* 2 | COUNT STOPKEY | | | | | | | |
| 3 | PARTITION LIST ALL | | 1 | 206 | 1 (100)| 00:00:01 | 1 | 2 |
|* 4 | EXTENDED DATA LINK FULL| INT$DBA_PROCEDURES | 1 | 206 | 1 (100)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<50)
4 - filter(("OWNER"='PUBLIC' OR "OWNER"='SYS' OR "OWNER"=USER@!) AND ("OBJECT_TYPE"='FUNCTION' OR
"OBJECT_TYPE"='PROCEDURE') AND "OBJECT_NAME" LIKE :1)
This results in an arbitrary set of at most 49 rows to be fetched, and finally sorted by PROCEDURE_NAME—which happens to be always NULL in this case, by the way!—rather than doing the opposite (sort all rows, then fetch only the first rows from the ordered set) which could arguably be (slightly) slower, but might seem more consistent from a logical viewpoint.
And I also keep wondering why procedures from SYS and USER (the session user) should always be put on the same level—maybe I don't need anything from the SYS schema to begin with—especially if doing an infix search (like '%infix%'), which may obviously return many more object names than a mere prefix search (like 'prefix%').
If I connect as a user with DBA privileges, but distinct from SYS, and if I create a procedure named FEATURE_TEST in my own schema, e.g.:
create or replace procedure feature_test
is begin
null;
end feature_test;
/
As shown below, that procedure does not even appear in the auto-completion popup.
Best regards,