Oracle SQL

Book advice: ORACLE SQL & PL/SQL Golden Diary by Asim Chowdhury

Posted on by Posted in oracle 1 Comment

I’ve reviewed this book recently, and I highly recommend it as it has almost all that needed to become strong Oracle developer. You can check at least the table of contents:

ORACLE SQL & PL/SQL Golden Diary: by Asim Chowdhury
New Book Demystifies Complex Cross-Version Oracle Problem Solving

Compiled by veteran computer scientist and data modeler, Asim Chowdhury, ‘ORACLE SQL & PL/SQL Golden Diary: Refactoring, Interoperability of Versions & Integration of related concepts for High Performance’ is the first book on the market that comprehensively allows data architects to unravel any concepts in SQL and PL/sql till oracle 12c. It’s poised to remove much confusion from the many versions of Oracle SQL now on the market; a Godsend for the computer science industry.

Intra-block row chaining optimization in 12.2

Posted on by Posted in 12c, oracle, trace, undocumented 3 Comments

I’ve wrote in previous post

4. Next row piece can be stored in the same block only with inserts. When you run update, oracle will place new row piece into another block.

But it’s not valid anymore 🙂 Since 12.2 Oracle optimizes updates too.
You can check it on 12.2 and previous version using example 4 from previous post:

Test 4 

drop table test purge;
set serverout on
alter session set tracefile_identifier='test4';
 
declare
   cols varchar2(32000):='c_1 char(3)';
    
   procedure print_and_exec(c varchar2) as
   begin
      dbms_output.put_line(c);
      execute immediate c;
   end;
begin
   for i in 2..355 loop
      cols:=cols||',c_'||i||' char(3)';
   end loop;
   print_and_exec ('create table test('||cols||')');
   print_and_exec ('insert into test(c_1) values(null)');
   commit;
   for i in 256..355 loop
      execute immediate 'update test set c_'||i||'='||i;
   end loop;
   commit;
   execute immediate 'alter system flush buffer_cache';
   for r in (select 'alter system dump datafile '||file#||' block '||block# cmd 
             from (
                   select distinct file#,block# 
                   from v$bh 
                   where class#=1 
                   and objd in (select o.data_object_id from user_objects o where object_name='TEST') 
                   order by 1,2
                  )
            )
   loop 
       execute immediate r.cmd;
   end loop;
end;
/
disc;

[collapse]

On 12.2 you will see that it creates just 5 blocks 🙂

PS. My presentation about Intra-block row chaining from RuOUG seminar: Intra-block row chaining(RuOUG)

SQL validation during PL/SQL compilation

Posted on by Posted in oracle, PL/SQL, SQL Leave a comment

A recent posting on SQL.RU asked why Oracle doesn’t raise such errors like “ORA-00979 not a group by expression” during PL/SQL compilation. Since I couldn’t find a link to the answer (though I read about it many years ago, but I don’t remember where…), I’ve decided to post short answer:

During PL/SQL compilation Oracle checks static SQL using only:

  1. Syntactic analysis – Oracle verifies that keywords, object names, operators, delimiters, and so on are placed correctly in your SQL statement. So such queries like “select * foRm dual” will fail during this validation. For example, we can get here such errors like:
    ORA-00900: invalid SQL statement
    ORA-00923: FROM keyword not found where expected
    ORA-00924: missing BY keyword
    ORA-00933: SQL command not properly ended
    …
  2. Semantic analysis – it verifies that references to host variables and database objects are valid(including their grants) and that host-variable datatypes are correct. For example, “select * from nonexisting_table” will fail this validation.

And since Oracle doesn’t validate all other types of errors during Syntactic and Semantic analysis, we can detect them only during optimization* or execution*.
For example, Oracle detects “ORA-00979 not a group by expression” during optimization phase.

* Read “Chapter Chapter 7: Parsing and Optimizing” in Oracle Core by Jonathan Lewis

NB. It doesn’t not apply to CREATE or ALTER VIEW, because Oracle executes optimization step for them. You can check it using trace 10053.

Simple example:

-- fORm instead of "from" - syntactic validation fails:
SQL> create table t(a int, b int, c int);
SQL> create or replace procedure p_syntactic is
  2     cursor c is select a,b,sum(c) sum_c fORm t group by a;
  3  begin
  4     null;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> sho error;
