Deigning suggestion for table and indexes
Date: Sun, 11 Feb 2024 19:09:33 +0530
Message-ID: <CAEzWdqeebeda3N3KFiPmf8V+4zsANxggWuOZMEAio69gD81dWA_at_mail.gmail.com>
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 andTABLE2.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
)
limit 100 offset 0;
1)What would be the appropriate indexes to make this above search query run in the quickest possible time?
one Index on table1(MID) , one index Table1(CID), one index on
table2(ACN_NBR)?
OR
Should we create a composite index here combining PR_ID i.e (PR_ID, MID),
(PR_ID, CID), (PR_ID, ACN_NBR) as that is the most unique attribute here?
The data pattern for the columns used in predicate are as below:- Table1 will be the driving table.
count(distinct ACN_NBR) - 25million count(distinct MID) - 223k count(distinct CID) - 59k count(*)from table1 and table2- ~350 millionPR_ID is a unique key.
3)One of the use cases is that the customer should be able to search on certain attributes and should be able to see the transactions in "desc by processing_date" i.e. latest transactions on the first page on the UI. And in such scenario, if the search attribute is less unique and the customer puts a date range of a month i.e. over 30 partitions , it may results in scanning and sorting billions of rows to get the top/recent ~100 transactions and most likely not going to respond back in <1 sec, even goes for the index. So how should we handle or design indexes for catering such queries? For e.g. if we have the only filter on column "TABLE1.CID" in the above query, which is very less unique then how to handle it?
Regards
Yudhi
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Feb 11 2024 - 14:39:33 CET