Hi Chris,
If you are talking about this answer:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9525275800346255150
then I posted later more reliable solution with laterals:
http://orasql.org/2013/07/05/topn-2/
On Mon, Nov 14, 2016 at 11:26 PM, Chris Taylor <
christopherdtaylor1994@xxxxxxxxx> wrote:
I read something the other day and I'm trying to find it - I thought it
was written by Tom Kyte but I cannot [for the life of me] put my hands on
it now.
There's a way to eliminate the SORT step in the execution plan when using
an ORDER BY - but it requires setting up a TYPE to load the contents into
(If I'm remembering correctly - I may not be wording it correctly).
My google-fu is failing me terribly and my history is no help (more due to
sheer volume than anything else).
Basically the scenario is something like:
select a.col1,a.col2, b.col1,b.col2
from a, b
where a.1=b.1
order by x,y,z
/
Becomes:
create type something_mumble_something (
col1 number,
col2 varchar,
col3 varchar,
col4 int );
with t1 (something_mumble_something(a,b,c)
select t1.col1, t1,col2, t1,col3, b.col1,b.col2
from t1, b
where t1.col1 = b.col1
order by t1.col1
/
The SORT in the explain plan will go away in example 2 above but I cannot
find the article. I think it was on Oracle's site (not the asktom site) -
like a blog or something but I'm not positive.
Anyone know what I'm looking for here - or used a TYPE to get rid of sort
step in the explain plan?
Thanks,
Chris