Errors for PROCEDURE P_SYNTACTIC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/16     PL/SQL: SQL Statement ignored
2/40     PL/SQL: ORA-00923: FROM keyword not found where expected

-- semantic validation fails:
SQL> create or replace procedure p_semantic is
  2     cursor c is select a,b,sum(blabla) sum_c from t group by a;
  3  begin
  4     null;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> sho error;
Errors for PROCEDURE P_SEMANTIC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/16     PL/SQL: SQL Statement ignored
2/31     PL/SQL: ORA-00904: "BLABLA": invalid identifier

-- As you can see this procedure passes successfully both syntactic and semantic analysis,
-- though query is not valid: it should raise "ORA-00979: not a GROUP BY expression"
SQL> create or replace procedure p_valid is
  2     cursor c is select a,b,sum(c) sum_c from t group by a;
  3  begin
  4     null;
  5  end;
  6  /

SQL> sho error;
No errors.

-- Oracle checks such errors for "CREATE VIEW", because it runs optimization for the query text:
SQL> create view v_cursor as select a,b,sum(c) sum_c from t group by a order by a;
create view v_cursor as select a,b,sum(c) sum_c from t group by a order by a
                                 *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

How to speed up slow unicode migration of a table with xmltype columns

Posted on by Posted in oracle, parallel, query optimizing, troubleshooting Leave a comment

Recently I have had an issue with slow unicode migration of the database upgraded from 10g to 12.1.0.2. The main problem was a table with xmltype: we spent about 4 hours for this table(~17GB) during test migration, though all other tables (~190GB) migrated just for about 20 minutes.
We used DMU(Database Migration Assistant for Unicode), and the root cause of the problem was update statement generated by DMU for this table:

update  /*+ PARALLEL(A,16)*/ "RRR"."T_XMLDATA" A  set A."SYS_NC00011$" = SYS_OP_CSCONV(A."SYS_NC00011$", 'AL16UTF16')

“SYS_NC00011$” was internal hidden CLOB column used to store XMLTYPE. As you can see DMU added PARALLEL hint, but though oracle can use parallel dml for xmltype since 12.1.0.1, we can’t use it because of its’ limitations:

Changes in Oracle Database 12c Release 1 (12.1.0.1) for Oracle XML DB

Parallel DML Support for XMLType
Support for parallel DML has been improved for XMLType storage model binary XML using SecureFiles LOBs. The performance and scalability have been improved for both CREATE TABLE AS SELECT and INSERT AS SELECT.

Restrictions on Parallel DML

Parallel DML can be done on tables with LOB columns provided the table is partitioned. However, intra-partition parallelism is not supported.

For non-partitioned tables with LOB columns, parallel INSERT operations are supported provided that the LOB columns are declared as SecureFiles LOBs. Parallel UPDATE, DELETE, and MERGE operations on such tables are not supported.

Btw, Oracle didn’t support parallel dml for xmltype on previous releases:

No Parallel DML for XMLType – DML operations on XMLType data are always performed in serial. Parallel DML is not supported for XMLType. (Parallel query and DDL are supported for XMLType.)

So I had to use manual parallelization:
1. Monitor “Convert application tables” step through “View Table Conversion progress” and press “Stop” button during conversion of this table.
2. Create table with ROWIDs of this table and split them into 16 groups:

create table tmp_rids as 
select rowid rid, ntile(16)over(order by rowid) grp 
from t_xmldata;

3. Execute 

ALTER SYSTEM SET EVENTS '22838 TRACE NAME CONTEXT LEVEL 1,FOREVER';

to avoid “ORA-22839: Direct updates on SYS_NC columns are disallowed”
4. Start 16 sessions and each of them have to update own part:

update t_xmldata A 
set A."SYS_NC00011$" = SYS_OP_CSCONV(A."SYS_NC00011$", 'AL16UTF16') 
where rowid in (select rid from tmp_rids where grp=&grp);
commit;

5. Disable event 22838:

ALTER SYSTEM SET EVENTS '22838 TRACE NAME CONTEXT OFF';

6. Open “View Table Conversion progress” window, click on this table and change “Retry” to “Skip” option for the update step.

