oracle查询符合多个条件吗,多条件Oracle查询

我有一个与查询性能有关的问题 .

首先，当我尝试在包含超过1300万条记录的oracle表上运行此查询时

Select *

from Table1

where CATSEARCH(NAME,'%ABCD% | %DCBA%', null) > 0

以上查询需要0.002秒才能获得36000行

也

Select *

from Table1

where ID = 123 //less than one second - one row result

但是当我尝试使用AND添加多个过滤器时，如下面的语句

Select *

from Table1

where CATSEARCH(NAME,'%ABCD% | %DCBA%', null) > 0

AND ID = 123

这花费了超过10秒来检索一行的结果 .

使用OR时，第二个问题相同

Select *

from Table1

where CATSEARCH(NAME,'%ABCD% | %DCBA%', null) > 0

OR ID = 123 //10 to 14 seconds

表结构

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;

/

执行计划FOR

从Table1中选择*，其中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 |

--------------------------------------------------------------