使用CTA测试您的查询。你们会发现，完成这项工作几乎需要同样的时间。当您只选择表时，几乎所有的代码编辑器都会首先带来第一个可用的结果。

CREATE TABLE TEST
AS
select * 
from employees a, department b 
where a.empoyee_id = 10
and a.dept_no = b.dept_no
and a.salary between 10000 and 20000
and a.start_date between date1 and date2 
and a.end_date between date3 and date4

我相信您的过程比sql查询运行得慢，因为您使用括号的顺序不对。在sql中，salary、start-and end \u date需要匹配，在过程salary中，start-or end \u date需要匹配。排得越多，时间就越多。
其次，语法 min_x IS NULL or max_x IS NULL or x BETWEEN min_x AND min_y 可能导致不同的执行计划。我用一个最小的例子来测试：

CREATE TABLE t AS SELECT * FROM all_objects;
CREATE INDEX i ON t(created);
EXEC DBMS_STATS.GATHER_TABLE_STATS(null, 't');

SELECT /* my_static */ count(*) 
  FROM t 
  WHERE created BETWEEN DATE '2020-06-06' AND DATE '2020-06-07';

CREATE OR REPLACE PROCEDURE p(d1 DATE, d2 DATE, tag VARCHAR2) 
AS
  stmt VARCHAR2(1000);
  n NUMBER;
BEGIN
  stmt := 'SELECT /* '||tag||' */ count(*) FROM t 
            WHERE created BETWEEN :d1 AND :d2';
  EXECUTE IMMEDIATE stmt INTO n USING d1, d2;
END p;
/

CREATE OR REPLACE PROCEDURE p2(d1 DATE, d2 DATE, tag VARCHAR2) 
AS
  stmt VARCHAR2(1000);
  n NUMBER;
BEGIN
  stmt := 'SELECT /* '||tag||' */ count(*) FROM t 
            WHERE :d1 IS NULL OR :d2 IS NULL OR created BETWEEN :d1 AND :d2';
  EXECUTE IMMEDIATE stmt INTO n USING d1, d2, d1, d2;
END p2;
/

EXEC  p(DATE '2020-06-06', DATE '2020-06-07', 'my_dynamic1');
EXEC p2(DATE '2020-06-06', DATE '2020-06-07', 'my_dynamic2');

查找存储过程的执行计划要复杂一些，您需要深入研究 V$SQLTEXT 找到你的 sql_id ，输入 DBMS_XPLAN :

SELECT * FROM V$SQLTEXT 
 where sql_text LIKE ('%my_static%') 
    or sql_text LIKE ('%my_dynamic%');

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('7agywn91wbmt3',0));

sql和过程p1都有相同的执行计划

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   FILTER           |      |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| I    | >>325<|  2600 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

但是，过程p2有一个不同的计划，需要扫描6840个索引行而不是325个：

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |       |       |    51 (100)|          |
|   1 |  SORT AGGREGATE       |      |     1 |     8 |            |          |
|*  2 |   INDEX FAST FULL SCAN| I    |>>6840<| 54720 |    51   (2)| 00:00:01 |
------------------------------------------------------------------------------