Failing to generate explain plan for query with SQL model operation

SQL Optimizer SQL Optimizer for Oracle
1

Using 8.5.0.2031 and trying get performance data for Oracle 10g SQL that contains the MODEL operation. The Get Explain Plan function in SQL Optimizer generates the following error:

The Parser did not identify the text as a valid SQL statement.

WITH inventory
*AS (SELECT bu_nm *
. . .
*WHERE fullset.ranking <= fullset.min_rank *
GROUP BY bu_nm , brnch , sku_cd , bucket_name)
*MODEL RETURN UPDATED ROWS *
*PARTITION BY (bu_nm, brnch, sku_cd) *
*DIMENSION BY (bucket_name) *
*MEASURES (btotal, 0 b0, 0 b1, 0 b2, 0 b3, 0 b4, 0 b5, 0 b6, 0 b7, 0 b8, 0 b9, 0 b10, 0 b11, 0 b12_17, 0 b18_23, 0 b24_35, 0 b36_47, 0 b48_59, 0 b59_older) *
RULES (b0 [0] = btotal[bucket_name = '0'], b1 [0] = btotal[bucket_name = '1'], b2 [0] = btotal[bucket_name = '2'], b3 [0] = btotal[bucket_name = '3'], b4 [0] = btotal[bucket_name = '4'], b5 [0] = btotal[bucket_name = '5'], b6 [0] = btotal[bucket_name = '6'], b7 [0] = btotal[bucket_name = '7'], b8 [0] = btotal[bucket_name = '8'], b9 [0] = btotal[bucket_name = '9'], b10 [0] = btotal[bucket_name = '10'], b11 [0] = btotal[bucket_name = '11'], b12_17 [0] = btotal[bucket_name = 'age12_17'], b18_23 [0] = btotal[bucket_name = 'age18_23'], b24_35 [0] = btotal[bucket_name = 'age24_35'], b36_47 [0] = btotal[bucket_name = 'age36_47'], b48_59 [0] = btotal[bucket_name = 'age48_59'], b59_older [0] = btotal[bucket_name = 'age59_older'])

This type of SQL statement is not supported.

I can generate the explain plan (see attached) and execute the SQL from TOAD 10.6.1.3, but prefer to collect perf numbers from SQL Optimizer. As a workaround is it possible to tweak the SQL Optimizer so the execution will be performed without trying to pull the explain plan ?
Long term question is when the SQL Optimizer will support the MODEL functions (see SQL for Modeling)?

TIA,
Alex

xplan_w_model.png
xplan_w_model.png682×554 26.7 KB

2

Hi Alexander,

Thanks for your question.

The MODEL clause is not supported in our current version, we will consider to enhance to support this in the future.

Please let us know if you have any problems/questions while using our products.

Thanks,
Polly

3

So is it possible to execute a SQL query in SOO without getting the explain plan for the query?

4

Hi Alexander,

The Optimizer need to get the execution plan before execution of the SQL done, so it’s not possible to execute the SQL query without plans.

Regards,
Polly