0

I have a very large table MY_TABLE (100 million rows). I wish to select a sample of 5, say, records from this table.

What I can think of is getting 5 arbitrary primary keys as follows, this uses fast full scan as the explain plan shows:

select MY_PRIMARY_KEY_COLUMN from (select MY_PRIMARY_KEY_COLUMN, rownum as rn from MY_TABLE) where rn <=5

and then getting the records corresponding to these primary keys.

However this is still very very slow..

Can it be done more efficiently?

John Donn
  • 1,718
  • 2
  • 19
  • 45
  • Have you seen the answers here: https://stackoverflow.com/questions/9868409/how-to-get-records-randomly-from-the-oracle-database. ? – default locale Apr 26 '18 at 10:43
  • Why not just `select MY_PRIMARY_KEY_COLUMN from MY_TABLE where rownum <=5`? – Littlefoot Apr 26 '18 at 10:52
  • @defaultlocale the question is not about large tables and glancing, at least superficially, at the answers there, I don't see how these can help me. – John Donn Apr 26 '18 at 12:03
  • https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9531313800346986113 – Art Apr 26 '18 at 12:11
  • As it seems, you don't really care about these rows being uniformly distributed. Have you tried `select * from my_table where rownum <= 5`? – default locale Apr 26 '18 at 12:16
  • @defaultlocale no, as I said, this takes a lot of time, whereas the answer which I posted runs almost instantly.. – John Donn Apr 26 '18 at 12:18
  • @JohnDonn This is not normal. Is there anything suspicious in the execution plan of this query? You can also try to add an optimizer hint (e.g. "`select /*+ ALL_ROWS */ column from my_table where rownum<=5`"), just in case `rownum` triggers some check in query optimizer. – default locale Apr 26 '18 at 12:37
  • I would use a very small block sample e.g. `select * from my_table sample block(1e-6) where rownum <= 5`. This has to scan the whole table but it skips most of the blocks so might complete in an acceptable time. – William Robertson Apr 26 '18 at 12:48
  • 1
    @defaultlocale, WilliamRobertson Thank you, as I explained in the edited answer, I simply got confused. – John Donn Apr 26 '18 at 13:32

1 Answers1

1

As it looks, I got confused. As the commenters noticed, there should have been no problem with the query

select * from MY_TABLE where rownum <=5

but I somehow started to look at 

select MY_PRIMARY_KEY_COLUMN from (select MY_PRIMARY_KEY_COLUMN, rownum as rn from MY_TABLE) where rn <=5

which indeed runs very slowly..

Sorry for wasting everyone's time, the select * from MY_TABLE where rownum <=5 works perfectly.

John Donn
  • 1,718
  • 2
  • 19
  • 45