0

As you know, if I execute a SQL statement, SQL Developer returns the first 50 rows. To make the database return all the rows, I sometimes select the output and press CTRL+A. So it reads all the rows from the database.

When I run the query below and select all the rows, it reads the whole rows in about 10 seconds. 

select c.cust_city,s.amount_sold from sales s, customers c
where s.cust_id = c.cust_id and
channel_id = 3;

However, if I write * in the selected columns area and run, and when I select all the rows, the execution never ends and the SQL Developer gets locked.

If I monitor with the real-time monitoring, I see that the query seems still being executed. But only the duration increases. None of other statistics doesn't change. So that means the query doesn't continue being executed.

So, what might be the reason of that the SQL Developer never shows the output? When I check the performance metrics, I see that it doesn't use all the resources of my PC. So it doesn't seem because of the PC resources I think. But I am not sure of course.

So what can be the reason of that?

Thanks in advance.

oramas
  • 881
  • 7
  • 12
  • Show sample data and expected result – Jens Feb 13 '19 at 10:16
  • 1
    *When I check the performance metrics, I see that it doesn't use all the resources of my PC* If you run a query you do not use the resources of the pc, you use the resources of the db server – Jens Feb 13 '19 at 10:18
  • Use the modern explitit join syntax, not the implizit using where statement: `select c.cust_city,s.amount_sold from sales s join customers c on s.cust_id = c.cust_id where channel_id = 3;` – Jens Feb 13 '19 at 10:19
  • Thanks for your answers. But what I seek was not a better query. I just wanted to have a long running query to learn the real-time sql monitoring tool. But when I try to select all the result, sql developer gets locked. So is it because of the lack of memory or sth? By the way, sales table has about 900K rows. Customers table has about 55K. And the result of the query has 540K rows. – oramas Feb 13 '19 at 10:22
  • The query executes fine, but when you pull all 600k rows, it hangs? Can I ask why you're doing this? Why not just page through the results as you need to browse them? – thatjeffsmith Feb 13 '19 at 11:52
  • 2
    @jens ansi join syntax won't make an impact on performance – thatjeffsmith Feb 13 '19 at 11:54
  • Thank you for your answer Jeff. Actually, I was trying to find a long running query. So when I execute this and try to select all, the SQL Developer showed as the query is executing for a long time. But when I check the monitoring tool, and saw that no reads etc are processing after some time, but the SQL Developer still shows that the query is being executed, I thought maybe its because of the lack of memory at somewhere. Because if I query two columns instead of * for the same query, I can select all the rows. No problem. But when I write *, execute, and try to select all, it hangs immdiatly – oramas Feb 13 '19 at 12:59
  • 1
    how many columns? and what are the data types? those look like the sample tables from the SALES HISTORY (SH) schema, which are pretty trivial. – thatjeffsmith Feb 13 '19 at 13:55
  • for the question like this, first thing to post is table structure – T.S. Feb 13 '19 at 20:02
  • Hi dear Jeff. Yes, the sample SH schema. And I shared the row counts in the previous posts. Thanks for your interest – oramas Feb 14 '19 at 09:53
  • Hi T.S. Actually, you are right. But I didn't share it since the tables I used is in the SH schema. But anyways, I will post the table structure as well in my next posts. Thank you for your recommend. Regards – oramas Feb 14 '19 at 09:55

0 Answers0