Skip to Main Content


How to check explain plan of a nested table query in SQL Developer or by some query?

Kinjan BhavsarJul 20 2021

Hi All,
I have a PL/SQL block in which I have used TYPE and I am querying a large table and using bulk collect storing the records into TYPE example:- tasks table so TASK_TYPE.
Then, after this, I am using this type in other queries using IN clause as the example shown below
select a1, a2 from table1 where task_id in (select t.task_id from TABLE(TASK_TYPE) t)
So, now in some cases, the query takes time and as it is a nested table, I can't see an explain plan in SQL Developer, it gives me an error regarding nested table. Is there a way I can find the explain plan of a query using nested table type? Is there a way, I can write some code in PL/SQL itself which stored the value in some table, etc?
I tried using a dummy table but that won't give me an exact idea of what exactly is happening in real time.

Post Details
Added on Jul 20 2021