Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Frank.

Asked: August 24, 2016 - 9:22 pm UTC

Last updated: January 23, 2018 - 1:55 am UTC

Version: Oracle SQL Developer version 4.1.1.19

Viewed 10K+ times! This question is

You Asked

I have a query that returns 50,000 records, how can I quickly get to the last record? if I use the scroll arrow of slide, it takes a long time before it gets to the bottom of the 'Query Results" is there a quick way to go to the last record? I hope it makes sense.
Frank L.

and Connor said...

We retrieve data in a similar way to what you might see on a Google screen, ie, you see the first set of results but not *all* of them immediately. So if your query returns 50,000 rows, and you have the first 500 on screen...we have *not yet* fetched the other 49,500 rows from the database yet. We *know* what they will be (because we ran your SQL) but we dont *have* them yet in the SQL Developer client.

We do this because *most* of the time, people just want to see the first results as fast as possible, rather than wait for all of the data to be pulled across the network down to SQL Developer.

You can see the number of rows we fetch at a time under Tools => PReferences => DAtabase => Advanced => sql fetch array size.

If you hit Ctrl-End within the results grid, then we'll go to the bottom of the results, but be aware that in doing do, we're going to head back to the database to pick up all of the rest of the rows, so it might not be instantaneous.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Reverse order?

Duke Ganote, August 25, 2016 - 3:52 pm UTC

Assuming there's some order involved: If only the last 5000 records are interesting, just reverse the ordering. If you only want the top and bottom records, then something like this:

   select object_name, object_type, object_id, r#, rr#
     from (
   select o.*
        , row_number()over(order by object_id) r#
        , row_number()over(order by object_id DESC) rr#
     from all_objects o
    where owner like 'SYS%'
   )
    where rr# <= 5 or r# <= 5
   order by r#;

OBJECT_NAME                    OBJECT_TYPE                     OBJECT_ID         R#        RR#
------------------------------ ------------------------------ ---------- ---------- ----------
C_OBJ#                         CLUSTER                                 2          1      52440
I_OBJ#                         INDEX                                   3          2      52439
TAB$                           TABLE                                   4          3      52438
CLU$                           TABLE                                   5          4      52437
C_TS#                          CLUSTER                                 6          5      52436
WRH$_CELL_OPEN_ALERTS_PK       INDEX PARTITION                   1628511      52436          5
WRH$_CELL_OPEN_ALERTS_PK       INDEX PARTITION                   1628512      52437          4
WRH$_IM_SEG_STAT               TABLE PARTITION                   1628514      52438          3
WRH$_IM_SEG_STAT_PK            INDEX PARTITION                   1628515      52439          2
WRH$_IM_SEG_STAT_PK            INDEX PARTITION                   1628516      52440          1

10 rows selected.


However, few people can really grasp more than a dozen or so records.
Chris Saxon
August 26, 2016 - 2:08 am UTC

Agreed.

The most common "scroll to end" thing I see in SQL Developer is "I want to know how many rows I got, and dont want to re-run it with count(*)"

Skip around, too

Duke Ganote, August 25, 2016 - 4:23 pm UTC

Similarly, it's easy to get 'bite size' examples records from the entire ordering. Say you want to look at records from seven (roughly) equal-sized chunks. So then:

  select object_name, object_type, object_id, r#, rr#, mod#
    from (
  select o.*
       , row_number()over(order by object_id)-1 AS r#
       , TRUNC(MOD(row_number()over(order by object_id)
                  ,count(*)over()
                   / 7 -- divide into seven "chunks"
                  )
              ) AS mod#
       , row_number()over(order by object_id DESC)-1 AS rr#
    from all_objects o
   where owner like 'SYS%'
  )
   where rr# < 5 or r# < 5 or mod# < 5
   order by r#
SQL> /

