CTAS Statement

Prathap G
A free video tutorial from Prathap G
Technical Enterprise Architect
4.1 instructor rating • 3 courses • 10,929 students

Learn more from the full course

Oracle SQL : Mastering Oracle SQL Performance Tuning

Oracle SQL Query Tuning and Architecture | Solve critical Oracle SQL query performance issues | Advanced SQL Tuning

06:26:10 of on-demand video • Updated October 2019

  • Step by Step approach on troubleshooting badly performing SQL queries.
  • Identify how internal Oracle components affects the performance of the SQL query.
  • Identify potential performance bottlenecks by reading the Explain plan of the queries.
  • Tips and Tricks to make the Oracle SQL query run faster.
  • Compare BAD vs GOOD Queries with Examples.
  • Understand how STATISTICS and CARDINALITY affects the performance of the SQL Query.
  • Implications of JOINS and ACCESS Methods on the performance of the Query.
  • Oracle Performance Tuning on both Oracle11g and Oracle12c Environment.
  • Advanced SQL Query Tuning Steps.
English [Auto] Hey guys. Welcome back. In this section look at one more way to include both comments or polygons or statement. So let us take the simple example let it first connect to his schema and then set the order rates to statistics and I will also set the timing called parameter. It will let us know how much time the query took for completion. Let me first create a table sales and the score could be one which is an exact copy of sales and the score copy table let me not insert some data into the new table that we created. If you see the query below I am bumping up the data by including the union all of either. The reason I am doing it is because I want to induce high data I want you to be inserted that as part of the query and we know execute the query to see that it in certain more than 4.5 million records. And it took close to 10 seconds to perform the operation. Let us commit to operation. Let me know execute the same query again but this time I take a different approach. If we show that it will first drop the tables sales and the score could be won. So now instead of creating that table again and inserting that data into the newly created table I would recommend only one statement that cleared the table and then insert that data at the same time. The approach I have used here is called CPA as it stands for Create people as select. That is we are creating the table by selecting the data from the source. Support the creation of the table and inserting the data into the table happen to the same statement. So let me now run this query it has now created a table and I also have inserted that data into the table. We now see that the runtime of the query is close to two seconds and we see nearly 60 to 70 percent improvement in that war. So how did CPS come and improve the response time of the query and in what scenarios could we use the CTA is. The CTA is common takes the advantage of direct path in sex that is data is returned directly to the data files bypassing the buffer cache. The usage of CTA approach will also reduce the amount of freedom log that it generates. I have seen many instances in various projects where this approach has helped me a lot to resolve the performance issue in appeals equal court. But in what scenarios can one can use CTA as command in many of the appeals legal procedures. I've seen developers creating some temporary data based on some logic that will move the data into a temporary table and in the subsequent queries they would use this temporary table either as part of sub queries or a joint to perform series of operation before reaching the final output. And before the control comes out of the procedure a developer would go ahead and delete the data from the temporary table. So instead of using the insert statement to move the bulk data into a temporary or a staging table you could use CTA as command to move that data into a static table. From this example we saw how CTA use improved the performance. So instead of using the insert statement to build a bulk data into a temporary or a staging table you could use CTA as command to move that data into staging peoples. It would significantly improve the performance of your overall political statement. From this example we saw how CPS improved the performance. So next time you have a performance issue with any of the bulk insert statements where you're storing some temporary data. Think about disruption. With that I will wrap this lesson and see you all in the next lesson.