Skip to Main Content

Using Multi Condition in the same query - Performance Issue

Ashraf ELHakimDec 20 2016 — edited Dec 21 2016

Hello,

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 multifilter 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;
/

Any help

Thank you

Comments
Post Details
Added on Dec 20 2016
25 comments
735 views