How to Skip N Rows in an Oracle SELECT Statement

I saw this question on a SQL Server blog. Apparently it was asked at an interview. Here is the suggested answer:

SELECT *

FROM [AdventureWorks2014].[Person].[Address]

ORDER BY AddressID

OFFSET 100 ROWS;

 

I don’t have access to SQL Server right now so I wondered how I might do this in Oracle. This is what I came up with. I ran it in an Oracle 11.2.0.4 database:

SQL> l

  1   select username from dba_users

  2   minus

  3   (select username from

  4    (select username from dba_users order by username)

  5    where rownum <=10)

  6* order by username

SQL> /

 

USERNAME

------------------------------

FRED

MDDATA

MDSYS

MGMT_VIEW

OLAPSYS

ORACLE

ORACLE_OCM

ORDDATA

ORDPLUGINS

ORDSYS

OUTLN

OWBSYS

OWBSYS_AUDIT

SI_INFORMTN_SCHEMA

SPATIAL_CSW_ADMIN_USR

SPATIAL_WFS_ADMIN_USR

SYS

SYSMAN

SYSTEM

WMSYS

XDB

XS$NULL

 

22 rows selected.

 

SQL>

 

It skips the first 10 rows from DBA_USERS and returns the rest. Here are all the rows, in case you are interested:

 

SQL> select username from dba_users order by 1

  2  /

 

USERNAME

------------------------------

ABCD

ANONYMOUS

APEX_030200

APEX_PUBLIC_USER

APPQOSSYS

CTXSYS

DBSNMP

DIP

EXFSYS

FLOWS_FILES

FRED

MDDATA

MDSYS

MGMT_VIEW

OLAPSYS

ORACLE

ORACLE_OCM

ORDDATA

ORDPLUGINS

ORDSYS

OUTLN

OWBSYS

OWBSYS_AUDIT

SI_INFORMTN_SCHEMA

SPATIAL_CSW_ADMIN_USR

SPATIAL_WFS_ADMIN_USR

SYS

SYSMAN

SYSTEM

WMSYS

XDB

XS$NULL

 

32 rows selected.

 

SQL>

SQL92_SECURITY

I tested the examples in this post in two Oracle 11.2 databases. In the first example, Andrew creates a table, inserts a row into it then grants UPDATE privilege on it to Fred, who is then able to work out the value in the table, without changing it permanently, using a series of UPDATE and ROLLBACK statements: 

SQL> conn / as sysdba

Connected.

SQL> select value from v$parameter

  2  where name = 'sql92_security'

  3  /

 

VALUE

------------------------------

FALSE

 

SQL> create user andrew

  2  identified by reid

  3  default tablespace users

  4  quota unlimited on users

  5  /

 

User created.

 

SQL> grant create session, create table

  2  to andrew

  3  /

 

Grant succeeded.

 

SQL> create user fred

  2  identified by bloggs

  3  /

 

User created.

 

