1

I'm new to Oracle databases, having worked primarily in Microsoft SQL Server databases in the past.

My non-profit organization recently moved to a new CRM which allows us to use a web-based editor in their application to run P/L SQL. They recently notified me of the following:

We saw a script running early this morning when we got database alerts. It looks like the problem was a WITH statement being used instead of creating a table. That could cause performance issues. You can create a table using a WITH statement just not in lieu of creating a table. Please let us know if you run into any trouble, and we’ll help you out.

My question is why would this cause a performance issue - to describe the script generally, I am building a CTE through my 'WITH' statement, then performing a CREATE TABLE statement referencing that CTE as I join in other tables to the CTE into the created table.

I can't run an execution plan or any kind of diagnostics on my scripts, and my editor is a plain old text editor - making writing scripts difficult (I can't connect to the database via sql developer etc...).

Any wisdom would be appreciated :)

Thanks!

Tyler Hahn
  • 57
  • 5
  • 5
    You need to contact whoever sent that message to determine what the issue really is. I would guess that it is the *logic* of the CTE causing the problem, not merely the use of CTEs for creating a table. – Gordon Linoff Feb 11 '20 at 15:17
  • A `CTE` may couse problems if it consist of pure perfoming SQL **and** is used several times from the main query. In this case ideed a physical creation of the table instead of the CTE would help. Hard to say without the *execution plan*. But anyway you can and should check it - if you have a DB user, Notepad editor and command line is enough - see some hints [here](https://stackoverflow.com/a/34975420/4808122) – Marmite Bomber Feb 11 '20 at 17:59

0 Answers0