Hi all,
What is the best way to search partial text and ignore cases from a table
to get best performance?
Our backend code in *Java. *Our environment has RAM: 61 gib, cpu: 16
vcpus, core : 8.
I have tried to index on that field and I'm still getting results in 4
sec.
Query Is :
*SELECT OBJ.PROP_VALUE_STR1 as name FROM DFM.BRANCH B INNER JOIN DFM.OBJECT
OBJ ON 1=1 WHERE (OBJ.BRANCH_ID = B.ID <http://B.ID>) AND
((CAST(OBJ.PROP_VALUE_STR1 AS VARCHAR_IGNORECASE) LIKE 'qualify%') AND
((OBJ.ID <http://OBJ.ID> <> '6a7a51ce-3fc9-4b35-a845-2be95c8180a1') AND
((OBJ.REFERENCE_ID <> '6a7a51ce-3fc9-4b35-a845-2be95c8180a1') AND
((B.PROJECT_ID = 'cad78e78-a38b-4340-817b-d689b988ec17') AND
(OBJ.OBJECT_TYPE_ID =1)))))*
In above Query join two table in that *OBJECT* table has index on
BRANCH_ID and PROP_VALUE_STR1 and *BRANCH* table has index on
PROJECT_ID. And Object table has more than a million entry
