I have a problem which is related to query performance.
First when I am trying to run this query on oracle table that contains over 13 million records
Select *
from Table1
where CATSEARCH(NAME,'%ABCD% | %DCBA%', null) > 0
the above query taking 0.002 seconds to get 36000 rows
also
Select *
from Table1
where ID = 123 //less than one second - one row result
But when I am trying to add multi filter using AND such as the below statement
Select *
from Table1
where CATSEARCH(NAME,'%ABCD% | %DCBA%', null) > 0
AND ID = 123
This took more than 10 seconds to retrieve the result of one row.
Second the same problem when using OR
Select *
from Table1
where CATSEARCH(NAME,'%ABCD% | %DCBA%', null) > 0
OR ID = 123 //10 to 14 seconds
Table Structure
CREATE TABLE "SDE"."LABOUR_OFFICE"
( "OBJECTID" NUMBER(*,0) NOT NULL ENABLE,
"LAB_ID" NUMBER(10,0),
"FAC_SEQ" NVARCHAR2(255),
"LAB_NAME" NVARCHAR2(40),
"FAC_NAME" NVARCHAR2(60),
"ZAQAT_ID" NVARCHAR2(14),
"ACTIVITY" NVARCHAR2(180),
"FAC_STATUS" NVARCHAR2(255),
"COMM_REG" NVARCHAR2(12),
"COMM_ISSUE_LOC" NVARCHAR2(50),
"MUNI_LIC_ID" NVARCHAR2(12),
"MUNI_LIC_LOC" NVARCHAR2(50),
"ADD_CITY" NVARCHAR2(100),
"ADD_DISTRICT" NVARCHAR2(30),
"ADD_STREET" NVARCHAR2(100),
"ADD_POBOX" NVARCHAR2(7),
"ADD_POST_ID" NVARCHAR2(7),
"ADD_TEL1" NVARCHAR2(9),
"ADD_TEL2" NVARCHAR2(9),
"ADD_FAX" NVARCHAR2(9),
"ADD_EMAIL" NVARCHAR2(50),
"MANAGER_NAME" NVARCHAR2(50),
"FAC_SCOPE" NVARCHAR2(20),
"FAC_SIZE" NVARCHAR2(20),
"FAC_ACTIVITY" NVARCHAR2(200),
"SADUI_NUMBERS" NUMBER(10,0),
"FOR_NUMBERS" NUMBER(10,0),
"FAC_ISTEQDAM_ID" NVARCHAR2(10),
"FAC_NATIONALITY" NVARCHAR2(50),
"OWNER_NAME" NVARCHAR2(100),
"OWNER_NATIONALITY" NVARCHAR2(50),
"OWNER_ID" NVARCHAR2(15),
"OWNER_GENDER" NVARCHAR2(50),
"OWNER_ADD_CITY" NVARCHAR2(100),
"OWNER_ADD_DISTRICT" NVARCHAR2(30),
"OWNER_ADD_STREET" NVARCHAR2(30),
"OWNER_ADD_PO" NVARCHAR2(7),
"OWNER_ADD_POSTID" NVARCHAR2(7),
"OWNER_ADD_TEL1" NVARCHAR2(9),
"OWNER_ADD_TEL2" NVARCHAR2(9),
"OWNER_ADD_FAX" NVARCHAR2(15),
"OWNER_ADD_EMAIL" NVARCHAR2(100),
"OWNER_MOBILE" NVARCHAR2(50),
"IQAMA_ID" NVARCHAR2(150),
"FIRST_NAME" NVARCHAR2(50),
"FATHER_NAME" NVARCHAR2(50),
"GRAND_NAME" NVARCHAR2(50),
"FAMILY_NAME" NVARCHAR2(50),
"EMPLOYEE_NAT" NVARCHAR2(50),
"EMPLOYEE_RELG" NVARCHAR2(50),
"EMPLOYEE_PASSPORT_ID" NVARCHAR2(30),
"EMPLOYEE_GENDER" NVARCHAR2(255),
"EMPLOYEE_BIRTH_YEAR" NUMBER(10,0),
"EMPLOYEE_OCC" NVARCHAR2(100),
"EMPLOYEE_STATUS" NVARCHAR2(255),
"EMPLOYEE_BORDER_NUMBE" NVARCHAR2(50),
"NAME" NVARCHAR2(255),
"NAME2" NVARCHAR2(255),
"NAME3" NVARCHAR2(255),
"ACTIVITY2" VARCHAR2(250 CHAR),
"NAME3_2" VARCHAR2(250 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 0 PCTUSED 40 INITRANS 4 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "GIS_SDE_DATA" ;
CREATE INDEX "SDE"."INDX_ACTCNT2" ON "SDE"."LABOUR_OFFICE" ("ACTIVITY2")
INDEXTYPE IS "CTXSYS"."CTXCAT" ;
CREATE INDEX "SDE"."INDX_NAME3_2" ON "SDE"."LABOUR_OFFICE" ("NAME3_2")
INDEXTYPE IS "CTXSYS"."CTXCAT" ;
CREATE UNIQUE INDEX "SDE"."R509_SDE_ROWID_UK" ON "SDE"."LABOUR_OFFICE" ("OBJECTID")
PCTFREE 0 INITRANS 4 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "GIS_SDE_INDEXES" ;
CREATE INDEX "SDE"."INDX_NAME3" ON "SDE"."LABOUR_OFFICE" ("NAME3")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "GIS_SDE_DATA" ;
CREATE INDEX "SDE"."INDX_ACT" ON "SDE"."LABOUR_OFFICE" ("ACTIVITY")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "GIS_SDE_DATA" ;
CREATE INDEX "SDE"."INDX_IQAMA_ID" ON "SDE"."LABOUR_OFFICE" ("IQAMA_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "GIS_SDE_DATA" ;
CREATE OR REPLACE TRIGGER "SDE"."DR$INDX_ACTCNT2TC" after insert or update on "SDE"."LABOUR_OFFICE" for each row declare reindex boolean := FALSE; updop boolean := FALSE; begin ctxsys.drvdml.c_updtab.delete; ctxsys.drvdml.c_numtab.delete; ctxsys.drvdml.c_vctab.delete; ctxsys.drvdml.c_rowid := :new.rowid; if (inserting or updating('ACTIVITY2') or :new."ACTIVITY2" <> :old."ACTIVITY2") then reindex := TRUE; updop := (not inserting); ctxsys.drvdml.c_text_vc2 := :new."ACTIVITY2"; end if; ctxsys.drvdml.ctxcat_dml('SDE','INDX_ACTCNT2', reindex, updop); end;
/
ALTER TRIGGER "SDE"."DR$INDX_ACTCNT2TC" ENABLE;
BEGIN
DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY('"SDE"','"DR$INDX_ACTCNT2TC"',FALSE) ;
END;
/
CREATE OR REPLACE TRIGGER "SDE"."DR$INDX_NAME3_2TC" after insert or update on "SDE"."LABOUR_OFFICE" for each row declare reindex boolean := FALSE; updop boolean := FALSE; begin ctxsys.drvdml.c_updtab.delete; ctxsys.drvdml.c_numtab.delete; ctxsys.drvdml.c_vctab.delete; ctxsys.drvdml.c_rowid := :new.rowid; if (inserting or updating('NAME3_2') or :new."NAME3_2" <> :old."NAME3_2") then reindex := TRUE; updop := (not inserting); ctxsys.drvdml.c_text_vc2 := :new."NAME3_2"; end if; ctxsys.drvdml.ctxcat_dml('SDE','INDX_NAME3_2', reindex, updop); end;
/
ALTER TRIGGER "SDE"."DR$INDX_NAME3_2TC" ENABLE;
BEGIN
DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY('"SDE"','"DR$INDX_NAME3_2TC"',FALSE) ;
END;
/
Execuation Plan FOR
Select *
from Table1
where CATSEARCH(NAME,'%ABCD% | %DCBA%', null) > 0
AND ID = 123
Plan hash value: 2958045980
--------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID | LABOUR_OFFICE |
| 2 | BITMAP CONVERSION TO ROWIDS | |
| 3 | BITMAP AND | |
| 4 | BITMAP CONVERSION FROM ROWIDS| |
| 5 | INDEX RANGE SCAN | R509_SDE_ROWID_UK |
| 6 | BITMAP CONVERSION FROM ROWIDS| |
| 7 | SORT ORDER BY | |
| 8 | DOMAIN INDEX | INDX_NAME3_2 |
--------------------------------------------------------------