This content has been marked as final. Show 9 replies
if i use 'is null' then still query using Index
Not with a b-tree index it isn't - those indexes don't store NULL values.
And if the 665720 values are all set to the string 'Other' Oracle won't use the index either. If it is, which I doubt, it is because the statistics for the table and index are not current.
A bitmap index would get used in either case if all you are doing is COUNT(c) since NULLs are indexed and the index would be faster than a full table scan.
Post the actual queries you are using.
T967778.PER_NAME_MONTH as c1,t967778.month_name,
count( T778770.ACCT_AR_ID ) as c2,
T967778.ROW_WID as c3
sch.Account T778394 ,
sch.FACT T778770 ,
where T778394.ACCT_AR_ID = T778770.ACCT_AR_ID
and T778770.MSRMNT_PRD_MNTH = T967778.ROW_WID
and T778770.ART_PRTFOL_GRP_ID = T968444.CL_ID
and T778770.ART_ACTIVE_FLAG = 'Y'
and (<b>T778394.SRC_BANK_CODE</b>) ='Other' -- 'ABC'--'Other'
and case when T968444.CL_NAME = 'MSP' then 'Construction'
when T968444.CL_NAME = 'HLS' then 'Non-Prime'
when T968444.CL_NAME in ( 'MSP1', 'SPS') then 'Mortgage'
when T968444.CL_NAME in ('FDR', 'ILS/ACLS')
then 'BHE' else T968444.CL_NAME end = 'Mortgage' and T778770.MSRMNT_PRD_MNTH is not null
group by T967778.ROW_WID, T967778.PER_NAME_MONTH, t967778.month_name
order by c3
This is The sql i am using, ( table names are modified) I ran statistics for all the tables involved with cascade = 'TRUE'
again, If I am using ABC result is in 3 / 4seconds, If I use Other or Is null it is going for around 540 secs. ( sometimes i can not even wait to see results )
following is the result for SQL
FROM sch.account T778394
GROUP BY src_bank_code;
it is inefficient to use index to access table for SRC_BANK_CODE = 'OTHER'
13:03:36 SQL> select 20308/665720 from dual; 20308/665720 ------------ .030505318 13:04:48 SQL>
may I know the reason for Inefficiency?
You need to decide what question you want help with. Your original post said your query was using an index when you were trying to select NULL values and also when you tried to select values of 'Other' which, as sb just showed you, comprise 97% of the records.
If you want help with your original question then post the information I ask for: the query that queries NULL values, the corresponding explain plan showing the b-tree index is being used in the query and the DDL that shows the indexes that exist and the columns that make them up.
Maybe: (extreme) predicate pushing sometimes turns out useful
select T967778.PER_NAME_MONTH as c1, t967778.month_name, count( T778770.ACCT_AR_ID ) as c2, T967778.ROW_WID as c3 from (select ACCT_AR_ID from sch.Account where T778394.SRC_BANK_CODE = 'Other' /* 'ABC' or 'Other' */ ) T778394 , (select ACCT_AR_ID,ART_PRTFOL_GRP_ID,MSRMNT_PRD_MNTH from sch.FACT where ART_ACTIVE_FLAG = 'Y' and MSRMNT_PRD_MNTH is not null ) T778770 , (select CL_ID from sch.Group where CL_NAME in ('MSP1','SPS') /* 'Mortgage' */ ) T968444 , sch.Date T967778 where T778394.ACCT_AR_ID = T778770.ACCT_AR_ID and T778770.MSRMNT_PRD_MNTH = T967778.ROW_WID and T778770.ART_PRTFOL_GRP_ID = T968444.CL_ID group by T967778.ROW_WID, T967778.PER_NAME_MONTH, t967778.month_name order by c3