RE: Deigning suggestion for table and indexes

with the bold assumption that order by TABLE1.PR_TIME DESC

is within the partition day for each partition, doing it pairwise partitions makes the n a lot smaller in the n log n sorting operation.  

Now if you’re NOT partitioned by something that is coincidentally in the same order as you desire for the top N, then stringing the pairwise partitions together as a union all the result of which is sorted is required. In the case you presented and that presuming that time is within the daily partition, the concatenated results are also sorted.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of yudhi s Sent: Sunday, February 11, 2024 3:37 PM
To: Andy Sayer
Cc: Oracle L
Subject: Re: Deigning suggestion for table and indexes  

Thank You Andy.  

Even if the count query is removed, I think the TOP N will still need to be doing the sorting to find the top based on a certain column. And I think to make the sorting in a less resource intensive way, below Jonathan's Blog was giving helpful pointers. It's mostly sorting cheaply using indexes and grabbing the rowids first and then getting the data using those rowids.  

https://jonathanlewis.wordpress.com/2008/05/09/manual-optimisation-2/  

In regards to make the above posted query runs faster in general , will it be good idea to have the composite indexes (i.e. Filters along with the JOIN column PR_ID) or is it advisable to just create the indexes on the filter column alone?  

Regards

Yudhi  

On Mon, Feb 12, 2024 at 1:23 AM Andy Sayer <andysayer_at_gmail.com> wrote:

There was another recent thread with the same sort of question. But the summary of my thoughts are: if you want to do fast top N style pagination then you do not want to also be reporting the count of results.  

Thanks,

Andy  

On Sun, 11 Feb 2024 at 05:40, yudhi s <learnerdatabase99_at_gmail.com> wrote:

Hello All,

Below was the question in a discussion and I want to understand experts' opinion.  

The requirement is to have the response time in <1 sec for our UI search query requirement. These will be pagination queries. These read queries will be on big transaction tables (will have ~500+ attributes approx will have approx. rows size of ~1KB) having a continuous stream of inserts consumed from source. And these tables will be a daily range partitioned on the processing_date column. Each partition is going to hold approx ~450million rows when it will serve in full capacity to all the customers transactions.  

 The customer will be given the capability to search on a Max date range of 30 days of transaction data i.e ~30 range partitions and are supposed to get the query response back in <1 sec as it will be UI from which those queries will be submitted. And pagination means the first page will show the latest 100 records , the second page will show 100 to 200 and so on. And count(*) is in the query because , UI also going to show how many total pages are going to be there as per total record set.

select count(*) over() as total_record, * from

    (select .......

        from   TABLE1
            Left join schema1.TABLE2  on TABLE2.PR_ID = TABLE1.PR_ID  and TABLE2.MID = TABLE1.MID
and TABLE2.processing_date=TABLE1.processing_date
        where   TABLE2.processing_date between '2023-04-20' and '2023-05-21'-- Partition pruning
            and TABLE2.ACN_NBR = 'XXXX'
            and ( TABLE1.MID in (XXXXXX) OR TABLE1.CID in (XXXXXX))
        order by   TABLE1.PR_TIME DESC

count(distinct ACN_NBR) - 25million
count(distinct MID) - 223k
count(distinct CID) - 59k
count(*)from table1 and table2- ~350 million

Regards

Yudhi

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