OBJECT_NAME                    OBJECT_TYPE                     OBJECT_ID         R#        RR#       MOD#
------------------------------ ------------------------------ ---------- ---------- ---------- ----------
C_OBJ#                         CLUSTER                                 2          0      52439       1
I_OBJ#                         INDEX                                   3          1      52438       2
TAB$                           TABLE                                   4          2      52437       3
CLU$                           TABLE                                   5          3      52436       4
C_TS#                          CLUSTER                                 6          4      52435       5
XS_PRINCIPAL_INT               PACKAGE                             11352       7491      44948       0
XS_PRINCIPAL_INT               PACKAGE BODY                        11353       7492      44947       1
XS_PRINCIPAL                   PACKAGE BODY                        11354       7493      44946       2
DS_VARRAY_4_CLOB               TYPE                                11358       7494      44945       3
COLDICTREC                     TYPE                                11360       7495      44944       4
sqlj/translator/ClassPathEntry JAVA CLASS                          22653      14982      37457       0
sqlj/translator/ClassPath      JAVA CLASS                          22654      14983      37456       1
sqlj/translator/ClassFile      JAVA CLASS                          22655      14984      37455       2
/91e1f59f_ClassResolverImpl    JAVA CLASS                          22656      14985      37454       3
sqlj/translator/Main           JAVA CLASS                          22657      14986      37453       4
/d6c9df50_ServiceLocator       JAVA CLASS                          30147      22474      29965       0
/6612359c_GenericURLContext    JAVA CLASS                          30148      22475      29964       1
/f27212bf_GenericURLDirContext JAVA CLASS                          30149      22476      29963       2
/b8950380_ldapURLContext       JAVA CLASS                          30150      22477      29962       3
/2342c2e_ldapURLContextFactory JAVA CLASS                          30151      22478      29961       4
/88d7b011_TexturePaintContextI JAVA CLASS                          37642      29965      22474       0
java/awt/TexturePaintContext   JAVA CLASS                          37643      29966      22473       1
java/awt/TexturePaint          JAVA CLASS                          37644      29967      22472       2
/1777c77d_OGLPaintsTexture     JAVA CLASS                          37645      29968      22471       3
sun/java2d/opengl/OGLPaints    JAVA CLASS                          37646      29969      22470       4
/59fd3565_SerProfileToClassErr JAVA CLASS                          45135      37457      14982       0
/48429d3_SerProfileToClassErro JAVA CLASS                          45136      37458      14981       1
/3dbc884b_SerProfileToClassErr JAVA CLASS                          45137      37459      14980       2
sqlj/mesg/SyntaxErrorsText     JAVA CLASS                          45138      37460      14979       3
sqlj/mesg/SyntaxErrorsText_ar  JAVA CLASS                          45139      37461      14978       4
WRI$_OPTSTAT_SYNOPSIS$         TABLE SUBPARTITION                 555078      44948       7491       0
WRI$_OPTSTAT_SYNOPSIS$         TABLE SUBPARTITION                 555079      44949       7490       1
WRI$_OPTSTAT_SYNOPSIS$         TABLE SUBPARTITION                 555080      44950       7489       2
WRI$_OPTSTAT_SYNOPSIS$         TABLE SUBPARTITION                 555081      44951       7488       3
WRI$_OPTSTAT_SYNOPSIS$         TABLE SUBPARTITION                 555082      44952       7487       4
WRH$_CELL_OPEN_ALERTS_PK       INDEX PARTITION                   1628511      52435          4       7487
WRH$_CELL_OPEN_ALERTS_PK       INDEX PARTITION                   1628512      52436          3       7488
WRH$_IM_SEG_STAT               TABLE PARTITION                   1628514      52437          2       7489
WRH$_IM_SEG_STAT_PK            INDEX PARTITION                   1628515      52438          1       7490
WRH$_IM_SEG_STAT_PK            INDEX PARTITION                   1628516      52439          0       0

40 rows selected.


Lets you look around a bit. (Notice that there are 8 sets of 5 records. Seven "first" records, and then the final 5 of the results).

Tabibitosan : The Traveler likes to skip around

Duke Ganote, August 25, 2016 - 4:57 pm UTC

If we add in Oracle ACE Aketi Jyuuzou's "Tabibitosan" technique for grouping (which should be in everyone's repertoire), then the GRP# make the stratifications visible:

