1

Please let me know how can we to select count(*) from an oracle view faster when having large number of records, preferably using predefined tables like ALL_TABLES.

ath_123
  • 11
  • 2
  • You want to count the number of records faster than `count(*)` does it? All you can do is... count then. Were you hoping for something like the value you can get from `all_tables.num_rows`, which is only accurate (at best) at the point statistics are gathered? Unless you have a materialised view that number doesn't exist. Maybe you need to tune the query the view is using. – Alex Poole Apr 28 '16 at 11:59
  • Following up on Alex's comment: You may use something like `all_tables.num_rows`, if you gather statistics often enough, if the number of rows in the table NEVER varies rapidly, and if you only need a very rough estimate of the number of rows. It all depends very strongly on your needs and your specific situation. –  Apr 28 '16 at 12:10
  • 1
    @mathguy - and if the OP was asking about a table, not a view... But for tables, yes, agree. The main thing is realising it's going to be a rough guide unless the data is static - useful for order of magnitude but not much else really. (Also just found [this](http://stackoverflow.com/q/14125731/266304). – Alex Poole Apr 28 '16 at 12:12
  • 1
    An index on a small, not-null field might help a bit, though – Erich Kitzmueller Apr 28 '16 at 12:49
  • @ammoQ ... UNIQUE index ... – Mottor Apr 28 '16 at 13:06
  • 2
    Possible duplicate of [Faster alternative in Oracle to SELECT COUNT(\*) FROM sometable](http://stackoverflow.com/questions/1840538/faster-alternative-in-oracle-to-select-count-from-sometable) – Erich Kitzmueller Apr 28 '16 at 13:09
  • @Mottor: sure? For counting, it shouldn't make a difference if the index is unique or not. It's just important that the column is not nullable. – Erich Kitzmueller Apr 28 '16 at 13:10
  • @ammoQ Sorry. NOT NULL is enough. Where is my coffee? – Mottor Apr 28 '16 at 13:14

0 Answers0