Re: Deigning suggestion for table and indexes

  • From: yudhi s <learnerdatabase99@xxxxxxxxx>
  • To: "Mark W. Farnham" <mwf@xxxxxxxx>, Andy Sayer <andysayer@xxxxxxxxx>
  • Date: Mon, 12 Feb 2024 10:56:08 +0530

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)?

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 02-2024