pl sql declare variable with query too

Question

I want to make a simple query in pl sql Please suggest and how to make it MORE FAST EXECUTE (maybe only 0.01 second in 1000000 data)

first query:

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

Result of first query will be used in second query.

select * 
  from traceability 
where endtime = [first query]

Please help me to implement that logic to pl sql

Thank you.

How is the first query different than `select max(datetime) from product`? What is the query plan for both queries? Are there indexes on `datetime` and `endtime`? — Justin Cave, Oct 01 '20 at 06:05
I want select all data from traceability with condition where from datetime product. I was try with indexes and it works. but to select table traceability with first query result not works. Do you have solution? — Ryan Pasaribu, Oct 01 '20 at 06:44
which oracle version are you using ? and also could you add sample data and expected result as well — Sujitmohanty30, Oct 01 '20 at 07:00
What does "result not works" mean? That you are getting incorrect results? If so, you're going to have to give us a reproducible test case that shows what you want and what you are getting. Or do you mean that you get the correct results but not quickly enough? If so, you'll need to at least show us a query plan, table definitions, etc. — Justin Cave, Oct 01 '20 at 07:05

score 1 · Accepted Answer · answered Oct 01 '20 at 07:10

Please find bellow an example with sample data.

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);

Your query shou be as follows

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

The query will lead to this execution plan. See here how to get the execution plan.

-------------------------------------------------------------------------------------------------
| 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"))

Note that in case that in the table TRACEABILITY will be a large number of rows with the max timestamp, you can also see a FULL TABLE SCAN in the line 1. Similar is valid for the PRODUCT table and the line 4

pl sql declare variable with query too

1 Answers1