Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Использовать индекс  [new]
PaulWist
Member

Откуда:
Сообщений: 2300
Братья по разуму, в Оракле я NULL.

Дано:

-- Табличка
 CREATE TABLE "BAAN"."TTCIBD200711" 
   (	"T$ITEM" NVARCHAR2(47) NOT NULL ENABLE)
 
-- Индекс
 CREATE UNIQUE INDEX "BAAN"."TTCIBD200711$IDX1" ON "BAAN"."TTCIBD200711" (NLSSORT("T$ITEM",'nls_sort=''UCA0700_DUCET_S3_VN_BN_NY_EN_FN_HN_DN_MN''')) 


Ищу одну запись

SELECT 
T$ITEM from baan.ttcibd200711  where 
baan.ttcibd200711.t$item in ('бла-бла-бла')


В плане получаю
автор
TABLE ACCESS -- FULL.


Пытаюсь добавить индекс

SELECT 
/*+ INDEX(ttcibd200711 TTCIBD200711$IDX1)*/
T$ITEM, T$CWAR from baan.ttcibd200711  where 
baan.ttcibd200711.t$item in ('бла-бла-бла')


В плане получаю:
автор
TABLE ACCESS -- BY INDEX ROWID BATCHED
INDEX -- FULL SCAN


Вопрос: Как заставить оптимизатор использовать индекс SEEK, что нужно написать в предикате для данного индекса.
15 ноя 21, 11:25    [22396086]     Ответить | Цитировать Сообщить модератору
 Re: Использовать индекс  [new]
PuM256
Member

Откуда:
Сообщений: 168
Что-то вы явно не договариваете. В DDL у вас только колонка T$ITEM, а в запросе ещё есть T$CWAR.
Так что приведите полный DDL таблицы и полный запрос без всяких "бла-бла-бла". На мой взгляд, вы упустили какую-то деталь, которая вам кажется неважной, но при этом она имеет решающее значение.

А хинты в таких простых случаях лучше вообще не использовать.
15 ноя 21, 11:43    [22396094]     Ответить | Цитировать Сообщить модератору
 Re: Использовать индекс  [new]
Asmodeus
Member

Откуда:
Сообщений: 612
PaulWist,

А индекс именно так кто-то заставляет создавать?
15 ноя 21, 11:58    [22396113]     Ответить | Цитировать Сообщить модератору
 Re: Использовать индекс  [new]
Asmodeus
Member

Откуда:
Сообщений: 612
PaulWist,

Вы как-то так хотите?
+

SQLcl> create table hr.t$drop
  2  (
  3      t_id    VARCHAR2 (47) not null enable
  4  );

Table HR.T$DROP created.

SQLcl> create unique index hr.i_t$drop_tid
  2      on hr.t$drop
  3      (nlssort (t_id, 'nls_sort=''UCA0700_DUCET_S3_VN_BN_NY_EN_FN_HN_DN_MN'''));

Index HR.I_T$DROP_TID created.

SQLcl> explain plan for
  2  select t_id
  3    from hr.t$drop
  4   where t_id in (:c);

Explained.

SQLcl> select * from table(dbms_xplan.display);

                                                              PLAN_TABLE_OUTPUT
_______________________________________________________________________________
Plan hash value: 3632732064

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |    25 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T$DROP |     1 |    25 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("T_ID"=:C)

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

17 rows selected.

SQLcl> select * from nls_session_parameters;

                 PARAMETER                         VALUE
__________________________ _____________________________
...
NLS_SORT                   RUSSIAN
NLS_COMP                   BINARY
...

17 rows selected.

SQLcl> alter session set nls_sort="UCA0700_DUCET_S3_VN_BN_NY_EN_FN_HN_DN_MN";

Session altered.

SQLcl> alter session set nls_comp=linguistic;

Session altered.

SQLcl> explain plan for
  2  select t_id
  3    from hr.t$drop
  4*  where t_id in (:c);

