Oracle sql query plan does not uses indices with or statement and a subquery

Question

I have a query with 'or' statement and 'inner select' statement. When I check query plan it uses full table access and I don't know why this happens.

I created this test case to show my problem:

CREATE TABLE PERSON (
    ID                   NUMBER(18)           NOT NULL,
    NAME                 VARCHAR2(18)         NOT NULL,
    SURNAME              VARCHAR2(18)         NOT NULL
);

BEGIN
  FOR b IN 1..500000
  LOOP
     INSERT INTO PERSON VALUES (b,'name' || to_char(b),'surname' || to_char(b));
  END LOOP;
END;

CREATE INDEX PERSON_NAME_index ON PERSON(NAME);
CREATE INDEX PERSON_SURNAME_index ON PERSON(SURNAME);

And here is the problematic query:

SELECT p.*
FROM PERSON p
WHERE p.NAME = 'name300' or  p.SURNAME in (SELECT p2.SURNAME
                                       FROM PERSON p2
                                       WHERE p2.NAME = 'name500');

When I analyze the query plan, I saw that it uses full table access for person table. That slows my query time drastically and I dont know why this happens.

If I eliminate first constraint(p.NAME = 'name300') and query with only subselect statement, everthing works fine and query uses indices again.

Can someone explain me why query is not using my indices for the first case?

Classic issue. Because of your OR the query plan doesn't manage to use the indexes and falls into a full table scan. Change your OR into an UNION like Gordon proposed below. Whenever you code an OR, you should ALWAYS ask yourself if it can be replaced with a UNION. — Thomas G, Jan 02 '19 at 13:24

score 1 · Accepted Answer · answered Jan 02 '19 at 11:52

1

OR is difficult for databases to optimize. You can split this into two queries and use UNION ALL:

SELECT p.*
FROM PERSON p
WHERE p.NAME = 'name300'
UNION ALL
SELECT p.*
FROM PERSON p
WHERE p.NAME <> 'name300' AND
      p.SURNAME IN (SELECT p2.SURNAME
                    FROM PERSON p2
                    WHERE p2.NAME = 'name500'
                   );

answered Jan 02 '19 at 11:52

Gordon Linoff

1,242,037
58
646
786

One thought though :) why ALL in this case? The orginal query would not have returned dups. – Thomas G Jan 02 '19 at 13:26
1

@ThomasG . . . There is no reason to incur the overhead of removing duplicates, which would be the case if `UNION` were used. This query doesn't return duplicates either, even if both conditions are true. – Gordon Linoff Jan 02 '19 at 13:50
Can you give me more specific information about "or" optimization. A documentation would be great. Also, even oracle can translate query like you did(since its a simple logic transformation), i don't understand why it's not doing this. – Alperen Üretmen Jan 02 '19 at 13:59
1

@AlperenÜretmen . . . Query optimization is a very complex subject. You should start with the documentation: https://docs.oracle.com/cd/E25178_01/server.1111/e16638/optimops.htm. – Gordon Linoff Jan 02 '19 at 14:13

Oracle sql query plan does not uses indices with or statement and a subquery

1 Answers1