Re: How to minimise sorting time in a query
Date: Sat, 05 Nov 2022 18:41:35 -0400
Message-ID: <9e500ecdbd4c43ab261cf3e55eebd908b3381a61.camel_at_gmail.com>
On Sun, 2022-11-06 at 01:48 +0530, Lok P wrote:
> Hello Listers, In a 19C Oracle database. One requirement is to just
> pick all the records sorted by two columns from a lookup table. The
> table holds ~1billion rows. And this data needs to be cached daily 8-
> 10 times as it's getting used by an ETL workflow for lookup and it's
> queried in sorted fashion from the database by that ETL workflow. The
> issue is that the majority of time is spent while sorting those
> ~1billion rows each time. Below is the sample sqlmonitor of the sql
> query. We wanted to understand what best we can do to minimize the
> response time of this query so that ETL workflow time can be reduced?
The first thing that comes to mind is creating the table as an index organized table. Then it is sorted and you don't have to sort it again.
-- Mladen Gogala Database Consultant https://dbwhisperer.wordpress.com -- http://www.freelists.org/webpage/oracle-lReceived on Sat Nov 05 2022 - 23:41:35 CET