This simple method allowed to make unicode migration about 16 times faster.

row pieces, 255 columns, intra-block row chaining in details

Posted on by Posted in oracle, trace, undocumented 10 Comments

You may know about Intra-block row chaining, that may occur when the number of columns in a table is more than 255 columns.
But do you know that intra-block chaining works with inserts only? not updates!

Documentation says:

When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is called intra-block chaining. A chained row’s pieces are chained together using the rowids of the pieces. With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.

A bit more details:
1. One row piece can store up to 255 columns
2. Oracle splits fields by row pieces in reverse order
3. Oracle doesn’t store trailing null fields in a row (not in row piece)
4. Next row piece can be stored in the same block only with inserts. When you run update, oracle will place new row piece into another block. (not valid since 12.2)

I’ll show in examples with dumps:
Continue reading→

How even empty trigger increases redo generation

Posted on by Posted in oracle 2 Comments

Very simple example:

Test case 

set feed on;
-- simple table:
create table xt_curr1 as select level a,level b from dual connect by level<=1e4;
-- same table but with empty trigger:
create table xt_curr2 as select level a,level b from dual connect by level<=1e4;
create or replace trigger tr_xt_curr2 before update on xt_curr2 for each row
begin
  null;
end;
/

set autot trace stat;
update xt_curr1 set b=a;
set autot off;

set autot trace stat;
update xt_curr2 set b=a;
set autot off;
set feed off

drop table xt_curr1 purge;
drop table xt_curr2 purge;

[collapse]

Update 2017/11/19: I have posted more detailed test cases with logfile dumps here.

SQL> -- simple table:
SQL> create table xt_curr1 as select level a,level b from dual connect by level<=1e4;

Table created.

SQL> -- same table but with empty trigger:
SQL> create table xt_curr2 as select level a,level b from dual connect by level<=1e4;

Table created.

SQL> create or replace trigger tr_xt_curr2 before update on xt_curr2 for each row
  2  begin
  3    null;
  4  end;
  5  /

Trigger created.

SQL> update xt_curr1 set b=a;

10000 rows updated.


Statistics
----------------------------------------------------------
         25  recursive calls
      10553  db block gets
         91  consistent gets
         18  physical reads
    3101992  redo size
        560  bytes sent via SQL*Net to client
        491  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> update xt_curr2 set b=a;

10000 rows updated.


Statistics
----------------------------------------------------------
         11  recursive calls
      20384  db block gets
         59  consistent gets
         18  physical reads
    4411724  redo size
        560  bytes sent via SQL*Net to client
        491  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10000  rows processed

8 queens chess problem: solution in Oracle SQL

Posted on by Posted in curious, oracle, SQL 1 Comment

This is just another solution of this problem for a chessboard, but you can choose any size of the checkerboard:

with 
 t as (select level i, cast(level as varchar2(1)) c from dual connect by level<=&d)
,x(l,s,n) as (
       select 1 l, c s, chr(97)||c||' ' from t
       union all
       select l+1, x.s||t.c, n||chr(97+l)||i||' '
       from x
            join t
                 on instr(s,c)=0
                    and not exists(select 0 from dual 
                                   where L+1 - t.i = level - substr(s,level,1)
                                      or L+1 + t.i = level + substr(s,level,1)
                                   connect by level<=length(s))
       where L<&d
 )
select n
from x
where l=&d
8x8: 
SQL> @tests/f
Size[8]: 8