SQLcl> select * from table(dbms_xplan.display);

                                                                              PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________
Plan hash value: 3730450835

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |   523 |     0   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T$DROP       |     1 |   523 |     0   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | I_T$DROP_TID |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access(NLSSORT("T_ID",'nls_sort=''UCA0700_DUCET_S3_VN_BN_NY_EN_FN_HN_DN_MN'''
              )=NLSSORT(:C,'nls_sort=''UCA0700_DUCET_S3_VN_BN_NY_EN_FN_HN_DN_MN'''))

15 rows selected.
15 ноя 21, 12:19    [22396126]     Ответить | Цитировать Сообщить модератору
 Re: Использовать индекс  [new]
PaulWist
Member

Откуда:
Сообщений: 2300
PuM256
Что-то вы явно не договариваете. В DDL у вас только колонка T$ITEM, а в запросе ещё есть T$CWAR.
Так что приведите полный DDL таблицы и полный запрос без всяких "бла-бла-бла". На мой взгляд, вы упустили какую-то деталь, которая вам кажется неважной, но при этом она имеет решающее значение.


2PuM256

Это не принципиально, пусть DDL будет такое:

  CREATE TABLE "BAAN"."TTCIBD200711" 
   (	"T$ITEM" NVARCHAR2(47) NOT NULL ENABLE, 
	"T$CWAR" NVARCHAR2(6) NOT NULL ENABLE)


PuM256

А хинты в таких простых случаях лучше вообще не использовать.


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

2Asmodeus

