difference between "tab" table and all_tables in oracle

Question

what tables are returned by using (in oracle)

select * from tab

and

select * from all_tables

I would like to know the difference between two.

Solution

tab is an ancient data dictionary table that should never be used. It exists solely to provide backwards compatibility for scripts that were written potentially decades ago. tab does not get updated as new object types and new features get added.

• all_tables gives you information about all the tables that you have access to.
• tab gives you information about tables, views, and synonyms that you own (making it more similar to views like user_tables, user_synonyms, and user_views). It doesn't know about things like the recycle bin, though, so tab will show you all the tables with names like BIN$+K4PlriXSGetpagyHCvBGA==$0 that are in the recycle bin. Realistically, any object types that have been added at least since the Oracle 7 days are likely to create problems for legacy data dictionary tables like tab.

Comments

[Ahmet Korkmaz]

But why is select tname from tab where tabtype='TABLE' order by tname (40-60ms) 3x or 4x faster than select table_name from user_tables order by table_name (170-190ms) in my testing (12c on Windows, using custom command line client)?

[Justin Cave]

@ddevienne - I've not walked through the underlying code for the two views but I would assume that it's more costly to account for everything that has changed in the data dictionary since the Oracle 7 days than to ignore it. Filtering out things in the recycle bin, for example, adds time but improves the results. If you are hitting the data dictionary so often that user_tables isn't fast enough for you, my guess is that you are doing something wrong.