CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10g
flower
flower

Reputation: 2242

Is it the best way to optimize this SQL statement when using not in in sql query?

The table called TB_ORDER have 90 million records of data,but only have 500 records which STATE is neither B nor C.

SELECT 
    ORDER.ID,ORDER.STATE,ORDER.NAME 
FROM 
    TB_ORDER ORDER 
WHERE 
    ORDER.STATE NOT IN ('B','C') ;

My workmate writes the sql like this and it cost about 7 minites because of full table scan.So I try to change it like this.Its that OK?I have add index on state field. Is it still full table scan because the subquery sql result is very large((90000000-500)/90000000)?

SELECT 
    A.ID,A.NAME,A.STATE 
FROM TB_ORDER A 
WHERE 
    NOT EXISTS
        (
        SELECT 1 FROM TB_ORDER B WHERE A.ID=B.ID and B.STATE='B'
        UNION ALL
        SELECT 1 FROM TB_ORDER C WHERE A.ID=C.ID and C.STATE='C'
        )

Upvotes: 0

Views: 87

Answers (2)

Koen Lostrie
Koen Lostrie

Reputation: 18685

Do you really need the NOT IN ? You could work around that by using a function and then creating a function based index. Make sure you where clause matches the predicate exactly. Example:

-- table
create table t_large_table (id NUMBER GENERATED ALWAYS AS IDENTITY,state VARCHAR2(1));
-- some sample data
DECLARE
BEGIN
  FOR i IN 1 .. 10 LOOP
    INSERT INTO t_large_table (state) VALUES ('A');
    INSERT INTO t_large_table (state) VALUES ('B');
  END LOOP;
  INSERT INTO t_large_table (state) VALUES ('C');
  INSERT INTO t_large_table (state) VALUES ('D');
  COMMIT;
END;
/

-- create index with function that has a bucket to put all states that are relevant to me. In this case everything that is not A or B
CREATE INDEX t_large_table_idx 
  ON t_large_table (CASE state WHEN 'A' THEN 'A' WHEN 'B' THEN 'B' ELSE 'X' END);

-- run a select with exactly same function as the index
SELECT * 
  FROM t_large_table
 WHERE CASE state WHEN 'A' THEN 'A' WHEN 'B' THEN 'B' ELSE 'X' END = 'X';

-- check explain plan
-----------------------------------------------------------------
| Id  | Operation                           | Name              |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_LARGE_TABLE     |
|   2 |   INDEX RANGE SCAN                  | T_LARGE_TABLE_IDX |
-----------------------------------------------------------------

Upvotes: 1

Srinika Pinnaduwage
Srinika Pinnaduwage

Reputation: 1042

I'll make a suggestion, you may try.

Select o.ID,o.o,o.NAME 
FROM TB_ORDER o
Inner Join
(
 Select STATE  from
 (
  Select STATE from TB_ORDER Group by ORDER 
 ) Q Where STATE NOT IN ('B','C') 
) QQ on QQ.STATE = o.STATE

Upvotes: 0

Related Questions