Такой индекс - это реальность на которую я не могу повлиять :(

Правильно ли я понимаю, что бы использовался "такой" индекс надо выполнить:

alter session set nls_sort="UCA0700_DUCET_S3_VN_BN_NY_EN_FN_HN_DN_MN";


те изменить настройки сессии???
15 ноя 21, 13:21    [22396170]     Ответить | Цитировать Сообщить модератору
 Re: Использовать индекс  [new]
Asmodeus
Member

Откуда:
Сообщений: 612
PaulWist,

nls_comp тоже надо поменять. Это можно делать через logon-триггер.

Сообщение было отредактировано: 15 ноя 21, 13:57
15 ноя 21, 13:53    [22396191]     Ответить | Цитировать Сообщить модератору
 Re: Использовать индекс  [new]
PaulWist
Member

Откуда:
Сообщений: 2300
Asmodeus,

ОК, спасибо!!! Получилось.
15 ноя 21, 14:51    [22396214]     Ответить | Цитировать Сообщить модератору
 Re: Использовать индекс  [new]
Правильный Вася
Member

Откуда:
Сообщений: 459
Если в таблице одна запись, как в примере, то оптимизатору без разницы, что сканировать - таблицу или индекс. А уж если в индексе нет нужных полей, выбираемых по SELECT, то уж тем более нет смысла его читать и потом читать таблицу - двойная работа.

Вообще же лучше почитать Кайта, например, для понимания механики и оптимизации создаваемых структур в БД.
15 ноя 21, 19:22    [22396375]     Ответить | Цитировать Сообщить модератору
 Re: Использовать индекс  [new]
witte
Member

Откуда:
Сообщений: 451
Правильный Вася
Если в таблице одна запись, как в примере, то оптимизатору без разницы, что сканировать - таблицу или индекс.

Точнее так: если таблица размером до 5 блоков включительно, ЕМНИП, то индекс не будет использоваться даже если он формально помогает найти строчку быстро. Таблица прочитается целиком, т.к. оптимизатор считает что так быстрее.
Правильный Вася
А уж если в индексе нет нужных полей, выбираемых по SELECT, то уж тем более нет смысла его читать и потом читать таблицу - двойная работа.

Это как? Если мне нужна строчка / немного строчек целиком из большой таблицы по индексу то Вы предполагаете что оптимизатор будет фуллсканить таблицу?
15 ноя 21, 19:55    [22396384]     Ответить | Цитировать Сообщить модератору
 Re: Использовать индекс  [new]
PaulWist
Member

Откуда:
Сообщений: 2300
witte

Правильный Вася
А уж если в индексе нет нужных полей, выбираемых по SELECT, то уж тем более нет смысла его читать и потом читать таблицу - двойная работа.

Это как? Если мне нужна строчка / немного строчек целиком из большой таблицы по индексу то Вы предполагаете что оптимизатор будет фуллсканить таблицу?


Вы, видимо, невнимательно прочли, те на таблице есть индекс по полю А, а предикат SELECT-а ищет по полю В, то в этом случае действительно оптимизатор вынужден сканировать таблицу целиком (не берём ситуацию когда данные умещаются на странице-блоке)

create index idxA on table (A)

select * from table where B = ...
15 ноя 21, 20:42    [22396398]     Ответить | Цитировать Сообщить модератору
 Re: Использовать индекс  [new]
witte
Member

Откуда:
Сообщений: 451
PaulWist
witte

пропущено...

Это как? Если мне нужна строчка / немного строчек целиком из большой таблицы по индексу то Вы предполагаете что оптимизатор будет фуллсканить таблицу?


Вы, видимо, невнимательно прочли, те на таблице есть индекс по полю А, а предикат SELECT-а ищет по полю В, то в этом случае действительно оптимизатор вынужден сканировать таблицу целиком (не берём ситуацию когда данные умещаются на странице-блоке)

create index idxA on table (A)

select * from table where B = ...

Да, верно, понял как select a, b, а не то что предикат по полю b.
15 ноя 21, 21:03    [22396400]     Ответить | Цитировать Сообщить модератору
 Re: Использовать индекс  [new]
Sayan Malakshinov
Member

Откуда: Мск
Сообщений: 5956
Правильный Вася
Если в таблице одна запись, как в примере, то оптимизатору без разницы, что сканировать - таблицу или индекс. А уж если в индексе нет нужных полей, выбираемых по SELECT, то уж тем более нет смысла его читать и потом читать таблицу - двойная работа.

witte
Точнее так: если таблица размером до 5 блоков включительно, ЕМНИП, то индекс не будет использоваться даже если он формально помогает найти строчку быстро. Таблица прочитается целиком, т.к. оптимизатор считает что так быстрее.

ну не правда же...
+ minitab.sql
set echo on;
create table t(a unique, b) as
select 1,1 from dual;

select blocks from user_tables where table_name='T';

alter session set MAX_DUMP_FILE_SIZE = unlimited;
alter session set tracefile_identifier='minitab_ius';
alter session set events '10053 trace name context forever, level 1';

alter session set tracefile_identifier='minitab_ius';
select * from t where a=1;
alter session set tracefile_identifier='minitab_fts';
select/*+ full(t) */ * from t where a=1;
alter session set tracefile_identifier='end';
alter session set events '10053 off';
set echo off;

+ plans
SQL_ID  g36j00khg170d, child number 0
-------------------------------------
select * from t where a=1

Plan hash value: 2971698376

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T           |     1 |     6 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C009298 |     1 |       |     0   (0)|          |
-------------------------------------------------------------------------------------------

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

   2 - access("A"=1)

SQL_ID  2bkjh9149pjqq, child number 0
-------------------------------------
select/*+ full(t) */ * from t where a=1

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     6 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("A"=1)

+ full-output
SQL> create table t(a unique, b) as
  2  select 1,1 from dual;

Table created.

SQL>
SQL> select blocks from user_tables where table_name='T';

    BLOCKS
----------
         4

SQL>
SQL> alter session set MAX_DUMP_FILE_SIZE = unlimited;

Session altered.

SQL> alter session set tracefile_identifier='minitab_ius';

Session altered.

SQL> alter session set events '10053 trace name context forever, level 1';

Session altered.

SQL>
SQL> alter session set tracefile_identifier='minitab_ius';

Session altered.

SQL> select * from t where a=1;

         A          B
---------- ----------
         1          1

SQL> alter session set tracefile_identifier='minitab_fts';

Session altered.

SQL> select/*+ full(t) */ * from t where a=1;

         A          B
---------- ----------
         1          1

SQL> alter session set tracefile_identifier='end';

Session altered.

SQL> alter session set events '10053 off';

Session altered.

SQL> set echo off;

SQL> @tracefile_by_mask.sql minitab

ADR_HOME                                 TRACE_FILENAME                                CHANGE_TIME     MODIFY_TIME
---------------------------------------- --------------------------------------------- --------------- ---------------
/opt/oracle/diag/rdbms/ora19/ORA19       ORA19_ora_18263_minitab_ius.trc               nov/15 22:28:29 nov/15 22:28:29
/opt/oracle/diag/rdbms/ora19/ORA19       ORA19_ora_18263_minitab_fts.trc               nov/15 22:28:29 nov/15 22:28:29

SQL> @tracefile_spool.sql ORA19_ora_18263_minitab_ius.trc /mnt/d/temp/local/ORA19_ora_18263_minitab_ius.trc
SQL> @tracefile_spool.sql ORA19_ora_18263_minitab_fts.trc /mnt/d/temp/local/ORA19_ora_18263_minitab_fts.trc

SQL> @plan g36j00khg170d

Childs:

CHILD_NUMBER
------------
           0


P P_FORMAT
- ---------------
  typical


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  g36j00khg170d, child number 0
-------------------------------------
select * from t where a=1

Plan hash value: 2971698376

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T           |     1 |     6 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C009298 |     1 |       |     0   (0)|          |
-------------------------------------------------------------------------------------------

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

   2 - access("A"=1)


19 rows selected.

SQL> @plan 2bkjh9149pjqq

Childs:

CHILD_NUMBER
------------
           0


P P_FORMAT
- ---------------
  typical


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  2bkjh9149pjqq, child number 0
-------------------------------------
select/*+ full(t) */ * from t where a=1

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     6 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("A"=1)


18 rows selected.


Модератор: Вложение удалено.


Сообщение было отредактировано: 16 ноя 21, 01:47
16 ноя 21, 01:45    [22396433]     Ответить | Цитировать Сообщить модератору
 Re: Использовать индекс  [new]
Sayan Malakshinov
Member

Откуда: Мск
Сообщений: 5956
+ картинка с планами под спойлером
Картинка с другого сайта.
16 ноя 21, 01:50    [22396434]     Ответить | Цитировать Сообщить модератору
 Re: Использовать индекс  [new]
Правильный Вася
Member

Откуда:
Сообщений: 459
witte
Правильный Вася
А уж если в индексе нет нужных полей, выбираемых по SELECT, то уж тем более нет смысла его читать и потом читать таблицу - двойная работа.

Это как? Если мне нужна строчка / немного строчек целиком из большой таблицы по индексу то Вы предполагаете что оптимизатор будет фуллсканить таблицу?

Вы вырвали предложение из контекста про одну запись в таблице. Если индекс не содержит всё, что нужно выбрать, то нет смысла в двойном чтении.

Sayan Malakshinov
ну не правда же...

Боюсь, что это зависит от наличия адекватной статистики, размеров блоков и прочих настроек.
16 ноя 21, 19:07    [22396674]     Ответить | Цитировать Сообщить модератору
 Re: Использовать индекс  [new]
Sayan Malakshinov
Member

Откуда: Мск
Сообщений: 5956
Правильный Вася

Боюсь, что это зависит от наличия адекватной статистики, размеров блоков и прочих настроек.
это стандартное поведение с адекватной статистикой, дефолтными параметрами и стандартным размером блока 8к
¯\_(ツ)_/¯
17 ноя 21, 02:27    [22396793]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить