Re: Looking for something I read - ORDER BY performance using a TYPE instead

Nevermind - I found it. Keywords were "oracle sort avoidance"

http://www.oracle.com/technetwork/issue-archive/2013/13-may/o33asktom-1917335.html

Cheers,
Chris

On Mon, Nov 14, 2016 at 2:26 PM, Chris Taylor < christopherdtaylor1994_at_gmail.com> 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
>
>

--
http://www.freelists.org/webpage/oracle-l