1

I have a view my_view

I can query it like: 

select * From my_view;

This takes about 4 seconds to run and returns about 100 rows of 10 columns (varchar2 and number - nothing fancy)

I want to take that results-set and insert it into a table.

I do this like: 

create table my_table as select * from my_view;

and this takes about 45 minutes to complete.

I can create the table alone in no time at all: 

create table my_table as select * From my_view where 1=0;

executes in a fraction of a second.

And I can insert 100 rows in no time at all: 

insert into table (c1, c2, c3, c4) values ('a1', 'b1', 'c1', 'd1') 
insert into table (c1, c2, c3, c4) values ('a2', 'b2', 'c2', 'd2') 
insert into table (c1, c2, c3, c4) values ('a3', 'b3', 'c3', 'd3') 
insert into table (c1, c2, c3, c4) values ('a4', 'b4', 'c4', 'd4')     
insert into table (c1, c2, c3, c4) values ('a5', 'b5', 'c5', 'd5')

also executes in less than a second - so for a hundred rows, a few seconds would make sense. 10 even would be no problem.

It seems like, even if insert as select command required the view to be queried in it's entirety - 100 or so times - once for each row inserted, it would still run faster.

What am I missing?

My questions are:

  • Why is it so slow?

    • If you guys don't know - how can I begin to debug it for an oracle database? How can I see what's happening under the hood?

  • How can I make it run faster, if my end goal is to write a robust procedure that'll take the data and archive it in a different table?

    • Is there some special syntax I can use to get the DB to cache the query-result better?
Paul
  • 3,318
  • 8
  • 36
  • 60
  • Are you sure that your session isn't blocked? – Lukasz Szozda Aug 10 '17 at 10:06
  • @lad2025 the query eventually completes - it just takes a very long time. I've tried a few times and get the same results. Is there a way I can check with more certainty? – Paul Aug 10 '17 at 10:20
  • 1
    Are you sure the view only has 100 rows - and not that your client (e.g. the data grid in SQL Developer) is only showing you the first 100? How long does `select count(*) from my_view` take, and what number does it report? If it really is that small total number of rows, does your view perhaps have join to a table accessed via a database link? Looking at the execution plans for the plain `select` and the `insert` might reveal something useful. – Alex Poole Aug 10 '17 at 10:30
  • Please run `EXPLAIN PLAN FOR select * FROM your_view`, then run `SELECT * FROM table( dbms_xplan.display)` and append a result of last query to the question. Simillary, run `EXPLAIN PLAN FOR create table AS SELECT ....`, then again `SELECT * FROM table( dbms_xplan.display)` and append a result of last query to the question too. There are basic steps to start investigate a performance problem. – krokodilko Aug 10 '17 at 10:33
  • @AlexPoole it has 161 to be completely exact. When I Run count(\*) from my_view it says "All Rows Fetched: 1 in 4.127 seconds" Count(\*) 161 – Paul Aug 10 '17 at 11:12
  • @AlexPoole It is entirely possible that some of the dependencies are accessed via database link - but the select * from my_view still only takes a few seconds - would the database link affect the create table as select * from my_view differently? – Paul Aug 10 '17 at 11:14
  • @Paul - yes, it can do; look at the plans for the `select` and `create` (or `insert` if you pre-create an empty table). Adding those plans to the question would really help; [an MCVE](https://stackoverflow.com/help/mcve) would be better though. [Also see this](https://stackoverflow.com/q/2462901/266304). – Alex Poole Aug 10 '17 at 11:25

0 Answers0