Hi Lok,
I have to say that I lost track of which columns are how selective.
So what I would do is the classical bitmap use, which are single column Bitmap
which can be combined.
Maybe nót the best way, but the most flexible.
For all column where <> and not null is used, you should create a bitmap index.
Thus we have one bitmap on NVL (I.MA_FLG, 'N') and one on NVL (I.D_UNMTCH,
'N'). For not <> only bitmap would work.
For is null and is not null we can also make a single column bitmap index.
It all depends how often this table is inserted and how often the columns are
updated. How much is too much is hard to decide and quite often the truth will
show only at runtime. There is some risk on bimap in an OLTP, always.
For all the other columns you can create a combined B*Tree index.
There is an other option like creating a big combined B*Tree with the columns
compare with = in the leading positions.
You can use the other columns as filter . This is a low risk option
E.g. something like this (in that sequence) TRIM(I.M_TXT) , PT_Code, DC_CODE,
D_CUR_CODE, ED_AMT, PR_CTGRY, NVL (I.MA_FLG, 'N'), NVL (I.D_UNMTCH, 'N'),
WOF_DATE, PE, PT_MCODE.
How good this index is will show in the combined selectivity of the = compared
columns.
You can check this by :
select count(*) from (
select distinct TRIM(I.M_TXT) , PT_Code, DC_CODE, D_CUR_CODE, ED_AMT, PR_CTGRY
from PP_IN_TAB)
;
I the selectivity is not good enough, you can increase the selektivity by using
a transformation like Mark described it.
Regards
Lothar
----Ursprüngliche Nachricht----
Von : loknath.73@xxxxxxxxx
Datum : 25/08/2021 - 21:33 (MS)
An : l.flatz@xxxxxxxxxx
Cc : oracle-l@xxxxxxxxxxxxx
Betreff : Re: Fixing Performance issue with less selective columns
Thank you Lothar. Actually this is an OLTP kind of database and I do see
UPDATE queries on these table columns. Need to see in detail about the
frequency of those and how concurrent they are. I am Not very familiar with the
usage of bitmap indexes , so trying to understand what exact column you are
suggesting for the bitmap index to help this query?
On Wed, Aug 25, 2021 at 11:45 PM Lothar Flatz <
l.flatz@xxxxxxxxxx> wrote:
This scenario cries for bitmap indexes.
Bitmap Indexes can deal with "not equal" as well as "is null".
The columns seems to be low cardinality too.
The only open question is how often these columns get updated. (
https://asktom.oracle.com/pls/apex/asktom.search?tag=bitmap-indexes-and-locking).
Regards
Lothar
Am 25.08.2021 um 19:19 schrieb Mark W. Farnham:
unfortunately you keep nearly all the rows of both MA_FLG and D_UNMTCH, so this
query is the opposite of those indexes being useful.
IF you were looking for ‘Y’ instead of not ‘Y’ on either one it would be
extremely good. I didn’t see initially that these two columns are extremely
inclusive.
I think Sayan was checking that in his query request. MA_FLG could reject at
most about 6 million rows, so that’s pretty worthless.
From: Lok P [mailto:loknath.73@xxxxxxxxx] ;
Sent: Wednesday, August 25, 2021 1:09 PM
To: Mark W. Farnham
Cc: Sayan Malakshinov; Oracle L
Subject: Re: Fixing Performance issue with less selective columns
Thank You Mark.
I may be wrong but in this situation I was unable to think of any other way we
could make this query faster , so I was thinking of creating a new index. If
there exists any other way to make this query faster without creating any new
index that would really be helpful.
I am not able to get your point fully, If you can help me understand it a bit
more here please. Below is the data pattern for MA_FLG and D_UNMTCH.
Thus , in this query condition " NVL (I.MA_FLG, 'N') <> 'Y' results in
~105million and NVL (I.D_UNMTCH, 'N') <> 'Y' results in ~111million. So how
should I create index or modify code to make it the best access/filter criteria
so as to make the query faster?
MA_FLG
Count(*)
N
105228656
Y
6000938
643566
D_UNMTCH
Count(*)
Y
13715
111859445
On Wed, Aug 25, 2021 at 8:00 PM Mark W. Farnham <mwf@xxxxxxxx> wrote:
The other thing, for flag values like AND NVL (I.MA_FLG, 'N') <> 'Y' AND
NVL (I.D_UNMTCH, 'N') <> 'Y'
if you’re thinking about adding an index, and even if you need a virtual column
to do this because you have too much code depending on values ‘N’ and ‘Y’,
define the final status (the one where nearly all of them land) as NULL, being
the ones you are NOT interested in most of the time. In both these cases it
looks like ‘Y’ would then be NULL, so
i.ma_flg_v is defined decoding Y to NULL and anything else to N and your code
becomes and i.ma_flg_v = ‘N’ and you deal with variability in non-nulls that
are not ‘Y’ on the original,
or
i.ma_flg_v decodes Y to NULL, NULL to ‘N’ and anything else unchanged and your
code becomes i.ma_flg_v is NOT NULL,
or
you make a functional index on i.ma_flg that does the equivalent.
I can’t remember off the top of my head whether either way gives you a real
advantage over the other in stats collections and the CBO doing something smart
and that probably changed over the releases. That might be in one of my papers.
When you then index that column the nulls disappear, leaving you with a very
tiny index to prune your result set immediately to very small and you can
usually filter the rest fast without an index.
Remember, ORACLE cannot assign a value to NULL in anything they do. But YOU can.
When this is appropriate, it is one of the neatest and easiest “magic tricks”
in the Oracle kit.
Good luck,
mwf
From:oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Sayan Malakshinov
Sent: Wednesday, August 25, 2021 9:40 AM
To: Lok P
Cc: Oracle L
Subject: Re: Fixing Performance issue with less selective columns
Hi Lok,
SUBSTR(:B8,0.50)