N
--------------------------------------------------------------------------------
a1 b5 c8 d6 e3 f7 g2 h4
a1 b6 c8 d3 e7 f4 g2 h5
a1 b7 c4 d6 e8 f2 g5 h3
a1 b7 c5 d8 e2 f4 g6 h3
a2 b4 c6 d8 e3 f1 g7 h5
a2 b5 c7 d1 e3 f8 g6 h4
a2 b5 c7 d4 e1 f8 g6 h3
a2 b6 c1 d7 e4 f8 g3 h5
a2 b6 c8 d3 e1 f4 g7 h5
a2 b7 c3 d6 e8 f5 g1 h4
a2 b7 c5 d8 e1 f4 g6 h3
a2 b8 c6 d1 e3 f5 g7 h4
a3 b1 c7 d5 e8 f2 g4 h6
a3 b5 c2 d8 e1 f7 g4 h6
a3 b5 c2 d8 e6 f4 g7 h1
a3 b5 c7 d1 e4 f2 g8 h6
a3 b5 c8 d4 e1 f7 g2 h6
a3 b6 c2 d5 e8 f1 g7 h4
a3 b6 c2 d7 e1 f4 g8 h5
a3 b6 c2 d7 e5 f1 g8 h4
a3 b6 c4 d1 e8 f5 g7 h2
a3 b6 c4 d2 e8 f5 g7 h1
a3 b6 c8 d1 e4 f7 g5 h2
a3 b6 c8 d1 e5 f7 g2 h4
a3 b6 c8 d2 e4 f1 g7 h5
a3 b7 c2 d8 e5 f1 g4 h6
a3 b7 c2 d8 e6 f4 g1 h5
a3 b8 c4 d7 e1 f6 g2 h5
a4 b1 c5 d8 e2 f7 g3 h6
a4 b1 c5 d8 e6 f3 g7 h2
a4 b2 c5 d8 e6 f1 g3 h7
a4 b2 c7 d3 e6 f8 g1 h5
a4 b2 c7 d3 e6 f8 g5 h1
a4 b2 c7 d5 e1 f8 g6 h3
a4 b2 c8 d5 e7 f1 g3 h6
a4 b2 c8 d6 e1 f3 g5 h7
a4 b6 c1 d5 e2 f8 g3 h7
a4 b6 c8 d2 e7 f1 g3 h5
a4 b6 c8 d3 e1 f7 g5 h2
a4 b7 c1 d8 e5 f2 g6 h3
a4 b7 c3 d8 e2 f5 g1 h6
a4 b7 c5 d2 e6 f1 g3 h8
a4 b7 c5 d3 e1 f6 g8 h2
a4 b8 c1 d3 e6 f2 g7 h5
a4 b8 c1 d5 e7 f2 g6 h3
a4 b8 c5 d3 e1 f7 g2 h6
a5 b1 c4 d6 e8 f2 g7 h3
a5 b1 c8 d4 e2 f7 g3 h6
a5 b1 c8 d6 e3 f7 g2 h4
a5 b2 c4 d6 e8 f3 g1 h7
a5 b2 c4 d7 e3 f8 g6 h1
a5 b2 c6 d1 e7 f4 g8 h3
a5 b2 c8 d1 e4 f7 g3 h6
a5 b3 c1 d6 e8 f2 g4 h7
a5 b3 c1 d7 e2 f8 g6 h4
a5 b3 c8 d4 e7 f1 g6 h2
a5 b7 c1 d3 e8 f6 g4 h2
a5 b7 c1 d4 e2 f8 g6 h3
a5 b7 c2 d4 e8 f1 g3 h6
a5 b7 c2 d6 e3 f1 g4 h8
a5 b7 c2 d6 e3 f1 g8 h4
a5 b7 c4 d1 e3 f8 g6 h2
a5 b8 c4 d1 e3 f6 g2 h7
a5 b8 c4 d1 e7 f2 g6 h3
a6 b1 c5 d2 e8 f3 g7 h4
a6 b2 c7 d1 e3 f5 g8 h4
a6 b2 c7 d1 e4 f8 g5 h3
a6 b3 c1 d7 e5 f8 g2 h4
a6 b3 c1 d8 e4 f2 g7 h5
a6 b3 c1 d8 e5 f2 g4 h7
a6 b3 c5 d7 e1 f4 g2 h8
a6 b3 c5 d8 e1 f4 g2 h7
a6 b3 c7 d2 e4 f8 g1 h5
a6 b3 c7 d2 e8 f5 g1 h4
a6 b3 c7 d4 e1 f8 g2 h5
a6 b4 c1 d5 e8 f2 g7 h3
a6 b4 c2 d8 e5 f7 g1 h3
a6 b4 c7 d1 e3 f5 g2 h8
a6 b4 c7 d1 e8 f2 g5 h3
a6 b8 c2 d4 e1 f7 g5 h3
a7 b1 c3 d8 e6 f4 g2 h5
a7 b2 c4 d1 e8 f5 g3 h6
a7 b2 c6 d3 e1 f4 g8 h5
a7 b3 c1 d6 e8 f5 g2 h4
a7 b3 c8 d2 e5 f1 g6 h4
a7 b4 c2 d5 e8 f1 g3 h6
a7 b4 c2 d8 e6 f1 g3 h5
a7 b5 c3 d1 e6 f8 g2 h4
a8 b2 c4 d1 e7 f5 g3 h6
a8 b2 c5 d3 e1 f7 g4 h6
a8 b3 c1 d6 e2 f5 g7 h4
a8 b4 c1 d3 e6 f2 g7 h5