SQL> grant create session to fred

  2  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> create table tab1 (col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values (5)

  2  /

 

1 row created.

 

SQL> grant update on tab1 to fred

  2  /

 

Grant succeeded.

 

SQL> conn fred/bloggs

Connected.

SQL> update andrew.tab1

  2  set col1 = 99

  3  where col1 > 10

  4  /

 

0 rows updated.

 

SQL> update andrew.tab1

  2  set col1 = 99

  3  where col1 > 0

  4  /

 

1 row updated.

 

SQL> rollback

  2  /

 

Rollback complete.

 

SQL> update andrew.tab1

  2  set col1 = 99

  3  where col1 > 5

  4  /

 

0 rows updated.

 

SQL> update andrew.tab1

  2  set col1 = 99

  3  where col1 > 3

  4  /

 

1 row updated.

 

SQL> rollback

  2  /

 

Rollback complete.

 

SQL> update andrew.tab1

  2  set col1 = 99

  3  where col1 > 4

  4  /

 

1 row updated.

 

SQL> rollback

  2  /

 

Rollback complete.

 

SQL> update andrew.tab1

  2  set col1 = 99

  3  where col1 = 5

  4  /

 

1 row updated.

 

SQL> rollback

  2  /

 

Rollback complete.

 

SQL> 

The example is trivial. Working out floating pointing numbers or long character strings in tables containing thousands of rows would take ages using this method. However, by using some clever PL/SQL, it would be possible, given time. This isn’t too much of a problem for me. After all, if a malicious person had UPDATE access to a table of mine, I’m sure he could cause far more damage by simply overwriting my data. But if you don’t like the idea of people guessing values in your tables, then the SQL92_SECURITY initialization parameter could be for you. In the example above, you may have noticed that it was turned off.

 

However, in the second example below, the parameter is turned on. It was run in a different database, as mentioned above, but the users were created in a similar fashion. This time, the UPDATE privilege does not allow Fred to run UPDATE statements with WHERE clauses. To do this, he needs SELECT access as well: 

SQL> conn / as sysdba

Connected.

SQL> select value from v$parameter

  2  where name = 'sql92_security'

  3  /

 

VALUE

------------------------------

TRUE

 

SQL> conn andrew/reid

Connected.

SQL> create table tab1 (col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values (1)

  2  /

 

1 row created.

 

SQL> grant update on tab1 to fred

  2  /

 

Grant succeeded.

 

SQL> conn fred/bloggs

Connected.

SQL> update andrew.tab1 set col1 = 2

  2  /

 

1 row updated.

 

SQL> update andrew.tab1 set col1 = 3

  2  where col1 = 2

  3  /

update andrew.tab1 set col1 = 3

              *

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL> conn andrew/reid

Connected.

SQL> grant select on tab1 to fred

  2  /

 

Grant succeeded.

 

SQL> conn fred/bloggs

Connected.

SQL> update andrew.tab1 set col1 = 3

  2  where col1 = 2

  3  /

 

1 row updated.

 

SQL>

How to Add Conditions After a Where Clause

I went on an Oracle 9i DBA Performance and Tuning course in 2005 and was looking through the course notes recently to find something to blog about. They suggested that if you had conditions after a WHERE clause joined by AND, you should put the test which was most likely to fail first. This would then save Oracle the bother of evaluating the subsequent condition(s). This seemed reasonable so I decided to try it out.

I copied the contents of DBA_TABLES into a table of my own called T1 and duplicated its contents repeatedly until it had over three million rows. Then I checked that every row had TABLE_LOCK set to ENABLED and that no rows had an owner called BLAH.

SQL> create table t1 as select * from dba_tables

  2  /

 

Table created.

 

SQL> begin

  2  for a in 1..11 loop

  3  insert into t1 select * from t1;

  4  end loop;

  5  end;

  6  /

 

PL/SQL procedure successfully completed.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL> select count(*) from t1

  2  /

 

  COUNT(*)

----------

   3217408

 

SQL> select count(*) from t1 where table_lock = 'ENABLED'

  2  /

 

  COUNT(*)

----------

   3217408

 

SQL> select count(*) from t1 where owner = 'BLAH'

  2  /

 

  COUNT(*)

----------

         0

 

SQL>

Then I ran the first SELECT statement as follows. If the course notes were correct, the first condition should reject every row and the second condition should never be evaluated:

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL> conn /

Connected.

SQL> select count(*) from t1

  2  where owner = 'BLAH'

  3  and table_lock = 'ENABLED'

  4  /

 

  COUNT(*)

----------

         0

 

SQL> select a.value/100 "CPU Used"

  2  from v$mystat a, v$sysstat b

  3  where a.statistic# = b.statistic#

  4  and name = 'CPU used by this session'

  5  /

 

  CPU Used

----------

     13.83

 

SQL>

Then I ran the second SELECT statement shown below. It was identical to the first but the conditions were swapped round. If the course notes were correct, the first condition should accept each row, forcing Oracle to evaluate the second condition every time. Notice how the CPU Used figure increased. I repeated this test five times and got similar results each time. So far so good:

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL> conn /

Connected.

SQL> select count(*) from t1

  2  where table_lock = 'ENABLED'

  3  and owner = 'BLAH'

  4  /

 

  COUNT(*)

----------

         0

 

SQL> select a.value/100 "CPU Used"

  2  from v$mystat a, v$sysstat b

  3  where a.statistic# = b.statistic#

  4  and name = 'CPU used by this session'

  5  /

 

  CPU Used

----------

     14.27

 

SQL>

The course notes also suggested that if you had conditions after a WHERE clause joined by OR, you should put the test which was most likely to succeed first. This would then save Oracle the bother of evaluating the subsequent condition(s). I decided to try this out too, using the table T1, which I created above.

I ran the third SELECT statement like this. If the course notes were correct, the first condition should accept every row and the second condition should never be evaluated: 

SQL> alter system flush shared_pool

  2  / 

System altered.

 

SQL> conn /

Connected.

SQL> select count(*) from t1

  2  where table_lock = 'ENABLED'

  3  or owner = 'BLAH'

  4  /

  COUNT(*)

----------

   3217408

SQL> select a.value/100 "CPU Used"

  2  from v$mystat a, v$sysstat b

  3  where a.statistic# = b.statistic#

  4  and name = 'CPU used by this session'

  5  / 

  CPU Used

----------

     14.17 

SQL>

Finally, I ran the fourth SELECT statement. It was identical to the third but the conditions were swapped round. If the course notes were correct, the first condition should reject each row, forcing Oracle to evaluate the second condition every time. Notice how the CPU Used figure increased. I repeated this test five times as well and got similar results each time. I think this demonstrated that the course notes were correct:

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL> conn /

Connected.

SQL> select count(*) from t1

  2  where owner = 'BLAH'

  3  or table_lock = 'ENABLED'

  4  /

 

  COUNT(*)

----------

   3217408

 

SQL> select a.value/100 "CPU Used"

  2  from v$mystat a, v$sysstat b

  3  where a.statistic# = b.statistic#

  4  and name = 'CPU used by this session'

  5  /

 

  CPU Used

----------

     14.79

 

SQL>

ORA-00934

This was tested on Oracle 11.1.0.6.0 running on Windows XP. You can only use a WHERE clause to select or reject individual rows. You cannot use it on a group function:

SQL> l
  1  select owner, round(avg(bytes))
  2  from dba_segments
  3  where avg(bytes) > 60000
  4  group by owner
  5* order by owner
SQL> /
where avg(bytes) > 60000
      *
ERROR at line 3:
ORA-00934: group function is not allowed here

SQL>

You must use HAVING instead:

SQL> l
  1  select owner, round(avg(bytes))
  2  from dba_segments
  3  group by owner
  4  having avg(bytes) > 60000
  5* order by owner
SQL> /

OWNER                ROUND(AVG(BYTES))
-------------------- -----------------
CTXSYS                           66298
DBSNMP                           65536
EXFSYS                           65536
FLOWS_030000                    123339
FLOWS_FILES                      65536
HR                               65536
IX                               65536
MDSYS                           139147
OE                              124955
OLAPSYS                          65536
ORDSYS                           80766
OUTLN                            65536
PM                              311296
SCOTT                            65536
SH                              204600
SYS                             623750
SYSMAN                          107652
SYSTEM                           65536
TSMSYS                           65536
WKSYS                            65536
WK_TEST                         244065
WMSYS                            65536
XDB                             124663

23 rows selected.

SQL>

SQL*Plus word_wrapped Command

This was tested on Oracle 11.1.0.6.0 running on Windows XP. I SELECTed a piece of text from DUAL and it all came out on the same line:

SQL> select 'To be or not to be that is the question'
  2  Shakespeare from dual
  3  /

SHAKESPEARE
---------------------------------------
To be or not to be that is the question

SQL>

Then I made the linesize much shorter and the text was split over 2 lines:

SQL> set lines 20
SQL> l
  1  select 'To be or not to be that is the question'
  2* Shakespeare from dual
SQL> /

SHAKESPEARE
--------------------
To be or not to be t
hat is the question

SQL>

It did not look very good as one of the words was split too. To stop this happening, I used the SQL*Plus word_wrapped feature, which splits lines on word boundaries:

SQL> col Shakespeare word_wrapped
SQL> l
  1  select 'To be or not to be that is the question'
  2* Shakespeare from dual
SQL> /

SHAKESPEARE
--------------------
To be or not to be
that is the question

SQL>

ORA-01789 and ORA-01790

This was tested on Oracle 11.1.0.6.0 running on Windows XP. You can merge the results from two or more SELECT statements with UNION:

SQL> l
  1  select owner, table_name
  2  from dba_tables
  3  union
  4  select owner, index_name
  5* from dba_indexes
SQL> /

OWNER                     TABLE_NAME
------------------------- -------------------------
CTXSYS                    DR$ACTIVELOGS
CTXSYS                    DR$CLASS
CTXSYS                    DR$DBO
CTXSYS                    DR$DELETE
CTXSYS                    DR$FEATURE_USED
CTXSYS                    DR$FEAT_KEY
CTXSYS                    DR$FREQTOKS
CTXSYS                    DR$INDEX
CTXSYS                    DR$INDEX_CDI_COLUMN
CTXSYS                    DR$INDEX_ERROR
CTXSYS                    DR$INDEX_OBJECT
CTXSYS                    DR$INDEX_PARTITION
etc.

Each SELECT statements must return the same number of columns. If not, you get an ORA-01789:

SQL> l
  1  select owner, table_name, last_analyzed
  2  from dba_tables
  3  union
  4  select owner, index_name
  5* from dba_indexes
SQL> /
select owner, table_name, last_analyzed
*
ERROR at line 1:
ORA-01789: query block has incorrect number of result
columns

SQL>

The column definitions from each SELECT must match the corresponding column definitions from the other SELECTs. Otherwise, you get an ORA-01790:

SQL> l
  1  select table_name, last_analyzed
  2  from dba_tables
  3  union
  4  select index_name, index_type
  5* from dba_indexes
SQL> /
select table_name, last_analyzed
                   *
ERROR at line 1:
ORA-01790: expression must have same datatype as
corresponding expression

You get the same error messages in these situations with UNION ALL, INTERSECT and MINUS.