Re: Re: Index not used to avoid sort with calculated column
From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 27 Jun 2019 08:40:31 +0000
Message-ID: <LO2P265MB0415402896A736797BC3F463A5FD0_at_LO2P265MB0415.GBRP265.PROD.OUTLOOK.COM>
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch> Sent: 27 June 2019 08:57
To: tanel_at_tanelpoder.com
Cc: martin.a.berger_at_gmail.com; oracle-l_at_freelists.org Subject: Re: Re: Index not used to avoid sort with calculated column
Date: Thu, 27 Jun 2019 08:40:31 +0000
Message-ID: <LO2P265MB0415402896A736797BC3F463A5FD0_at_LO2P265MB0415.GBRP265.PROD.OUTLOOK.COM>
With the usual warning that including "invisible" is highly desirable in 12c to pre-empt the threats of:
select * ....
and
insert without (list of columns)
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch> Sent: 27 June 2019 08:57
To: tanel_at_tanelpoder.com
Cc: martin.a.berger_at_gmail.com; oracle-l_at_freelists.org Subject: Re: Re: Index not used to avoid sort with calculated column
Hi Tanel,
thanks for your help. I is good to have collegues who can help. I was suspecting something like that, but I am up to my neck in work and could not affort testing on suspicion. As from the doc I think going for the virtual column being preferrable. It avoids using an underscore parameter.
drop index e_low_sort1;
alter table emp add (low_job as (lower(job)) virtual);
create index e_low_sort1 on emp (low_job, hiredate);
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 27 2019 - 10:40:31 CEST