Skip to Main Content
  • Questions
  • ANSI SQL is slower than Oracle SQL notation

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Olga.

Asked: September 22, 2020 - 1:18 am UTC

Answered by: Connor McDonald - Last updated: September 22, 2020 - 7:57 am UTC

Category: PL/SQL - Version: Exadata 12c

Viewed 100+ times

You Asked

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



and we said...

An ANSI join query is rewritten to use the Oracle syntax, but its possible that conversion may result in an execution plan difference.

But...we consider this to be a bug, because we never want any difference, so please report it to Support. What we recommend you do:

For each query, do

- start a new database session
- alter session set events = '10053 trace name context forever, level 1'
- explain plan for [your query]

This will create 2 trace files which you can provide to Support.

Support will be your first port of call here, but if you like, email both trace file to "asktom_us@oracle.com" and we'll also take a look.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.