JOIN с временной таблицей

#sql #oracle #оптимизация_запросов

Есть временная таблица

CREATE GLOBAL TEMPORARY TABLE "TMP_CONTROL_POINT_DETECT" (
    "POINT_ID" NUMBER NOT NULL ENABLE, 
     CONSTRAINT "TMP_CONTROL_POINT_DETECT_PK" PRIMARY KEY ("POINT_ID") ENABLE
) ON COMMIT PRESERVE ROWS ;


И есть persistent таблица

CREATE TABLE "CONTROL_POINTS_" (
    "ID" NUMBER, 
    "STATUS" NUMBER(1,0), 
     CONSTRAINT "PK_CONTROL_POINTS_" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE "USERS"  ENABLE 
);


Во временной таблице 200 записей, в персистентной 29 000

Делаю запрос

SELECT
  cp.status
FROM
  TMP_CONTROL_POINT_DETECT det
  JOIN CONTROL_POINTS_ cp ON (
    det.POINT_ID = cp.ID
  )


и ужасаюсь плану

-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name                     | Rows  | Bytes | Cost (%CPU)|
Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                          |   200 |  3800 |    18   (6)|
00:00:01 |
|*  1 |  HASH JOIN         |                          |   200 |  3800 |    18   (6)|
00:00:01 |
|   2 |   TABLE ACCESS FULL| TMP_CONTROL_POINT_DETECT |   200 |  2600 |     2   (0)|
00:00:01 |
|   3 |   TABLE ACCESS FULL| CONTROL_POINTS_          | 29303 |   171K|    16   (7)|
00:00:01 |
-----------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DET"."POINT_ID"="CP"."ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan


Потом меняю в запросе выбираемое поле

SELECT
  cp.id
FROM
  TMP_CONTROL_POINT_DETECT det
  JOIN CONTROL_POINTS_ cp ON (
    det.POINT_ID = cp.ID
  )


И получаю ожидаемый план

-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name                     | Rows  | Bytes | Cost (%CPU)|
Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                          |   200 |  3600 |     2   (0)|
00:00:01 |
|   1 |  NESTED LOOPS      |                          |   200 |  3600 |     2   (0)|
00:00:01 |
|   2 |   TABLE ACCESS FULL| TMP_CONTROL_POINT_DETECT |   200 |  2600 |     2   (0)|
00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_CONTROL_POINTS_       |     1 |     5 |     0   (0)|
00:00:01 |
-----------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DET"."POINT_ID"="CP"."ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Кто нибудь может объяснить, что происходит? Откуда берется FULL SCAN? Сам запрос
выбирает честные 200 записей

Манипуляции типа

SELECT
  cp.STATUS
FROM
  CONTROL_POINTS_ cp
WHERE
  cp.ID IN (SELECT ID FROM TMP_CONTROL_POINT_DETECT det)


приводят к еще более удручающим последствиям

-------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                     | Rows  | Bytes | Cost (%CPU)|
Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                          |  5860K|    33M|  2820 
 (4)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|                          |  5860K|    33M|  2820 
 (4)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | TMP_CONTROL_POINT_DETECT |   200 |       |     2 
 (0)| 00:00:01 |
|   3 |   BUFFER SORT        |                          | 29303 |   171K|  2818 
 (4)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | CONTROL_POINTS_          | 29303 |   171K|    14 
 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------


Update

Временная таблица ни при чем. С персистентной такой же структуры и такими же данными
та же картина
    

Ответы

Ответ 1

Оба плана абсолютно предсказуемы. во втором случае вам нужно только значение из индекса,
поэтому берется индекс.

А в первом случае нужно получать значение из области данных. При этом данных у вас
29000 записей, при средней длине записи около 4х байт. На диске вся таблица занимает
171 Кбайт (что видно в плане). При размере блока на диске в 4к это 43 блока. Получать
43 блока за 200 отдельных обращений по указателям из индекса мягко говоря накладно
(Гарантировано будет прочитана вся таблица, причем каждый блок придется разбирать 4
раза). Full scan да еще и с hash join более чем оправдан.

Использование индекса эффективно, когда с его помощью нужно обратиться не более чем
к 10% всех блоков таблицы.


Ответ 2

Использование хинта index привел запрос в чувство

SELECT  /*+ index(cp PK_CONTROL_POINTS) */
  cp.STATUS
FROM
  TMP_CONTROL_POINT_DETECT det
  JOIN CONTROL_POINTS PARTITION (lic) cp ON (
    det.POINT_ID = cp.ID
  )




--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                     | Rows  |
Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                          |   200 |
 4000 |   202   (0)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                       |                          |   200 |
 4000 |   202   (0)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                      |                          |   200 |
 4000 |   202   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS FULL                | TMP_CONTROL_POINT_DETECT |   200 |
 2600 |     2   (0)| 00:00:01 |       |       |
|*  4 |    INDEX UNIQUE SCAN                | PK_CONTROL_POINTS        |     1 |
      |     0   (0)| 00:00:01 |       |       |
|   5 |   TABLE ACCESS BY GLOBAL INDEX ROWID| CONTROL_POINTS           |     1 |
    7 |     1   (0)| 00:00:01 |     2 |     2 |

PLAN_TABLE_OUTPUT
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("DET"."POINT_ID"="CP"."ID")
       filter(TBL$OR$IDX$PART$NUM("CONTROL_POINTS",0,1,0,ROWID)=2)