# XTended Oracle SQL

### Little quiz: Ordering/Grouping – Guess the output

How many times have you guessed the right answer?

1

select * from dual order by -1; select * from dual order by 0;

[collapse] 2

select * from dual order by -(0.1+0/1) desc; select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by -(0.1+0/1) desc;

[collapse] 3

select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 0; select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 0+0; select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 3+7 desc; select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by -(3.1+0f) desc;

[collapse] 4

select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by 1.9; select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by 2.5; select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by 2.7 desc; select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by -2.7 desc;

[collapse]

### 12c: Little test of “TABLE ACCESS INMEMORY FULL” with count stopkey

The table has 9M rows:

SQL> with function f return int is 2 begin 3 for r in (select value from v$mystat natural join v$statname where name like 'IM scan rows') loop 4 dbms_output.put_line(r.value); 5 return r.value; 6 end loop; 7 end; 8 select f() from t_inmemory where rownum<=1 9 ; 10 / F() ---------- 0 1 row selected. SQL> / F() ---------- 491436 1 row selected. SQL> / F() ---------- 982872 1 row selected.DDL and Plan

create table t_inmemory inmemory as with gen as (select 0 id from dual connect by level<=3e3) select 0 n from gen,gen; SQL_ID cpgrrfv9h6m52, child number 0 ------------------------------------- with function f return int is begin for r in (select value from v$mystat natural join v$statname where name like 'IM scan rows') loop dbms_output.put_line(r.value); return r.value; end loop; end; select f() from t_inmemory where rownum<=1 Plan hash value: 3697881339 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 3 (100)| | |* 1 | COUNT STOPKEY | | | | | | 2 | TABLE ACCESS INMEMORY FULL| T_INMEMORY | 1 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=1)

[collapse]

### Easy quiz: rownum < NaN

As you know, NaN is a “Not a Number”.

How do you think, what would be the result of the following query? *(0f/0 == NaN)*

select count(*) cnt from dual where rownum < 0f/0;Answer

SQL> select count(*) cnt, 0f/0 from dual where rownum < 0f/0; CNT 0F/0 ---------- ---------- 1 Nan 1 row selected.

[collapse]

Ok, when you know the result, try to guess what will return this query:

select count(*) cnt from dual where 1f/0 < 0f/0;Answer

SQL> select count(*) cnt from dual where 1f/0 < 0f/0; CNT ---------- 1 1 row selected.

[collapse] PS

1 * X < 0 * Y

So we know now which non-negative values we should to substitute for X and Y in this expression, to make it true.

[collapse]### select * from table where rownum=1

I never thought I would have to optimize so simple query as

select col1, col2, col4, col7 from table where rownum=1

(even though I read recently “SELECT * FROM TABLE” Runs Out Of TEMP Space)

But a few days ago frequent executions of this query caused big problems on the one of our databases(11.2.0.3) because of adaptive serial direct path reads.

I don’t know why, but I felt intuitively that full table scan with “First K rows” optimization (“*_optimizer_rownum_pred_based_fkr*“=*true*) should turn off adaptive serial direct path reads. It seems quite logical to me.

PS. Unfortunately I had a little time, so I didn’t investigate what process and why it was doing that, I just created profile with “index full scan” access, and it completely solved the problem.