Querying table node is very slow

[PhilippSalvisberg]

Querying the table node takes the first time around 30 seconds. GUI is blocked during the time.

The reason is most probably the named query hist_tabs following query (based on SQL Developer statement history):

WITH 
  tabs AS (
     SELECT /*+ materialize */ object_name AS table_name
       FROM user_objects
      WHERE object_type = 'TABLE'
        AND generated = 'N'
   ),
   hist_tabs AS (
      SELECT /*+ materialize */ table_name
        FROM user_tab_cols
       WHERE column_name = 'VT$'
         AND hidden_column = 'YES'
   ),
   pk_tabs AS (
      SELECT /*+ materialize */ table_name 
        FROM all_constraints
        WHERE constraint_type = 'P' AND owner = USER
   )
SELECT /*+ordered use_hash(tabs) use_hash(hist_tabs) use_has(pk_tabs) */ tabs.table_name
  FROM tabs
  JOIN pk_tabs ON pk_tabs.table_name = tabs.table_name 
  LEFT JOIN hist_tabs ON hist_tabs.table_name = tabs.table_name
 WHERE hist_tabs.table_name is NULL
 ORDER BY tabs.table_name

[PhilippSalvisberg]

This should be better:

WITH 
  tabs AS (
     SELECT /*+ materialize */ object_name AS table_name
       FROM user_objects
      WHERE object_type = 'TABLE'
        AND generated = 'N'
   ),
   cols AS (
      SELECT /*+ materialize */ table_name, column_name, hidden_column
        FROM user_tab_cols
   ),
   hist_tabs AS (
      SELECT DISTINCT table_name
        FROM cols
       WHERE column_name = 'VT$'
         AND hidden_column = 'YES'
   ),
   pk_tabs AS (
      SELECT /*+ materialize */ table_name 
        FROM all_constraints
        WHERE constraint_type = 'P' AND owner = USER
   )
SELECT /*+ordered use_hash(tabs) use_hash(hist_tabs) use_hash(pk_tabs) */ tabs.table_name
  FROM tabs
  JOIN pk_tabs ON pk_tabs.table_name = tabs.table_name 
  LEFT JOIN hist_tabs ON hist_tabs.table_name = tabs.table_name
 WHERE hist_tabs.table_name is NULL
 ORDER BY tabs.table_name