0

We have a table of 627 columns and approx 850 000 records. We are trying to retrieve only two columns and dump that data in new table, but the query is taking endless time and we are unable to get the result in new table.

create table test_sample
as
select roll_no, date_of_birth from sample_1;

We have unique index on roll_no column (varchar) and data type for date_of_birth is date.

James Z
  • 12,209
  • 10
  • 24
  • 44
lobh
  • 33
  • 7

1 Answers1

2

Your query has no WHERE clause, so it scans the full table. It reads all the columns of every row into memory to extract the columns it needs to satisfy your query. This will take a long time because your table has 627 columns, and I'll bet some of them are pretty wide.

Additionally, a table with that many columns may give you problems with migrated rows or chaining. The impact of that will depend on the relative position of roll_no and date_of_birth in the table's projection.

In short, a table with 627 columns shows poor (non-existent) data modelling. Which doesn't help you now, it's just a lesson to be learned.

If this is a one-off exercise you'll just need to let the query run. (Although you should check whether it is running at all: can you see active progress in V$SESSION_LONGOPS?)

APC
  • 144,005
  • 19
  • 170
  • 281
  • 1
    I know people whose table(s) also have huge number of columns. When I asked "why", they said that they pay dear money to Oracle. Oracle allows 1000 columns per table. They are going to use every single one of them. (?!?) – Littlefoot Oct 09 '19 at 19:13
  • Dear APC,Thank you for your reply.but could you please suggest what will be the correct approach to do the same.As in 627 columns are created to maintain 120 days status and few more things. – lobh Oct 16 '19 at 05:33
  • @lobh - Sorry but there's no "correct approach". I can think of various things I might try if I were in your position but so much depends on the specifics of the base table and the derived table: data distribution, churn, skew; indexing; purpose and activity. Also, any tuning exercise must start with a clear understanding of why the query takes so long. Where does it spend its time? Please read [this excellent answer](https://stackoverflow.com/a/34975420/146325) to understand how you should go about the investigation. – APC Oct 16 '19 at 06:39