column grp# format 999,999
column r# format 999,999
column rr# format 999,999
column mod# format 999,999
SELECT object_name, object_type, object_id
     , DENSE_RANK() OVER (ORDER BY tabibito#) GRP#
     , r#, rr#, mod#
  FROM (
select object_name, object_type, object_id, r#, rr#, mod#
     , r# - row_number() OVER (order by r#) AS tabibito#
    from (
  select o.*
       , row_number()over(order by object_id)-1 AS r#
       , TRUNC(MOD(row_number()over(order by object_id)
                  ,count(*)over()
                   / 7 -- divide into seven "chunks"
                  )
              ) AS mod#
       , row_number()over(order by object_id DESC)-1 AS rr#
    from all_objects o
   where owner like 'SYS%'
  )
   where rr# < 5 or r# < 5 or mod# < 5
)
   order by r#;


OBJECT_NAME                    OBJECT_TYPE                     OBJECT_ID     GRP#       R#      RR#  MOD#
------------------------------ ------------------------------ ---------- -------- -------- -------- --------
C_OBJ#                         CLUSTER                                 2        1        0   52,439     1
I_OBJ#                         INDEX                                   3        1        1   52,438     2
TAB$                           TABLE                                   4        1        2   52,437     3
CLU$                           TABLE                                   5        1        3   52,436     4
C_TS#                          CLUSTER                                 6        1        4   52,435     5
XS_PRINCIPAL_INT               PACKAGE                             11352        2    7,491   44,948     0
XS_PRINCIPAL_INT               PACKAGE BODY                        11353        2    7,492   44,947     1
XS_PRINCIPAL                   PACKAGE BODY                        11354        2    7,493   44,946     2
DS_VARRAY_4_CLOB               TYPE                                11358        2    7,494   44,945     3
COLDICTREC                     TYPE                                11360        2    7,495   44,944     4
sqlj/translator/ClassPathEntry JAVA CLASS                          22653        3   14,982   37,457     0
sqlj/translator/ClassPath      JAVA CLASS                          22654        3   14,983   37,456     1
sqlj/translator/ClassFile      JAVA CLASS                          22655        3   14,984   37,455     2
/91e1f59f_ClassResolverImpl    JAVA CLASS                          22656        3   14,985   37,454     3
sqlj/translator/Main           JAVA CLASS                          22657        3   14,986   37,453     4
/d6c9df50_ServiceLocator       JAVA CLASS                          30147        4   22,474   29,965     0
/6612359c_GenericURLContext    JAVA CLASS                          30148        4   22,475   29,964     1
/f27212bf_GenericURLDirContext JAVA CLASS                          30149        4   22,476   29,963     2
/b8950380_ldapURLContext       JAVA CLASS                          30150        4   22,477   29,962     3
/2342c2e_ldapURLContextFactory JAVA CLASS                          30151        4   22,478   29,961     4
/88d7b011_TexturePaintContextI JAVA CLASS                          37642        5   29,965   22,474     0
java/awt/TexturePaintContext   JAVA CLASS                          37643        5   29,966   22,473     1
java/awt/TexturePaint          JAVA CLASS                          37644        5   29,967   22,472     2
/1777c77d_OGLPaintsTexture     JAVA CLASS                          37645        5   29,968   22,471     3
sun/java2d/opengl/OGLPaints    JAVA CLASS                          37646        5   29,969   22,470     4
/59fd3565_SerProfileToClassErr JAVA CLASS                          45135        6   37,457   14,982     0
/48429d3_SerProfileToClassErro JAVA CLASS                          45136        6   37,458   14,981     1
/3dbc884b_SerProfileToClassErr JAVA CLASS                          45137        6   37,459   14,980     2
sqlj/mesg/SyntaxErrorsText     JAVA CLASS                          45138        6   37,460   14,979     3
sqlj/mesg/SyntaxErrorsText_ar  JAVA CLASS                          45139        6   37,461   14,978     4
WRI$_OPTSTAT_SYNOPSIS$         TABLE SUBPARTITION                 555078        7   44,948    7,491     0
WRI$_OPTSTAT_SYNOPSIS$         TABLE SUBPARTITION                 555079        7   44,949    7,490     1
WRI$_OPTSTAT_SYNOPSIS$         TABLE SUBPARTITION                 555080        7   44,950    7,489     2
WRI$_OPTSTAT_SYNOPSIS$         TABLE SUBPARTITION                 555081        7   44,951    7,488     3
WRI$_OPTSTAT_SYNOPSIS$         TABLE SUBPARTITION                 555082        7   44,952    7,487     4
WRH$_CELL_OPEN_ALERTS_PK       INDEX PARTITION                   1628511        8   52,435        4    7,487
WRH$_CELL_OPEN_ALERTS_PK       INDEX PARTITION                   1628512        8   52,436        3    7,488
WRH$_IM_SEG_STAT               TABLE PARTITION                   1628514        8   52,437        2    7,489
WRH$_IM_SEG_STAT_PK            INDEX PARTITION                   1628515        8   52,438        1    7,490
WRH$_IM_SEG_STAT_PK            INDEX PARTITION                   1628516        8   52,439        0     0

40 rows selected.


FYI: "Tabibito" is Japanese for "traveler"; "san" is an honorific.

SQL developer is a terrible tool

A reader, August 26, 2016 - 9:32 am UTC

SQL developer is a terrible tool
It's nowhere even close compared to toad.

Just try to get current user creation script, not possible easily even with dba module.

How can the product team be so dumb and unaware.

Please learn from toad and try to incorporate its features.
Connor McDonald
August 26, 2016 - 1:44 pm UTC

I dont mind if you want to bleat about your gripes with SQL Developer.

Because I know also there's 4,000,000+ downloads of SQL Developer, and the vast majority of people that have done so, have positive things to say about it ... one of those things being its price compared with TOAD.

Of course, you could always adopt a more sensible stance and *liaise* with the product management for SQL Developer, and provide *objective* feedback, and work *with* them to get desired improvements to the product.

But hey.... do whatever floats your boat. If bile and invective are your thing...fine.

Even with rants, I know the SQL Dev product team will keep working to make a great product greater, because that's the kind of team they are.

SQL*Developer is good tool

Duke Ganote, August 26, 2016 - 3:56 pm UTC

I switched to a TOAD-less existence over 2 years ago as an Oracle developer / data designer:
http://it.toolbox.com/blogs/data-ruminations/drinking-your-own-champagne-skip-the-toad-juice-59408

I haven't looked back -- except when someone asks. Overall, SQL*Developer isn't as slick as TOAD, but each release gets better. I'ven't felt any need to go back to the TOAD juice.

Usually a google search of ThatJeffSmith.com answers any questions I have about SQL*Developer.
Chris Saxon
August 27, 2016 - 2:11 am UTC

Exactly. I've switch numerous clients from TOAD to SQL Developer...functionality is perfectly fine for SQL/PLSQL development (in my view better) and...cost...well....night and day

DB-DOC on other schema's

Rajeshwaran, Jeyabal, January 18, 2018 - 5:46 am UTC

Team,

This morning was reading this blog post from Jeffsmith, Excellent !

https://www.thatjeffsmith.com/archive/2012/03/javadoc-for-the-database-a-la-dbdoc-via-sql-developer/

Is that still possible to generated DBDOC for other schema that are not listed in connections panel?

Say, we have a Database that supports 10 to 15 applications, spread across different schema. we have a DBA account, which we can switch into different schema and do manipulations, but we don't know the password for individual schema.

Is it still possible from SQL Developer (version 17.4) to generate DB DOC for all the schema from a single DBA account?
Connor McDonald
January 22, 2018 - 1:58 am UTC

Not to my knowledge.

You could perhaps use the Database Export facility to export DDL using the "one file per object", and then iterate through that with the command line facility.

DB-DOC on other schema's

Rajeshwaran, Jeyabal, January 22, 2018 - 5:23 am UTC

Team,

You could perhaps use the Database Export facility to export DDL using the "one file per object, and then iterate through that with the command line facility.


Able to understand this - "You could perhaps use the Database Export facility to export DDL using the "one file per object"


"and then iterate through that with the command line facility" - what does that mean? could you please elaborate ?
Connor McDonald
January 23, 2018 - 1:55 am UTC

On the pldoc site

http://pldoc.sourceforge.net/maven-site/

You can run the command line utility for multiple files , eg

pldoc -d sampleOutput -doctitle TheBigApp -overview overview.html sample*.sql


So use SQL Developer Tools => Database Export to export your DDL for all schemas desired, and then use the command line utility against that.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.