Hallo,
ich bin sql-neuling und versuche gerade mit php eine sql-Abfrage umzusetzen, die in einer bestimmten Spalte einer bestimmten Tabelle nach einem bestimmten Begriff sucht:
SELECT * FROM urteile WHERE titel LIKE ‚%grund%‘
das funkt auch schon ganz gut.
Jetzt meine Frage:
Wie muss der String aussehen, wenn ich nach zwei oder mehr Begriffen die über OR verknüpft werden sollen?
Kann ich die einfach hinten dran hängen?:
SELECT * FROM urteile WHERE titel LIKE ‚%grund%‘ OR ‚%los%‘
(funkt bei mir irgendwie nicht!) oder muss ich den gesamten String wiederholen? Oder sonst irgendwie anders? Oder geht das so vielleicht gar nicht?
stimmt nicht ganz. zumindest im oracle macht der optimizer aus einem select mit mind. einem „or“ implizit eine union. scheint also einfacher für die db abzuhandeln zu sein. bei anderen db-systemen kenne ich mich nicht so gut aus, ich vermute aber, die reagieren ähnlich.
stimmt nicht ganz. zumindest im oracle macht der optimizer aus
einem select mit mind. einem „or“ implizit eine union.
Sorry, aber das stimmt nicht!
SQL\> @or\_vs\_union.sql
SQL\> SET ECHO ON;
SQL\> DROP TABLE or\_test;
Table dropped.
SQL\> CREATE TABLE or\_test AS
2 SELECT DISTINCT object\_id, object\_name, object\_type, status
3 FROM dba\_objects
4 WHERE object\_id IS NOT NULL
5 AND owner='SYS'
6 AND object\_type not like '%PARTITION%';
Table created.
SQL\> ALTER TABLE or\_test ADD CONSTRAINT pk\_or\_test PRIMARY KEY (object\_id);
Table altered.
SQL\> CREATE UNIQUE INDEX uk\_or\_test ON or\_test(object\_name, object\_type);
Index created.
SQL\> CREATE INDEX idx1\_or\_test ON or\_test(object\_type);
Index created.
SQL\> EXEC dbms\_stats.gather\_table\_stats('scott','or\_test');
PL/SQL procedure successfully completed.
SQL\> DELETE FROM plan\_table;
48 rows deleted.
SQL\> EXPLAIN PLAN SET statement\_id='1\_OR BY PK' FOR
2 SELECT \* FROM or\_test WHERE object\_id=12345 OR object\_id=4711;
Explained.
SQL\> EXPLAIN PLAN SET statement\_id='1\_UNION BY PK' FOR
2 SELECT \* FROM or\_test WHERE object\_id=12345 UNION
3 SELECT \* FROM or\_test WHERE object\_id=4711;
Explained.
SQL\> EXPLAIN PLAN SET statement\_id='2\_OR BY UK' FOR
2 SELECT \* FROM or\_test
3 WHERE (object\_name LIKE 'ABC%' OR object\_name LIKE 'XYZ%') AND
4 object\_type='PACKAGE';
Explained.
SQL\> EXPLAIN PLAN SET statement\_id='2\_UNION BY UK' FOR
2 SELECT \* FROM or\_test WHERE object\_name like 'ABC%' AND object\_type='PACKAGE' UNION
3 SELECT \* FROM or\_test WHERE object\_name LIKE 'XYZ%' AND object\_type='PACKAGE';
Explained.
SQL\> EXPLAIN PLAN SET statement\_id='3\_OR BY IDX' FOR
2 SELECT \* FROM or\_test WHERE object\_type LIKE 'PACK%' OR object\_type LIKE 'V%';
Explained.
SQL\> EXPLAIN PLAN SET statement\_id='3\_UNION BY IDX' FOR
2 SELECT \* FROM or\_test WHERE object\_type LIKE 'PACK%' UNION
3 SELECT \* FROM or\_test WHERE object\_type LIKE 'V%';
Explained.
SQL\> EXPLAIN PLAN SET statement\_id='4\_OR FULL SCAN' FOR
2 SELECT \* FROM or\_test WHERE status LIKE 'I%' OR status LIKE 'X%';
Explained.
SQL\> EXPLAIN PLAN SET statement\_id='4\_UNION FULL SCAN' FOR
2 SELECT \* FROM or\_test WHERE status LIKE 'I%' UNION
3 SELECT \* FROM or\_test where status LIKE 'X%';
Explained.
SQL\> COLUMN statement\_id FORMAT A18
SQL\> COLUMN operation FORMAT A24 TRUNCATE
SQL\> COLUMN options FORMAT A14
SQL\> COLUMN object\_name FORMAT A14
SQL\> SET NUM 3
SQL\> SET PAGESIZE 20
SQL\> BREAK ON statement\_id SKIP PAGE
SQL\> SELECT statement\_id, lpad(' ',depth\*3)||operation AS operation, options, object\_name, cost
2 FROM plan\_table
3 ORDER BY statement\_id,id;
STATEMENT\_ID OPERATION OPTIONS OBJECT\_NAME COST
------------------ ------------------------ -------------- -------------- -----
1\_OR BY PK SELECT STATEMENT 1
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID OR\_TEST 1
INDEX RANGE SCAN PK\_OR\_TEST 1
STATEMENT\_ID OPERATION OPTIONS OBJECT\_NAME COST
------------------ ------------------------ -------------- -------------- -----
1\_UNION BY PK SELECT STATEMENT 4
SORT UNIQUE 4
UNION-ALL
TABLE ACCESS BY INDEX ROWID OR\_TEST 1
INDEX UNIQUE SCAN PK\_OR\_TEST 1
TABLE ACCESS BY INDEX ROWID OR\_TEST 1
INDEX UNIQUE SCAN PK\_OR\_TEST 1
STATEMENT\_ID OPERATION OPTIONS OBJECT\_NAME COST
------------------ ------------------------ -------------- -------------- -----
2\_OR BY UK SELECT STATEMENT 2
CONCATENATION
TABLE ACCESS BY INDEX ROWID OR\_TEST 1
INDEX RANGE SCAN UK\_OR\_TEST 1
TABLE ACCESS BY INDEX ROWID OR\_TEST 1
INDEX RANGE SCAN UK\_OR\_TEST 1
STATEMENT\_ID OPERATION OPTIONS OBJECT\_NAME COST
------------------ ------------------------ -------------- -------------- -----
2\_UNION BY UK SELECT STATEMENT 4
SORT UNIQUE 4
UNION-ALL
TABLE ACCESS BY INDEX ROWID OR\_TEST 1
INDEX RANGE SCAN UK\_OR\_TEST 1
TABLE ACCESS BY INDEX ROWID OR\_TEST 1
INDEX RANGE SCAN UK\_OR\_TEST 1
STATEMENT\_ID OPERATION OPTIONS OBJECT\_NAME COST
------------------ ------------------------ -------------- -------------- -----
3\_OR BY IDX SELECT STATEMENT 19
TABLE ACCESS BY INDEX ROWID OR\_TEST 19
BITMAP CONVERSION TO ROWIDS
BITMAP OR
BITMAP CONVE FROM ROWIDS
SORT ORDER BY
INDEX RANGE SCAN IDX1\_OR\_TEST 2
BITMAP CONVE FROM ROWIDS
SORT ORDER BY
INDEX RANGE SCAN IDX1\_OR\_TEST 1
STATEMENT\_ID OPERATION OPTIONS OBJECT\_NAME COST
------------------ ------------------------ -------------- -------------- -----
3\_UNION BY IDX SELECT STATEMENT 24
SORT UNIQUE 24
UNION-ALL
TABLE ACCESS BY INDEX ROWID OR\_TEST 8
INDEX RANGE SCAN IDX1\_OR\_TEST 1
TABLE ACCESS BY INDEX ROWID OR\_TEST 14
INDEX RANGE SCAN IDX1\_OR\_TEST 2
STATEMENT\_ID OPERATION OPTIONS OBJECT\_NAME COST
------------------ ------------------------ -------------- -------------- -----
4\_OR FULL SCAN SELECT STATEMENT 49
TABLE ACCESS FULL OR\_TEST 49
STATEMENT\_ID OPERATION OPTIONS OBJECT\_NAME COST
------------------ ------------------------ -------------- -------------- -----
4\_UNION FULL SCAN SELECT STATEMENT 612
SORT UNIQUE 612
UNION-ALL
TABLE ACCESS FULL OR\_TEST 49
TABLE ACCESS FULL OR\_TEST 49
48 rows selected.
SQL\> spool off