92 rows selected.

[collapse]

Solution for N between 10 and 100 

with
 t as (select level i, to_char(level,'fm00') c from dual connect by level<=&d)
,x(l,s,n) as (
       select 1 l, c s, chr(97)||c||' ' from t
       union all
       select l+1, x.s||t.c, n||chr(97+l)||to_char(i,'fm00')||' '
       from x
            join t
                 on instr(s,c)=0
                    and not exists(select 0 from dual 
                                   where L+1 - t.i = level - substr(s,length(c)*level-1,length(c))
                                      or L+1 + t.i = level + substr(s,length(c)*level-1,length(c))
                                   connect by level<=length(s))
       where L<&d
 )
select n
from x
where l=&d

[collapse]

It works quite fast:
8*8 ~ 0.1s
9*9 ~ 0.6s
10*10 ~4s

script for sqlplus 

set arrays 1000;
col n for a80;
accept d prompt "Size[8]: " default 8;
with 
 t as (select/*+inline*/ level i, cast(level as varchar2(2)) c from dual connect by level<=&d)
,x(l,s,n) as (
       select 1 l, c s, chr(97)||c||' ' from t
       union all
       select l+1, x.s||t.c, n||chr(97+l)||i||' '
       from x
            join t
                 on instr(s,c)=0
                    and not exists(select 0 from dual 
                                   where L+1 - t.i = level - substr(s,level,1)
                                      or L+1 + t.i = level + substr(s,level,1)
                                   connect by level<=length(s))
       where L<&d
 )
select n
from x
where l=&d
/
col n clear;

[collapse]

Update: Fixed the typo, thanks to Brian Fitzgerald (@ExaGridDba)

Maven: how to copy files after a build into several distribution directories

Posted on by Posted in java Leave a comment

Sometimes it is convenient to copy jar-files automatically after a build into several different directories, for example if you have different config files for local tests and for office test server, then you may want to copy these files into local test directory, internal office test server and public distribution directory.
This short part of pom.xml contains 2 different methods to build UBER-JAR and copying them into 3 different directories: localtest, officetest and public

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-assembly-plugin</artifactId>
                <version>2.6</version>
                <configuration>
                    <archive>
                        <manifest>
                            <mainClass>tv.tmd.YourMainClass</mainClass>
                        </manifest>
                        <manifestEntries>
                            <Class-Path>.</Class-Path>
                        </manifestEntries>
                    </archive>
                    <descriptorRefs>
                        <descriptorRef>jar-with-dependencies</descriptorRef>
                    </descriptorRefs>
                </configuration>
                <executions>
                    <execution>
                        <id>make-assembly</id>
                        <phase>package</phase>
                        <goals>
                            <goal>single</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>

            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-shade-plugin</artifactId>
                <version>2.4.3</version>
                <executions>
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>shade</goal>
                        </goals>
                        <configuration>
                            <transformers>
                                <transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
                                    <manifestEntries>
                                        <Main-Class>tv.tmd.YourMainClass</Main-Class>
                                        <Build-Number>2</Build-Number>
                                    </manifestEntries>
                                </transformer>
                            </transformers>
                        </configuration>
                    </execution>
                </executions>
            </plugin>

            <plugin>
                <artifactId>maven-antrun-plugin</artifactId>
                <version>1.8</version>
                <executions>
                    <execution>
                        <id>copy</id>
                        <phase>package</phase>
                        <configuration>
                            <target>
                                <echo>ANT TASK - copying files....</echo>
                                <copy todir="${basedir}/distribution/localtest" overwrite="true" flatten="true">
                                    <fileset dir="${basedir}" includes="*.bat" >
                                        <include name="*.bat" />
                                        <include name="ReadME.*" />
                                        <include name="target/*.jar" />
                                    </fileset>
                                </copy>

                                <copy todir="${basedir}/distribution/officetest" overwrite="true" flatten="true">
                                    <fileset dir="${basedir}" includes="*.bat" >
                                        <include name="*.bat" />
                                        <include name="ReadME.*" />
                                        <include name="target/*.jar" />
                                    </fileset>
                                </copy>

                                <copy todir="${basedir}/distribution/public" overwrite="true" flatten="true">
                                    <fileset dir="${basedir}" includes="*.bat" >
                                        <include name="*.bat" />
                                        <include name="ReadME.*" />
                                        <include name="target/*.jar" />
                                    </fileset>
                                </copy>
                            </target>
                        </configuration>
                        <goals>
                            <goal>run</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

