Re: Deigning suggestion for table and indexes

Thank you so much.

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
> )
> 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 million
> PR_ID is a unique key
>
>
Actually table1 is the driving table and thus the ordering of final result set is also happening using pr_time column of table1.

We have few searches which only happen on CID column of table1(which is less selective one) , in those cases, for making the access of table1 faster, will it be good idea to include joined column pr_id column (which is fully unique) in the index after having filter column and sorting column as leading columns (I.e. index on CID, PR_Time, PR_ID) for table1? Likewise for table2, index on (ACN_NBR, PR_ID)?

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