October 21, 2009 - 9am Central time zone
Reviewer: A reader
Thanks for explaining, Tom!
(Makes me curious though, as to why you need to concatenate, but that's a different question...)
And yes, I get the results again.
> I had to rebuild the index...
So, here's a valid reason to rebuild an index, however:
Let sleeping dogs lie? ;)
Regards,
Martijn
Followup October 23, 2009 - 11am Central time zone:
The data for the index comes from three different tables and many rows.
There is no "page" stored anywhere - all of the data on this page is assembled from many smaller bits.
I wanted to give you the impression you were searching a page, the page doesn't really exist anywhere persistently, so I have a procedure that returns to TEXT a page to index (minus HTML, formatted with just the stuff I want searchable...)
And in actuality, I was really creating a *new* index that never existed before :) The old procedure wasn't doing the right thing in the new database - the output changed in the new code.
Why was the search function offline?
October 21, 2009 - 10am Central time zone
Reviewer: Annoyed from Melbourne
"I had to rebuild the index (so if you noticed search not working for a brief period on 20-oct-2009
- that is why) but it should be all good to go for now. "
Why would you take the search function offline to rebuild the search index?!
You would not take the database offline to update the name of a customer.
You would not take the database offline to insert 100 rows into a table.
It is very disappointing that you have decided to take the search function offline. Now, you will
probably claim that AskTom is not a production site. But this is to miss the point. An application
based on the Oracle database should not have to be taken offline in order to "rebuild a search
index."
Followup October 23, 2009 - 12pm Central time zone:
because I recreated it - I had a code change.
be annoyed, you were asleep when it happened anyway :)
I had a DDL modification to make, could I have done it online? Yes, it would have take a lot more resources - resources that frankly on apex.oracle.com I don't have anymore ( I don't own the machine, I'm a customer on the machine now).
I was fighting the restrictions of the resource manager (everything on apex.oracle.com is resource limited). I was fighting a disk quota. I was fighting "my time versus a little down time"
A little down time won - hands down. It took me about 5 seconds to decide that.
High Availability comes at a price, a high price. The more available, the higher the price.
Could I have put in for more storage to allow for two of a big thing? Probably, it would cost me - in terms of money and in terms of time (I'd have to wait)
Could I have created the new index while the old one existed? Not easily - but I could have had I spent the time to do so. It would cost me. In terms of my time.
Could I have gotten a pass on the resource limits? Yes, probably but it would cost me - in terms of money and in terms of time (I'd have to wait)
I didn't think the costs were justified.
And I'll be down again tonight - they are upgrading the database. Could it be done online?
Yes, it could.
Would it be worth the cost?
No, it wouldn't
So, it will be an offline upgrade of apex.oracle.com - because High Availability costs a lot and it isn't worth the cost for this service.
Now, if I were amazon.com where the act of being down would cost ME money - and the amount of money it would cost ME outweighs the cost of doing it with zero downtime - then we'd have a different story.
October 22, 2009 - 3am Central time zone
Reviewer: Martin Vajsar
Dear Annoyed,
although I'm pretty sure it was technically possible to rebuild said index online, there are sometimes very legitimate reasons to choose otherwise, even in production. In this case, I'd say the considerations would be:
1) It was probably faster to rebuild it offline. The service was therefore restored sooner. I don't know how serious the issue was, but search function that does not return what has been searched for is useless at best (and detrimental at worst).
2) Even the most annoyed person on Earth probably knows how to use Google, so replacement for the function was readily at hand no more than two clicks away.
3) To me, it is much more annoying when some service just returns wrong values than when it is completely offline or clearly states that something is broken. Trying to figure out what's wrong when the results just look a bit suspicious is one of the most frustrating and time-wasting activities I have experienced.
No longer annoyed
October 23, 2009 - 12pm Central time zone
Reviewer: No longer annoyed from Melbourne
"be annoyed, you were asleep when it happened anyway :) "
I must admit, I laughed when I read that. I had been outplayed! And I say, that was very good :)
Incidentally, after I reviewed my original reply, I did realise that, yes, the cost would not have
been justified.
What kind index is that?
October 23, 2009 - 12pm Central time zone
Reviewer: Atl
---The data for the index comes from three different tables and many rows. ---
Is an index on a view?
Followup October 23, 2009 - 3pm Central time zone:
no, on a procedure, Oracle text can index the results of a procedure that takes a textkey as input and returns a clob as output.
so (this is oldish code but gist is there)
create or replace procedure index_ask_tom( p_id in rowid, p_lob IN OUT clob )
as
begin
for x in ( select *
from ask_tom.WWC_ASK_INDEXED_QUESTIONS$
where rowid = p_id )
loop
dbms_lob.copy( p_lob, x.text, dbms_lob.getLength(x.text) );
for y in ( select REVIEW_TITLE || ' ' || REVIEWER_NAME || ' ' ||
REVIEWER_LOCATION || ' ' subj,
review_comments
from ask_tom.WWC_ASK_QUESTION_REVIEWS$
where displayid = x.displayid )
loop
dbms_lob.writeAppend( p_lob, length(y.subj), y.subj );
dbms_lob.append( p_lob, y.review_comments );
end loop;
for y in ( select followup_comments
from ask_tom.WWC_ASK_REVIEW_FOLLOWUPS$
where displayid = x.displayid )
loop
dbms_lob.writeAppend( p_lob, 1, ' ' );
dbms_lob.append( p_lob, y.followup_comments );
end loop;
end loop;
end;
/
begin
ctx_ddl.create_preference('ask_tom_user_datastore',
'user_datastore' );
ctx_ddl.set_attribute( 'ask_tom_user_datastore', 'procedure',
'INDEX_ASK_TOM' );
end;
/
create index search_idx on WWC_ASK_INDEXED_QUESTIONS$(dummy)
indextype is ctxsys.context parameters( 'datastore ask_tom_user_datastore lexer my_lexer')
/
The Frontend
October 26, 2009 - 11am Central time zone
Reviewer: Ash from India
Hi Tom,
Since we're on the topic of the Asktom website, I thought I'd bring this up here.
It has to do with the front end. Suppose I'm browsing a page that has results 76 through 90. I
click on a question and then go through the thread. Now when I click the Back button of my browser
to go through the next thread, I am back to results 1 through 15! A minor annoyance, but not
to someone who's deep down the line. Room for improvement?
Thanks for your time!
Followup October 26, 2009 - 2pm Central time zone:
lower right hand side of the home page
"open pages in new window"
that is what I use (i almost always click links with middle mouse or ctl-mouse to have that happen on all websites - back is evil, especially when the page you back into was a 'posted' page)
October 26, 2009 - 11am Central time zone
Reviewer: A reader
So you put a TEXT index on a procedure. What does your SQL look like that would cause this index to
be used? I'm trying to understand this index on a procedure concept, but I'm quite getting it. Can
you please provide a quick demo?
Followup October 26, 2009 - 3pm Central time zone:
Ok, let's say you have EMP and DEPT
and you have a need to search for information across EMP and DEPT - specifically, you need to return rows for hits on Dname, Job and Ename - all three fields should be used to find DEPT records.
Now, DEPT doesn't have job and ename - rather dept has many jobs and enames related to it in the EMP table. So, rather than querying a join with a bit or going across two tables - we'll index the data.
In our index, we'll have dname, job and ename
here is the demo:
ops$tkyte%ORA11GR2> create table emp as select * from scott.emp;
Table created.
ops$tkyte%ORA11GR2> create table dept as select * from scott.dept;
Table created.
ops$tkyte%ORA11GR2> exec dbms_stats.set_table_stats( user, 'EMP', numrows => 100000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec dbms_stats.set_table_stats( user, 'DEPT', numrows => 10000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> create index emp_deptno_idx on emp(deptno);
Index created.
ops$tkyte%ORA11GR2> alter table dept add amorphous varchar2(1);
Table altered.
this is the column we'll index and search on. It will NEVER have data. It will consume NO
STORAGE. We will update this column - but never store anything in it
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> alter table dept add constraint dept_pk primary key(deptno);
Table altered.
ops$tkyte%ORA11GR2> alter table emp add constraint emp_fk_dept foreign key(deptno) references dept;
Table altered.
ops$tkyte%ORA11GR2> alter table emp modify deptno not null;
Table altered.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace procedure my_text_procedure( p_id in rowid, p_lob in out clob
)
2 as
3 begin
4 select dname into p_lob
5 from dept
6 where rowid = p_id;
7
8 for x in (select job, ename from emp where deptno = ( select deptno from dept where
rowid = p_id ))
9 loop
10 p_lob := p_lob || ' ' || x.job || ' ' || x.ename;
11 end loop;
12 end;
13 /
Procedure created.
that is the procedure that glues together what we need...
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> begin
2 ctx_ddl.create_preference('my_user_datastore', 'user_datastore' );
3 ctx_ddl.set_attribute( 'my_user_datastore', 'procedure', 'MY_TEXT_PROCEDURE' );
4 end;
5 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create trigger emp
2 after insert or delete or update of job,ename on emp for each row
3 begin
4 if (inserting or updating)
5 then
6 update dept set amorphous=amorphous where deptno=:new.deptno;
7 end if;
8 if (inserting or (updating AND :new.deptno <> :old.deptno))
9 then
10 update dept set amorphous=amorphous where deptno=:old.deptno;
11 end if;
12 end;
13 /
Trigger created.
that trigger will touch the parent row when the child is modified - when we commit a change
against the child, it'll update the index on the parent for us
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create index search_index on dept(amorphous) indextype is ctxsys.context
parameters( 'datastore my_user_datastore sync (on commit)' );
Index created.
and now we can query:
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from dept where contains( amorphous, 'Smith' ) > 0;
DEPTNO DNAME LOC A
---------- -------------- ------------- -
20 RESEARCH DALLAS
ops$tkyte%ORA11GR2> select deptno from emp where ename = 'SMITH';
DEPTNO
----------
20
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from dept where contains( amorphous, 'president' ) > 0;
DEPTNO DNAME LOC A
---------- -------------- ------------- -
10 ACCOUNTING NEW YORK
ops$tkyte%ORA11GR2> select * from dept where contains( amorphous, 'accounting' ) > 0;
DEPTNO DNAME LOC A
---------- -------------- ------------- -
10 ACCOUNTING NEW YORK
ops$tkyte%ORA11GR2> insert into emp(ename,job,deptno) values ( 'TKYTE', 'Asktom', 10 );
1 row created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> set autotrace on explain
ops$tkyte%ORA11GR2> select * from dept, emp where contains( amorphous, 'TKYTE' ) > 0 and emp.deptno
= dept.deptno;
DEPTNO DNAME LOC A EMPNO ENAME JOB MGR HIREDATE
SAL COMM DEPTNO
---------- -------------- ------------- - ---------- ---------- --------- ---------- ---------
---------- ---------- ----------
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 09-JUN-81
2450 10
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 17-NOV-81
5000 10
10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 23-JAN-82
1300 10
10 ACCOUNTING NEW YORK TKYTE Asktom
10
Execution Plan
----------------------------------------------------------
Plan hash value: 223998782
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 6550 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 50 | 6550 | 10 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 5 | 220 | 5 (0)| 00:00:01 |
|* 4 | DOMAIN INDEX | SEARCH_INDEX | | | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 32 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | EMP | 10 | 870 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("CTXSYS"."CONTAINS"("AMORPHOUS",'TKYTE')>0)
5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
ops$tkyte%ORA11GR2> set autotrace off
October 27, 2009 - 12pm Central time zone
Reviewer: A reader
Does this concept of indexing a procedure (hence indexing columns across tables) work only with
TEXT indexes? Is it possible to apply this to a regular index? Suppose I have a pagination query
and the SQL is ordered by columns from two different tables. Is there anyway I can create a
composite index on those columns so that the records can be retrieved optimally by traversing
through the index n records at a time?
Followup October 27, 2009 - 4pm Central time zone:
you have function based indexes - but those MUST BE DETERMINISTIC - meaning: if they query a table - they are probably not deterministic since tables change over time.
So, basically, this is "it" as far as this technique goes.
And in your case:
... Suppose I have a pagination query and the SQL is ordered by columns
from two different tables. ...
are those columns what you "where on" as well - if not, it could be a very bad idea to use an index on them.
But tell us more, give us a concrete example - is the data updatable, how often and how? give us a "for example"
October 28, 2009 - 5pm Central time zone
Reviewer: A reader
Ok. Here is an example. Not the best example in the world, but may be good enough to demonstrate what I'm trying to achieve. I'd like to be able for SQL #2 to use a similar execution plan as that used in SQL #1. This is easily achievable if emp and dept were one "flattened" table. However, that would not be a normalized structure.
SQL>
SQL> DROP TABLE emp;
Table dropped.
SQL> DROP TABLE dept;
Table dropped.
SQL> DROP SEQUENCE emp_seq;
Sequence dropped.
SQL>
SQL> CREATE TABLE dept (
2 deptno INTEGER NOT NULL PRIMARY KEY,
3 dName VARCHAR2(20) NOT NULL,
4 CreateDate DATE NOT NULL
5 );
Table created.
SQL>
SQL> CREATE INDEX dept_CreateDate_idx ON dept(CreateDate);
Index created.
SQL>
SQL> CREATE TABLE emp (
2 empno INTEGER NOT NULL PRIMARY KEY,
3 eName VARCHAR2(20) NOT NULL,
4 job VARCHAR2(10) NOT NULL,
5 hireDate DATE NOT NULL,
6 deptno INTEGER NOT NULL REFERENCES dept(deptno)
7 );
Table created.
SQL>
SQL> CREATE INDEX emp_fk1 ON emp(deptno);
Index created.
SQL>
SQL> CREATE SEQUENCE emp_seq;
Sequence created.
SQL>
SQL> INSERT INTO dept
2 SELECT rownum deptno,
3 'Dept' || rownum dName,
4 SYSDATE - 1000 - TRUNC(dbms_random.value(1, 500)) CreateDate
5 FROM dual
6 CONNECT BY level <= 50000;
50000 rows created.
SQL>
SQL>
SQL> DECLARE
2 CURSOR c IS
3 SELECT deptno
4 FROM dept;
5
6 l_counter INTEGER := 1;
7
8 BEGIN
9 FOR x IN c LOOP
10 FOR i IN 1..10 LOOP
11 INSERT INTO emp VALUES (
12 emp_seq.NEXTVAL,
13 'ename' || l_counter,
14 'job' || l_counter,
15 SYSDATE - TRUNC(dbms_random.value(1, 1000)),
16 x.deptno
17 );
18
19 l_counter := l_counter + 1;
20 END LOOP;
21 END LOOP;
22 END;
23 /
PL/SQL procedure successfully completed.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(
3 ownname => user,
4 tabname => 'DEPT',
5 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
6 method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
7 cascade => TRUE
8 );
9
10 DBMS_STATS.GATHER_TABLE_STATS(
11 ownname => user,
12 tabname => 'EMP',
13 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
14 method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
15 cascade => TRUE
16 );
17 END;
18 /
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace traceonly
SQL>
SQL> -- ------------------------------------------------------------------
SQL> -- SQL #1
SQL> --
SQL> -- This has a more efficient plan, but same records may appear across
SQL> -- pages because CreateDate is not unique.
SQL> -- ------------------------------------------------------------------
SQL> SELECT *
2 FROM (
3 SELECT x.*, rownum rn
4 FROM (
5 SELECT d.dName, e.eName, e.Job, e.HireDate
6 FROM dept d, emp e
7 WHERE d.deptno = e.deptno
8 ORDER BY d.CreateDate
9 ) x
10 WHERE rownum <= 20
11 )
12 WHERE rn >= 1;
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3599532986
----------------------------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 20 | 1060 | 14 (0)|
00:00:01 |
|* 1 | VIEW | | 20 | 1060 | 14 (0)|
00:00:01 |
|* 2 | COUNT STOPKEY | | | | |
|
| 3 | VIEW | | 20 | 800 | 14 (0)|
00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | EMP | 7 | 245 | 3 (0)|
00:00:01 |
| 5 | NESTED LOOPS | | 20 | 1140 | 14 (0)|
00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 50000 | 1074K| 5 (0)|
00:00:01 |
| 7 | INDEX FULL SCAN | DEPT_CREATEDATE_IDX | 3 | | 2 (0)|
00:00:01 |
|* 8 | INDEX RANGE SCAN | EMP_FK1 | 7 | | 2 (0)|
00:00:01 |
----------------------------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=20)
8 - access("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
1463 bytes sent via SQL*Net to client
407 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
SQL>
SQL> -- ------------------------------------------------------------------
SQL> -- SQL #1
SQL> --
SQL> -- This has a less efficient plan, but same records will not appear
SQL> -- across pages because empno makes a record unique.
SQL> -- ------------------------------------------------------------------
SQL> SELECT *
2 FROM (
3 SELECT x.*, rownum rn
4 FROM (
5 SELECT d.dName, e.eName, e.Job, e.HireDate
6 FROM dept d, emp e
7 WHERE d.deptno = e.deptno
8 ORDER BY d.CreateDate, e.empno
9 ) x
10 WHERE rownum <= 20
11 )
12 WHERE rn >= 1;
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3275588944
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1060 | | 9535 (2)| 00:01:55 |
|* 1 | VIEW | | 20 | 1060 | | 9535 (2)| 00:01:55 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 499K| 19M| | 9535 (2)| 00:01:55 |
|* 4 | SORT ORDER BY STOPKEY| | 499K| 29M| 73M| 9535 (2)| 00:01:55 |
|* 5 | HASH JOIN | | 499K| 29M| 1664K| 2075 (3)| 00:00:25 |
| 6 | TABLE ACCESS FULL | DEPT | 50000 | 1074K| | 57 (4)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 498K| 18M| | 713 (4)| 00:00:09 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=20)
4 - filter(ROWNUM<=20)
5 - access("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3328 consistent gets
0 physical reads
0 redo size
1463 bytes sent via SQL*Net to client
407 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20 rows processed
Followup October 29, 2009 - 7am Central time zone:
but you did not answer any of my questions? I have emp and dept - I would have used them to demonstrate with.
but yes, you need to order by something deterministic - else the concepts of "rows 20-30" doesn't really mean anything
but we can limit the amount of work necessary by
a) getting a deterministic slice of dept that will provide enough rows to AT LEAST get our 20
b) then go after emp
ops$tkyte%ORA10GR2> with d
2 as
3 (select * from (select * from dept order by createdate) where rownum <= 20),
4 e
5 as
6 (
7 select x.*, rownum rn
8 from ( select d.dname, emp.ename, emp.job, emp.hiredate , d.createdate, emp.empno
9 from d, emp
10 where d.deptno = emp.deptno
11 order by d.createdate, emp.empno ) x
12 where rownum <= 20
13 )
14 select * from e where rn >= 1 order by rn
15 /
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 203022047
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1500 | | 358 (2)| 00:00:03
|
| 1 | SORT ORDER BY | | 20 | 1500 | | 358 (2)| 00:00:03
|
|* 2 | VIEW | | 20 | 1500 | | 357 (2)| 00:00:03
|
|* 3 | COUNT STOPKEY | | | | | |
|
| 4 | VIEW | | 200 | 12400 | | 357 (2)| 00:00:03
|
|* 5 | SORT ORDER BY STOPKEY | | 200 | 13000 | | 357 (2)| 00:00:03
|
| 6 | TABLE ACCESS BY INDEX ROWID| EMP | 10 | 390 | | 3 (0)| 00:00:01
|
| 7 | NESTED LOOPS | | 200 | 13000 | | 356 (2)| 00:00:03
|
| 8 | VIEW | | 20 | 520 | | 297 (3)| 00:00:03
|
|* 9 | COUNT STOPKEY | | | | | |
|
| 10 | VIEW | | 50000 | 1269K| | 297 (3)| 00:00:03
|
|* 11 | SORT ORDER BY STOPKEY | | 50000 | 1074K| 3160K| 297 (3)| 00:00:03
|
| 12 | TABLE ACCESS FULL | DEPT | 50000 | 1074K| | 19 (6)| 00:00:01
|
|* 13 | INDEX RANGE SCAN | EMP_FK1 | 10 | | | 2 (0)| 00:00:01
|
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RN">=1)
3 - filter(ROWNUM<=20)
5 - filter(ROWNUM<=20)
9 - filter(ROWNUM<=20)
11 - filter(ROWNUM<=20)
13 - access("D"."DEPTNO"="EMP"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
301 consistent gets
0 physical reads
0 redo size
1656 bytes sent via SQL*Net to client
411 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
20 rows processed
but this is one of the *rare* cases whereby a pipelined function with a "do it yourself nested loop join" might be appropriate.
|