Oracle Riddle Blog

Is this the sort of column that gets inserted into not-null as a flag that some task needs to be run against it? The task would then set this column to null to mark it as processed. If you had a big batch to run, or the task hadn’t run in some time (looking at your 41K buffers, that must be quite a lot of rows!) then the index would have grown – setting the column back to null isn’t going to shrink it again on it’s own but will mean the index can shrink if asked to.

The same sort of thing happens with AQ tables, except the rows get deleted (although that can some time after processing)

LikeLike

Hi Martin,

thanks. Good Point. Just hard to find the right name for the column. I will use Mohamed’s “DDL optimized” column instead.

Lothar

LikeLike

Lothar,
a nice example and certainly a feature with interesting (and sometimes strange) effects. But I am not sure if I would use the term “virtual” in this context since – if my memory serves me well – the column is technically not a virtual column. Though I would certainly agree that storing the defaults only in the dictionary for rows that have been there before the addition of the column makes these values somehow virtual. Mohamed Houri has written an instructive OTN article containing some additional details: http://www.oracle.com/technetwork/articles/database/ddl-optimizaton-in-odb12c-2331068.html.

Regards
Martin

LikeLike

Hi Bryn,
thanks for replying. You are raising an important point here by suggesting to do the join in SQL rather than in PL/SQL. However as I wrote I wanted two loops rather than one. In your solution you are replacing the other loop with an IF checking for the change of the department number. I was aiming for the employees nested as a collection in the departments. Well, and You don’t need to bother to limit the bulk collect. (You can, if you like).

LikeLike

I had to tidy up the example to impose proper style (like adding “order by”) and to make it do something:

declare
cursor c1 is
select d.Deptno, d.Dname from Dept d order by d.Deptno;
cursor c2 (Deptno Dept.Deptno%type) is
select e.Empno, e.Ename from Emp e where e.Deptno = c2.Deptno order by e.Empno;
begin
for r1 in c1 loop
DBMS_Output.Put_Line(Chr(10)||r1.Deptno||’ ‘||r1.Dname);
for r2 in c2(r1.Deptno) loop
DBMS_Output.Put_Line(‘ ‘||r2.Empno||’ ‘||r2.Ename);
end loop;
end loop;
end;

It’s performing a left outer join using nested loops programmed in PL/SQL. Here is the output:

10 ACCOUNTING
7782 CLARK
7839 KING
7934 MILLER

20 RESEARCH
7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD

30 SALES
7499 ALLEN
7521 WARD
7654 MARTIN
7698 BLAKE
7844 TURNER
7900 JAMES

40 OPERATIONS

Here’s the SQL:

select Deptno, d.Dname, e.Empno, e.Ename
from Dept d left outer join Emp e using (Deptno)
order by Deptno, e.Empno

Programming a join in PL/SQL is one of the famous crimes of procedural guys who are new to SQL.

We can simply bulk collect this — using the “limit” clause if called for.

I have to assume that the “complex logic” does something for each row in the driving master Dept loop and then, within that, something for each child Emp row within each master. This is like the SQL*Plus “break” report of old. So is the question actually “How to program ‘break’ logic?”

Here you are:

declare
type Row_t is record(
Deptno Dept.Deptno %type not null := -1,
Dname Dept.Dname %type,
Empno Emp. Empno %type,
Ename Emp. Ename %type);
type Rows_t is table of Row_t index by pls_integer;
Rows Rows_t;
Prev_Deptno Dept.Deptno%type not null := -1;
begin
select Deptno, d.Dname, e.Empno, e.Ename
bulk collect into Rows
from Dept d left outer join Emp e using (Deptno)
order by Deptno, e.Empno;

for j in 1..Rows.Count loop
if Rows(j).Deptno Prev_Deptno then
DBMS_Output.Put_Line(Chr(10)||Rows(j).Deptno||’ ‘||Rows(j).Dname);
Prev_Deptno := Rows(j).Deptno;
end if;
if Rows(j).Empno is null then
DBMS_Output.Put_Line(‘ No employees’);
else
DBMS_Output.Put_Line(‘ ‘||Rows(j).Empno||’ ‘||Rows(j).Ename);
end if;
end loop;
end;

Here is the output:

10 ACCOUNTING
7782 CLARK
7839 KING
7934 MILLER

20 RESEARCH
7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD

30 SALES
7499 ALLEN
7521 WARD
7654 MARTIN
7698 BLAKE
7844 TURNER
7900 JAMES

40 OPERATIONS
No employees

Now tell me what in your question I’m failing to grasp.

LikeLike

Hi Helmut, thanks for actually answering my second question ;). But no the block size is identical.

LikeLike

Hi Cary, it is no bug or similar. It is indented behavior.

LikeLike

Hi Stefan, correct there are no execution plan changes. But still scattered reads are reads and there are not any reads, are there not? Yes, I know what you mean and you are right, but it is a bit trickly.

LikeLike

under which circumstance will that happen? How do you test it?

LikeLike