sql也使用查询声明变量。

Question

我想在pl中进行一个简单的查询，请建议并说明如何使它更快执行(在1000000数据中可能只有0.01秒)。

第一个查询：

select datetime 
  from product 
order by datetime desc 
FETCH NEXT 1 ROWS ONLY

第一个查询的结果将在第二个查询中使用。

select * 
  from traceability 
where endtime = [first query]

请帮助我将逻辑实现为pl sql。

谢谢。

Answer 1

请找下面的一个样本数据的例子。

create table product as
select rownum product_id, DATE'2020-01-01' + NUMTODSINTERVAL(rownum-1, 'second') datetime
from dual connect by level <= 10;

create index product_idx on product(datetime);

create table traceability as
select
rownum id, DATE'2020-01-01' + NUMTODSINTERVAL(rownum-1, 'second') endtime
from dual connect by level <= 10;

create index traceability_idx on traceability(endtime);

您的查询应该如下

select * 
  from traceability 
where endtime = 
 (select max(datetime) 
  from product );

查询将导致此执行计划。请参见here如何获得执行计划。

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     1 |    22 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | TRACEABILITY     |     1 |    22 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN           | TRACEABILITY_IDX |     1 |       |     1   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE            |                  |     1 |     9 |            |          |
|   4 |     INDEX FULL SCAN (MIN/MAX)| PRODUCT_IDX      |     1 |     9 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ENDTIME"= (SELECT MAX("DATETIME") FROM "PRODUCT" "PRODUCT"))  

注意，如果在表TRACEABILITY中有大量带有最大时间戳的行，您也可以在第1行中看到一个FULL TABLE SCAN。