0

I have tried to optimize a select query with a temp table (I'm more used to SQL Server so maybe there's a better way).

The query is basically this (a bit simplified): create table, fill it, use it, drop table.

create global temporary table trucks (
    nb_trucks int,
    warehouse int
)
on commit preserve rows
;
insert into trucks (
    nb_trucks, warehouse
)
select
    sum(pla.NB_TRUCKS)
    ,pla.id_site
from ASI_MAS_PLA_PLANIFICATION pla
group by
    pla.id_site
;
select distinct
op.operation_id as OPERATION_CODE,
pla.id_site as WAREHOUSE_ID,
(
    select
    coalesce(sum(nb_trucks), 0)
    from trucks
    where
        and trucks.warehouse = pla.id_site
) as NB_TRUCKS,
from ASI_MAS_PLA_PLANIFICATION pla
inner join ASI_MAS_PLA_CL_PLANIF_OP op
    on op.planif_operation_id = pla.z_planif_operation_id
where
    pla.z_remainder = 0
group by
    op.operation_id
    ,pla.id_site
order by
    op.operation_id
;
truncate table trucks;
drop table trucks;

For various technical reasons, I need to execute these actions in a single script (actually I only need to execute the "select" part, but not using a temp table sends the execution time through the roof).

I have placed the script inside a begin ... end; block but it doesn't like that I "create" a table as first instruction.

As I saw here, I'm currently trying to wrap each statement with a "execute immediate" like this but I'm not very fond of this solution.

How can I execute multiple statements, including a "create", inside a single SQL script?

Cœur
  • 37,241
  • 25
  • 195
  • 267
thomasb
  • 5,816
  • 10
  • 57
  • 92

1 Answers1

0

Without an explain plan is hard to understand. Maybe a "with" clause help:

WITH trucks AS
 (SELECT /*+ materialize */
   SUM(pla.nb_trucks) nb_trucks,
   pla.id_site warehouse
    FROM asi_mas_pla_planification pla
   GROUP BY pla.id_site)
SELECT DISTINCT op.operation_id AS operation_code,
                pla.id_site AS warehouse_id,
                (SELECT coalesce(SUM(nb_trucks), 0)
                   FROM trucks
                  WHERE trucks.warehouse = pla.id_site) AS nb_trucks
  FROM asi_mas_pla_planification pla
 INNER JOIN asi_mas_pla_cl_planif_op op
    ON op.planif_operation_id = pla.z_planif_operation_id
 WHERE pla.z_remainder = 0
 GROUP BY op.operation_id,
          pla.id_site
 ORDER BY op.operation_id;

but I think there are some problems with the logic of the query. I think you are not counting correctly the trucks. But you know if this is correct.

Maybe this query is better?

SELECT op.operation_id AS operation_code,
       pla.id_site AS warehouse_id,
       nvl(SUM(pla.nb_trucks), 0) nb_trucks
  FROM (SELECT /*+ materialize*/ id_site,
               z_planif_operation_id,
               SUM(nb_trucks) nb_trucks
          FROM asi_mas_pla_planification
         WHERE z_remainder = 0
         GROUP BY id_site,
                  z_planif_operation_id) pla
 INNER JOIN asi_mas_pla_cl_planif_op op
    ON op.planif_operation_id = pla.z_planif_operation_id
 GROUP BY op.operation_id,
          pla.id_site
 ORDER BY op.operation_id;

Regards

StefanG
  • 320
  • 1
  • 3
  • 10