|
|
|
Supreme Being
Group: Forum Members
Last Login: Today @ 12:27:09 PM
Posts: 120,
Visits: 71
|
|
Hi,
I have a query which uses CASE on the a indexed column,
the bad part is it ignores the index ,
is there any way I can force index on it
Thanks
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Today @ 4:54:41 PM
Posts: 534,
Visits: 285
|
|
Any calculation based on an indexed column will not use any index, you have to rewrite it as a SARGable condition:
e.g. "indexed_col + x = y" -> "indexed_col = y - x"
Could you post that CASE?
Dieter
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Today @ 12:27:09 PM
Posts: 120,
Visits: 71
|
|
here is the sql
Select
CASE
WHEN SUBSTR(TRN_NM,13,1) = ' '
THEN TRN_NM
ELSE SUBSTR(TRN_NM,1,6) ||
'******' ||
SUBSTR(TRN_NM,13,4)
END TRN_NM
From TRNS_TBL
Where TRN_NM = '0066563438'
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Today @ 4:54:41 PM
Posts: 534,
Visits: 285
|
|
You can't use an index for your case statement, because it can't be rewritten and i doubt, that any DBMS will use an index for that.
Anyway, your example should use an index, because renaming a calculated column to the original columnname is bad practice:
"Where TRN_NM = '0066563438'" uses the base column TRN_NM, but not the result of the CASE
SELECT 'blabla' AS databasename
FROM dbc.databases
WHERE databasename = 'dbc';
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
databasename
------------
blabla
Dieter
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Today @ 12:27:09 PM
Posts: 120,
Visits: 71
|
|
Thanks for reply ,
it is now using index , but if I cover it up with another select layer then it doesn't e.g.
Select * from (
Select
CASE
WHEN SUBSTR(TRN_NM,13,1) = ' '
THEN TRN_NM
ELSE SUBSTR(TRN_NM,1,6) ||
'******' ||
SUBSTR(TRN_NM,13,4)
END TRN_NM1
From TRNS_TBL
)
Where TRN_NM1 = '0066563438'
can you suggest something ,
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Today @ 4:54:41 PM
Posts: 534,
Visits: 285
|
|
No DBMS i'm aware of will be able to use an index for that case statement, sorry.
Dieter
|
|
|
|