Looking for something I read - ORDER BY performance using a TYPE instead
Date: Mon, 14 Nov 2016 14:26:05 -0600
Message-ID: <CAP79kiTMu3W_kPAHnti_nJ3k3H0e_ZnJ2w9NFBhXqUFF=RU8vg_at_mail.gmail.com>
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-lReceived on Mon Nov 14 2016 - 21:26:05 CET