0

I am trying to query an Oracle SQL view set by a client to get a few sample records.

This view is so huge that SQL Developer gets hung up when I click on its name on the views list to try to display its records.

Client says I should put a where clause to limit the number of records.

The thing is I don't even know what to put in this where clause to get just a few sample records.

In MySQL I would just do SELECT * FROM huge_view LIMIT 10 (why it works is explained here: http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/)

The Oracle equivalent SELECT * FROM huge_view WHERE ROWNUM < 10 does not work like that, so the query still hungs up and no results.

Is there any way I can get a few random samples from this view without knowing anything about it?

NotGaeL
  • 8,344
  • 5
  • 40
  • 70
  • `ROWCOUNT` or `ROWNUM`? – ibre5041 May 27 '15 at 08:02
  • yeah, `ROWNUM`, sorry ^^' – NotGaeL May 27 '15 at 08:02
  • It this was a table you could use `SELECT * FROM huge_table sample(10);`. But I still think you're doing something wrong, such a simple query (without sort) should return instantly. – ibre5041 May 27 '15 at 08:05
  • Aaa I've got it. It is a view. The view the joins tables and Oracle decided to use hash join. You should use optimizer goal FIRST_ROWS (use hint `/*+ FIRST_ROWS(100) */`) to get result instantly. Oracle should prefer nested loops before hash tables. – ibre5041 May 27 '15 at 08:08
  • I asked the DBA client why it didn't work. He told me something like "the view is doing a very convoluted query and you should limit the number of records. Just put a where clause." I confirmed I could get a record using `SELECT * FROM huge_view WHERE reference='anitemreference'`. The problem is I don't know which item references are on this view so I tried the `ROWNUM < 10` thing, and also `OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY`. What I didn't try was `WHERE ROWNUM BETWEEN 1 and 10`, I am about to do it now. – NotGaeL May 27 '15 at 08:14
  • @ibre5041 You are right `SELECT /*+ FIRST_ROWS(10)*/ * FROM huge_view WHERE ROWNUM < 10;` did the trick. – NotGaeL May 27 '15 at 08:24

0 Answers0