Hello, I've noticed that sometime a query of an Insert statement written in ANSI SQL runs slightly slower than a query constructed using Oracle native syntax. But recently I had a case when a native SQL executed within seconds, while the ANSI SQL could not complete.
I resolved the problem by converting the ANSI SQL to Oracle native, but wanted to understand why it was happening and whether any guidelines could be offered on when not to use ANSI SQL.

The long running query was pulling data from one huge fact table and a a few not so big dimension tables.
The fact table is partitioned by date range and sub-partitioned by list, and was created with PARALLEL 16 clause.
The /*+ FULL */ hint in the query is used to force Exadata smart scan. The query looked like that:

SELECT /*+ FULL (fact) */
  fact.partition_key_date,
  fact.subpartition_col,
  SUM(CASE WHEN fact.col = 'S' THEN (ABS (sale_amt)) END)
      OVER PARTITION (fact.col_a, fact.col_b) as SALE_AMT,
  SUM(CASE WHEN fact.col = 'O' THEN (sale_amt) END)
      OVER PARTITION (fact.col_a, fact.col_b) as OTHER_AMT
FROM dim1
     JOIN fact
        ON dim1.key = fact.attr1
     JOIN dim2
        ON fact.attr2 = dim2.key
          AND dim2.col = 'ABC'
WHERE fact.partition_key_date = :dt_parm
  AND fact.subpartition_col = 'KYZ'
  AND fact.attr2 = 'X'
  AND NVL(fact.attr3, 'N') <> 'Y'
  AND NOT EXISTS (
                 SELECT 1
                 FROM dim3
                 WHERE fact.attr4 = dim3.key);

It was queued for parallel execution for more than an hour before starting the 32 parallel slaves. It was running for many hours and never completed.
But when the analytical SUM function was removed from the selected column list it completed within seconds.
Yet with the same analytical function and with Oracle SQL syntax, as shown below, the query also completed in 5 seconds.

SELECT /*+ FULL (fact) */
  fact.partition_key_date,
  fact.subpartition_col,
  SUM(CASE WHEN fact.col = 'S' THEN (ABS (sale_amt)) END)
      OVER PARTITION (fact.col_a, fact.col_b) as SALE_AMT,
  SUM(CASE WHEN fact.col = 'O' THEN (sale_amt) END)
      OVER PARTITION (fact.col_a, fact.col_b) as OTHER_AMT
FROM dim1,
     fact,
     dim2
WHERE fact.partition_key_date = :dt_parm
  AND fact.subpartition_col = 'KYZ'
  AND fact.attr2 = 'X'
  AND NVL(fact.attr3, 'N') <> 'Y'
  AND dim1.key = fact.attr1
  AND fact.attr2 = dim2.key
  AND dim2.col = 'ABC'
  AND NOT EXISTS (
                 SELECT 1
                 FROM dim3
                 WHERE fact.attr4 = dim3.key);