WINDOW NOSORT STOPKEY + RANK()

Posted on by Posted in CBO, oracle, query optimizing, SQL Leave a comment

Recently I found that WINDOW NOSORT STOPKEY with RANK()OVER() works very inefficiently: http://www.freelists.org/post/oracle-l/RANKWINDOW-NOSORT-STOPKEY-stopkey-doesnt-work
The root cause of this behaviour is that Oracle optimizes WINDOW NOSORT STOPKEY with RANK the same way as with DENSE_RANK:

rnk1 

create table test(n not null) as 
  with gen as (select level n from dual connect by level<=100)
  select g2.n as n
  from gen g1, gen g2
  where g1.n<=10
/
create index ix_test on test(n)
/
exec dbms_stats.gather_table_stats('','TEST');
select/*+ gather_plan_statistics */ n
from (select rank()over(order by n) rnk
            ,n
      from test)
where rnk<=3
/
select * from table(dbms_xplan.display_cursor('','','allstats last'));
drop table test purge;

[collapse]
Output 

         N
----------
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1

10 rows selected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID  8tbq95dpw0gw7, child number 0
-------------------------------------
select/*+ gather_plan_statistics */ n from (select rank()over(order by
n) rnk             ,n       from test) where rnk<=3

Plan hash value: 1892911073

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |      1 |        |     10 |00:00:00.01 |       3 |       |       |          |
|*  1 |  VIEW                  |         |      1 |   1000 |     10 |00:00:00.01 |       3 |       |       |          |
|*  2 |   WINDOW NOSORT STOPKEY|         |      1 |   1000 |     30 |00:00:00.01 |       3 | 73728 | 73728 |          |
|   3 |    INDEX FULL SCAN     | IX_TEST |      1 |   1000 |     31 |00:00:00.01 |       3 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RNK"<=3)
   2 - filter(RANK() OVER ( ORDER BY "N")<=3)

[collapse]

As you can see, A-Rows in plan step 2 = 30 – ie, that is the number of rows where 

DENSE_RANK<=3

but not 

RANK<=3

The more effective way will be to stop after first 10 rows, because 11th row already has RANK more than 3!
But we can create own STOPKEY version with PL/SQL:

PLSQL STOPKEY version 

create or replace type rowids_table is table of varchar2(18);
/
create or replace function get_rowids_by_rank(
      n          int
     ,max_rank   int
   ) 
   return rowids_table pipelined
as
begin
   for r in (
      select/*+ index_rs_asc(t (n))  */ rowidtochar(rowid) chr_rowid, rank()over(order by n) rnk
      from test t
      where t.n > get_rowids_by_rank.n
      order by n
   )
   loop
      if r.rnk <= max_rank then
         pipe row (r.chr_rowid);
      else
         exit;
      end if;
   end loop;
   return;
end;
/
select/*+ leading(r t) use_nl(t) */
   t.*
from table(get_rowids_by_rank(1, 3)) r
    ,test t
where t.rowid = chartorowid(r.column_value)
/

[collapse]
In that case the fetch from a table will stop when rnk will be larger than max_rank

Thanks to all #odevchoice voters!

Posted on by Posted in odevchoice, oracle, Oracle database developer choice award Leave a comment

And special thanks to all the great people who voted for me! 🙂
I want to list all voters for all nomenees in one page: http://orasql.org/odevchoice/all-the-voters.html
The query (using xt_http of course :):

