3 Replies Latest reply on Apr 13, 2017 7:53 PM by Gary Graham-Oracle

    Sql Developer - Query Builder Bug or working as designed?

    3427066

      Tried a few different versions of SQL Developer, but seem to have the same problem... Hoping I'm missing something simple....  

       

      I create a 1 column table under my schema , open SQL Worksheet, tab to Query Bulider, drag the newly created table to Query Builder tab, and SQL Developer hangs?  Spinning.... looking at active/last queries executed I see the below query....

       

      *If I select * from TEST_TABLE in SQL Worksheet, and then click on Query Builder, the TEST_TABLE graphic interface pops right up.

       

      Sql Developer version is 4.0.3, (same with 4.2) .  Oracle instance version is 12.0.

       

      select * from ( select pkc.OWNER as PK_SCHEMA_NAME, pkc.TABLE_NAME as PK_TABLE_N

      AME, pkc.COLUMN_NAME as PK_FIELD_NAME,        fkc.OWNER as FK_SCHEMA_NAME, fkc.T

      ABLE_NAME as FK_TABLE_NAME, fkc.COLUMN_NAME as FK_FIELD_NAME,        pkc.POSITIO

      N as ORDINAL from ALL_CONSTRAINTS fk,      ALL_CONS_COLUMNS fkc,      ALL_CONS_C

      OLUMNS pkc where (fk.CONSTRAINT_TYPE in ('R'))and       (fk.OWNER = fkc.OWNER)an

      d(fk.CONSTRAINT_NAME = fkc.CONSTRAINT_NAME)and       (fk.R_OWNER = pkc.OWNER)and

      (fk.R_CONSTRAINT_NAME = pkc.CONSTRAINT_NAME)and       (fkc.POSITION = pkc.POSITI

      ON) ) r where (((PK_SCHEMA_NAME like 'ORAUSR')) and ((PK_TABLE_NAME like 'TEST_

      TABLE'))) or (((FK_SCHEMA_NAME like 'ORAUSR')) and ((FK_TABLE_NAME like 'TEST_T

      ABLE')))

        • 1. Re: Sql Developer - Query Builder Bug or working as designed?
          Gary Graham-Oracle

          Are you saying that SQL is slow?   Why don't you trying running it directly from the worksheet, or SQLcl, or even in SQL*Plus (if you have it) to see if you get a similar slow response.

          • 2. Re: Sql Developer - Query Builder Bug or working as designed?
            3427066

            Query runs fine in SQL*Plus, I included the query in case it means anything to the folks who wrote the "drag and drop" code for query builder.

             

            *Updated... the original posted query runs fine.  I found the following query to take over 1 minute in SQL Plus...

             

            select * from ( select pkc.OWNER as PK_SCHEMA_NAME, pkc.TABLE_NAME as PK_TABLE_NAME, pkc.COLUMN_NAME as PK_FIELD_NAME,

                    fkc.OWNER as FK_SCHEMA_NAME, fkc.TABLE_NAME as FK_TABLE_NAME, fkc.COLUMN_NAME as FK_FIELD_NAME,       

            pkc.POSITION as ORDINAL from ALL_CONSTRAINTS fk,      ALL_CONS_COLUMNS fkc,     

            ALL_CONS_COLUMNS pkc where (fk.CONSTRAINT_TYPE in ('R'))and       (fk.OWNER = fkc.OWNER)and(fk.CONSTRAINT_NAME = fkc.CONSTRAINT_NAME)and      

            (fk.R_OWNER = pkc.OWNER)and(fk.R_CONSTRAINT_NAME = pkc.CONSTRAINT_NAME)and       (fkc.POSITION = pkc.POSITION) ) r

            where((PK_TABLE_NAME like 'TEST_TABLE')) or ((FK_TABLE_NAME like 'TEST_TABLE'))

            • 3. Re: Sql Developer - Query Builder Bug or working as designed?
              Gary Graham-Oracle

              If the dictionary query is slow, then normally that would be something to discuss with your DBA.  The difference between the two SQL statements occurs in the final where clause, with additional predicates applied on the PK and FK SCHEMA_NAME columns.  In my environment using local 11.2 XE and remote 12.2 EE instances I don't see much of a difference, but perhaps your 12.0 has many users.