Re: How to minimise sorting time in a query

You table is range partitioned - what's the partitioning column? Do the table have a primary key?

You want to sort by c1, c2 - if you're not range partitioned by c1 you will HAVE to do a sort to get the data in order. As Mladen suggests, the best option for your requirement would be to turn the table into an IOT, but that's only possible if the table has a primary key, and then you need the primary key to start with (c1,c2) if you want to avoid a sort, and you have to worry about the impact on data loading. If you need other indexes on the table you then have to worry about the effects on those indexes (for both loading and querying).

Your second best option might be to create an index on c1, c2, c3, globally partitioned by range on c1, then hint the query to do a full scan on the index - and not allow parallelism. This requires a MAXVALUE partition on C1, so you may need to put in place a mechanism to keep the MAXVALUE partition empty and split it on a regular basis so that you don't end up with one enormous partition some time in the future.

Regards
Jonathan Lewis

On Sat, 5 Nov 2022 at 20:18, Lok P <loknath.73_at_gmail.com> 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?
>
> It's already using ~1GB of memory and ~30GB of temp. The rows are expected
> to grow 2billion+. So setting manual memory management and sort_area_size
> as 2GB may not help in the long run. Tried running in parallel(4) and
> parallel(8) but then no relief in the amount of temp spill, rather the
> response time seems to be increasing further. Is there any other possible
> way to reduce the execution time of this query?
>
> The table has three columns with below data structure/data types.
>
> C1 VARCHAR2 (34 Byte)
> C2 VARCHAR2 (1 Byte)
> C3 VARCHAR2 (34 Byte)
> CREATE_BY VARCHAR2 (50 Byte)
> DT_CREATE DATE
>
>
> SELECT C3,c1,c2 FROM TAB1 ORDER BY c1,c2;
>

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