Spoiler 

with 
  finalists(category, userid, name) as (
      --                               SQL~ Voting:
      ------ ------------------ -------------------------------------------------------------------------------------
      select 'SQL'     , 6899,  'Stew Ashton      ' from dual union all
      select 'SQL'     , 6900,  'Sean Stuber      ' from dual union all
      select 'SQL'     , 6901,  'Sayan Malakshinov' from dual union all
      select 'SQL'     , 6902,  'Matthias Rogel   ' from dual union all
      select 'SQL'     , 6903,  'Kim Berg Hansen  ' from dual union all
      select 'SQL'     , 6904,  'Justin Cave      ' from dual union all
      select 'SQL'     , 6905,  'Erik Van Roon    ' from dual union all
      select 'SQL'     , 6906,  'Emrah Mete       ' from dual union all
      --                               PL/SQL~ Voting
      ------ ------------------ -------------------------------------------------------------------------------------
      select 'PL/SQL'  , 6907,  'Sean Stuber      ' from dual union all
      select 'PL/SQL'  , 6908,  'Roger Troller    ' from dual union all
      select 'PL/SQL'  , 6909,  'Patrick Barel    ' from dual union all
      select 'PL/SQL'  , 6910,  'Morten Braten    ' from dual union all
      select 'PL/SQL'  , 6911,  'Kim Berg Hansen  ' from dual union all
      select 'PL/SQL'  , 6912,  'Bill Coulam      ' from dual union all
      select 'PL/SQL'  , 6913,  'Adrian Billington' from dual union all
      --                               ORDS ~ Voting
      ------ ------------------ -------------------------------------------------------------------------------------
      select 'ORDS'    , 6881,  'Tim St. Hilaire  ' from dual union all
      select 'ORDS'    , 6882,  'Morten Braten    ' from dual union all
      select 'ORDS'    , 6883,  'Kiran Pawar      ' from dual union all
      select 'ORDS'    , 6884,  'Dimitri Gielis   ' from dual union all
      select 'ORDS'    , 6885,  'Dietmar Aust     ' from dual union all
      select 'ORDS'    , 6886,  'Anton Nielsen    ' from dual union all
      --                               APEX ~ Voting
      ------ ------------------ -------------------------------------------------------------------------------------
      select 'ORDS'    , 6887,  'Trent Schafer    ' from dual union all
      select 'ORDS'    , 6888,  'Paul MacMillan   ' from dual union all
      select 'ORDS'    , 6889,  'Morten Braten    ' from dual union all
      select 'ORDS'    , 6890,  'Kiran Pawar      ' from dual union all
      select 'ORDS'    , 6891,  'Karen Cannell    ' from dual union all
      select 'ORDS'    , 6893,  'Juergen Schuster ' from dual union all
      select 'ORDS'    , 6894,  'Jari Laine       ' from dual union all
      --                             DatabaseDesign ~ Voting
      ------ ----------------- -------------------------------------------------------------------------------------
      select 'DbDesign', 6896,  'Michelle Kolbe   ' from dual union all
      select 'DbDesign', 6897,  'Mark Hoxey       ' from dual union all
      select 'DbDesign', 6898,  'Heli Helskyaho   ' from dual union all
      select 'DbDesign', 6919,  'Rob Lockard      ' from dual
)
select 
       f.category
      ,f.name         as Nominee
      ,row_number()over(partition by f.category,f.name 
                        order by 
                        case 
                           when regexp_like(t.column_value,'^user\d+$')                      then 2
                           when regexp_like(t.column_value,'^\d+$')                          then 3
                           when regexp_like(t.column_value,'\w{6}-\w{4}-\w{4}-\w{4}-\w{12}') then 4
                           else 1
                        end
                        ) n
      ,t.column_value as VoterName 
from finalists f,
     table(
         xt_http.get_matches(
            pUrl     => 'https://community.oracle.com/voting-history.jspa?ideaID='||to_char(f.userid,'fm0000')||'&start=0&numResults=1000'
           ,pPattern => 'alt="([^"]+)"'
           ,pGroup   => 1
         )
    ) t
order by 1,2,3,4

[collapse]