Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 ORDER BY не использует индекс  [new]
lu4kiv
Member

Откуда:
Сообщений: 42
Oracle 10.2, Windows 2008 x32. Временный сервер.

Есть таблица примерно такого плана:
CREATE TABLE "tbl_Contact" (
  ID                      VARCHAR2(38 BYTE)     NOT NULL,
  "Name"                  VARCHAR2(250 CHAR),
)

В таблице 3-млн. записей.

Есть созданный индекс
CREATE INDEX "IContactName" ON "tbl_Contact" ("Name")

Не подскажет ли многоуважаемый all, почему при таком запросе
select ID, "Name" from "tbl_Contact" order by "Name"

не подхватывается индекс, а делается фулл-скан.

Plan
SELECT STATEMENT  ALL_ROWS Cost: 49,511  Bytes: 48,000,400  Cardinality: 3,000,025  		
	2 SORT ORDER BY  Cost: 49,511  Bytes: 48,000,400  Cardinality: 3,000,025  	
		1 TABLE ACCESS FULL TABLE TS.tbl_Contact Cost: 33,093  Bytes: 48,000,400  Cardinality: 3,000,025  
(Копипаст из TOAD)
Запрос отрабатывает приблизительно 10-15 сек. Oracle 10.2 установленный по дефолту.
14 июн 10, 14:13    [8938597]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
Андрей Панфилов
Member

Откуда: Москва > Melbourne
Сообщений: 3778
lu4kiv
В таблице 3-млн. записей.
и сколько из трех мильенов должно попасть в приложение?
14 июн 10, 14:15    [8938607]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
Alexander Ryndin
Member

Откуда:
Сообщений: 4917
Блог
lu4kiv
Oracle 10.2, Windows 2008 x32. Временный сервер.

Есть таблица примерно такого плана:
CREATE TABLE "tbl_Contact" (
  ID                      VARCHAR2(38 BYTE)     NOT NULL,
  "Name"                  VARCHAR2(250 CHAR),
)

В таблице 3-млн. записей.

Есть созданный индекс
CREATE INDEX "IContactName" ON "tbl_Contact" ("Name")

Не подскажет ли многоуважаемый all, почему при таком запросе
select ID, "Name" from "tbl_Contact" order by "Name"

не подхватывается индекс, а делается фулл-скан.

Plan
SELECT STATEMENT  ALL_ROWS Cost: 49,511  Bytes: 48,000,400  Cardinality: 3,000,025  		
	2 SORT ORDER BY  Cost: 49,511  Bytes: 48,000,400  Cardinality: 3,000,025  	
		1 TABLE ACCESS FULL TABLE TS.tbl_Contact Cost: 33,093  Bytes: 48,000,400  Cardinality: 3,000,025  
(Копипаст из TOAD)
Запрос отрабатывает приблизительно 10-15 сек. Oracle 10.2 установленный по дефолту.
1) если статистика собрана, то, судя по всему, дешевле прочитать всю таблицу и отсортироваться ее, чем работать с индексом.
2) размер таблицы у вас очень маленький 48 Мб. Если сделать ее хотя бы 10 Гб, то должна быть совсем другая картина
3) можно попробовать заставить Oracle использовать индекс и посмотреть на стоимость. Hint Index.
14 июн 10, 14:19    [8938619]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
Elic
Member

Откуда:
Сообщений: 29990
lu4kiv
VARCHAR2, order by, почему не подхватывается индекс, а делается фулл-скан.
STFF/RTFM NLS_SORT
14 июн 10, 14:39    [8938676]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
lu4kiv

Есть созданный индекс
CREATE INDEX "IContactName" ON "tbl_Contact" ("Name")

Не подскажет ли многоуважаемый all, почему при таком запросе
select ID, "Name" from "tbl_Contact" order by "Name"

не подхватывается индекс, а делается фулл-скан.

попробуйте варианты и покажите время
1. уберите ID из select
2. добавьте ID к индексу
14 июн 10, 14:44    [8938696]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
lu4kiv
Member

Откуда:
Сообщений: 42
Андрей Панфилов
и сколько из трех мильенов должно попасть в приложение?

Сейчас речь не о скорости fetch'а. Это понятно, что fetch всех записей будет долгим.
14 июн 10, 14:52    [8938714]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
lu4kiv
Member

Откуда:
Сообщений: 42
Alexander Ryndin
1) если статистика собрана, то, судя по всему, дешевле прочитать всю таблицу и отсортироваться ее, чем работать с индексом.

Статистика собрана. И по индексу и по таблице.

Alexander Ryndin
2) размер таблицы у вас очень маленький 48 Мб. Если сделать ее хотя бы 10 Гб, то должна быть совсем другая картина

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

Alexander Ryndin
3) можно попробовать заставить Oracle использовать индекс и посмотреть на стоимость. Hint Index.


На хинты почему-то оракл не обращает внимания.
SELECT /*+ index(tbl_Contact "IContactName") */
    "Name"
FROM 
    "tbl_Contact" tbl_Contact
ORDER BY
    tbl_Contact."Name"

Plan
SELECT STATEMENT  ALL_ROWS Cost: 49,511  Bytes: 48,000,400  Cardinality: 3,000,025  		
	2 SORT ORDER BY  Cost: 49,511  Bytes: 48,000,400  Cardinality: 3,000,025  	
		1 TABLE ACCESS FULL TABLE TS.tbl_Contact Cost: 33,093  Bytes: 48,000,400  Cardinality: 3,000,025


Что самое интересное, что если добавить условие и убрать ORDER BY:

SELECT /*+ index(tbl_Contact "IContactName") */
    "Name"
FROM 
    "tbl_Contact" tbl_Contact
WHERE
    tbl_Contact."Name" LIKE '%%'

то результат - это то, что хотелось бы получить в первом случае. Открытие запроса - мгновенное и присутствует сортировка.
Plan
SELECT STATEMENT  ALL_ROWS Cost: 11,404  Bytes: 2,400,016  Cardinality: 150,001  	
	1 INDEX FULL SCAN INDEX TS.ICONTACTNAME Cost: 11,404  Bytes: 2,400,016  Cardinality: 150,001  
14 июн 10, 15:00    [8938746]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
lu4kiv
Сейчас речь не о скорости fetch'а. Это понятно, что fetch всех записей будет долгим.

А потом добавится поле new_field и запросы будут в духе
select id, name from tbl_Contact where new_field = :A order by name
и вы будете спрашивать почему не испльзуется индекс и даже хинт не помогает :) ?

P.S. не используйте закавыченные имена типа "tbl_Contact" - избавите от головной боли себя и тех кто будет это всё поддерживать.
14 июн 10, 15:05    [8938766]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
Elic
Member

Откуда:
Сообщений: 29990
lu4kiv
  "Name"                  VARCHAR2(250 CHAR),
В индексе не хранятся null-ы.
14 июн 10, 15:06    [8938769]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
Андрей Панфилов
Member

Откуда: Москва > Melbourne
Сообщений: 3778
lu4kiv
Сейчас речь не о скорости fetch'а. Это понятно, что fetch всех записей будет долгим.
дайте ему /*+ first_rows(1) */
14 июн 10, 15:06    [8938770]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
lu4kiv
Member

Откуда:
Сообщений: 42
_Nikotin
1. уберите ID из select

Не помогло. ПыСы: Сорри, не знаю как показать время выполнения.
_Nikotin
2. добавьте ID к индексу


CREATE INDEX TS."IContactName3" ON TS."tbl_Contact" ("Name", ID)

Добавил.

SELECT
    ID,
    "Name"
FROM 
    "TS"."tbl_Contact" tbl_Contact
ORDER BY
    tbl_Contact."Name"

Результат стал немного лучше. Выборка отрабатывает за 5-6 сек.

Plan
SELECT STATEMENT  ALL_ROWS Cost: 46,982  Bytes: 165,001,375  Cardinality: 3,000,025  		
	2 SORT ORDER BY  Cost: 46,982  Bytes: 165,001,375  Cardinality: 3,000,025  	
		1 INDEX FAST FULL SCAN INDEX TS.IContactName3 Cost: 6,146  Bytes: 165,001,375  Cardinality: 3,000,025  

С хинтами - то же самое.

SELECT /*+ index("tbl_Contact" "IContactName3") */
    ID,
    "Name"
FROM 
    "TS"."tbl_Contact" tbl_Contact
ORDER BY
    tbl_Contact."Name"


Plan
SELECT STATEMENT  ALL_ROWS Cost: 46,982  Bytes: 165,001,375  Cardinality: 3,000,025  		
	2 SORT ORDER BY  Cost: 46,982  Bytes: 165,001,375  Cardinality: 3,000,025  	
		1 INDEX FAST FULL SCAN INDEX TS.IContactName3 Cost: 6,146  Bytes: 165,001,375  Cardinality: 3,000,025 
14 июн 10, 15:07    [8938776]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
lu4kiv
На хинты почему-то оракл не обращает внимания.
SELECT /*+ index(tbl_Contact "IContactName") */
    "Name"
FROM 
    "tbl_Contact" tbl_Contact
ORDER BY
    tbl_Contact."Name"

Попробуйте хинт /*+ first_rows */.
То что оракл проигнорировал ваш /*+ index(tbl_Contact "IContactName") */ вероятно начало проблем с закавыченными именами :)
14 июн 10, 15:08    [8938779]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
lu4kiv
Member

Откуда:
Сообщений: 42
Elic
STFF/RTFM NLS_SORT


Пробовали - результат тот же.

SELECT
    "Name"
FROM 
    "tbl_Contact" tbl_Contact
ORDER BY
    NLSSORT(tbl_Contact."Name")


Plan
SELECT STATEMENT  ALL_ROWS Cost: 49,511  Bytes: 48,000,400  Cardinality: 3,000,025  		
	2 SORT ORDER BY  Cost: 49,511  Bytes: 48,000,400  Cardinality: 3,000,025  	
		1 TABLE ACCESS FULL TABLE TS.tbl_Contact Cost: 33,093  Bytes: 48,000,400  Cardinality: 3,000,025  
14 июн 10, 15:10    [8938787]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
_Nikotin
То что оракл проигнорировал ваш /*+ index(tbl_Contact "IContactName") */ вероятно начало проблем с закавыченными именами :)

ой, не заметил что поле nullable, либо добавьте ограничение not null, либо where name is not null в запрос
14 июн 10, 15:11    [8938789]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
AlexFF__|
Member

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

Ну заканчивай уже тормозить )
14 июн 10, 15:12    [8938794]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
lu4kiv
Member

Откуда:
Сообщений: 42
Андрей Панфилов
lu4kiv
Сейчас речь не о скорости fetch'а. Это понятно, что fetch всех записей будет долгим.
дайте ему /*+ first_rows(1) */


Не работает.

SELECT /*+ first_rows(1) */
    "Name"
FROM 
    "tbl_Contact" tbl_Contact
ORDER BY
    NLSSORT(tbl_Contact."Name")


Plan
SELECT STATEMENT  HINT: FIRST_ROWS Cost: 49,511  Bytes: 48,000,400  Cardinality: 3,000,025  		
	2 SORT ORDER BY  Cost: 49,511  Bytes: 48,000,400  Cardinality: 3,000,025  	
		1 TABLE ACCESS FULL TABLE TS.tbl_Contact Cost: 33,093  Bytes: 48,000,400  Cardinality: 3,000,025  
14 июн 10, 15:13    [8938799]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
lu4kiv
Результат стал немного лучше. Выборка отрабатывает за 5-6 сек.

из которых 95% - передача данных не клиент?
14 июн 10, 15:14    [8938803]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
lu4kiv
Member

Откуда:
Сообщений: 42
_Nikotin

А потом добавится поле new_field и запросы будут в духе
select id, name from tbl_Contact where new_field = :A order by name
и вы будете спрашивать почему не испльзуется индекс и даже хинт не помогает :) ?

Я говорю про конкретный случай, когда условий фильтрации нету.

_Nikotin

P.S. не используйте закавыченные имена типа "tbl_Contact" - избавите от головной боли себя и тех кто будет это всё поддерживать.


Я же не предлагаю вам не использовать Oracle. Если выбрано квотирование идентификаторов, то это было обосновано в нашем случае. И, имхо, разницы не вижу - по моему использование квотирования - это личное дело каждого.
14 июн 10, 15:16    [8938815]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
lu4kiv
Я же не предлагаю вам не использовать Oracle. Если выбрано квотирование идентификаторов, то это было обосновано в нашем случае. И, имхо, разницы не вижу - по моему использование квотирования - это личное дело каждого.

Не, я что, я ничего. Некоторые тут и ООП оракловое пытались использовать - лично дело каждого.
14 июн 10, 15:19    [8938826]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
Ааз
Member

Откуда: Москва/Протвино
Сообщений: 4274
lu4kiv
...
ORDER BY
    NLSSORT(tbl_Contact."Name")
Разве Elic просил использовать функцию в предикате?
14 июн 10, 20:45    [8939999]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
Ааз
Member

Откуда: Москва/Протвино
Сообщений: 4274
Ааз
в предикате
выбросить.
14 июн 10, 20:46    [8940004]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
Elic
Member

Откуда:
Сообщений: 29990
Ааз
lu4kiv
    NLSSORT(tbl_Contact."Name")
Разве Elic просил использовать функцию?
Да бог с ней функцией, в принципе. Но использование её без второго параметра - это полная дискредитация самой идеи использования этой функции.
14 июн 10, 20:53    [8940017]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18385
lu4kiv
Не подскажет ли многоуважаемый all, почему при таком запросе
select ID, "Name" from "tbl_Contact" order by "Name"

не подхватывается индекс, а делается фулл-скан.

Потому что колонка "Name" допускает null-значения, а в самом запросе нет никаких указаний на эту тему.
Два варианта:
- сделать колонку "Name" NOT NULL
- добавить в запрос условие
select ID, "Name" from "tbl_Contact" 
where "Name" is not null
order by "Name"
любой из этих вариантов даст оптимизатору возможность заменить сортировку на сканирование индекса при условии NLS_SORT=BINARY
14 июн 10, 21:58    [8940163]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
ITmonstrik
Member

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

1) Сделать ограничение NOT NULL на колонку или добавить такое ограничение в условие.
2) Возможно, в Вашем случае вы читаете что-то кроме ID и Name, а т.к. таблица маленькая, то вполне возможно, что данные проще получить FULL SCANом за один раз, чем каждый раз лазить и в индекс и в таблицу потом для получения недостающих данных.
15 июн 10, 15:00    [8944088]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY не использует индекс  [new]
stax..
Guest
andrey_anonymous
lu4kiv
Не подскажет ли многоуважаемый all, почему при таком запросе
select ID, "Name" from "tbl_Contact" order by "Name"

не подхватывается индекс, а делается фулл-скан.

Потому что колонка "Name" допускает null-значения, а в самом запросе нет никаких указаний на эту тему.
Два варианта:
- сделать колонку "Name" NOT NULL
- добавить в запрос условие
select ID, "Name" from "tbl_Contact" 
where "Name" is not null
order by "Name"
любой из этих вариантов даст оптимизатору возможность заменить сортировку на сканирование индекса при условии NLS_SORT=BINARY

так ж обычто сортировка не BINARY (нет смысла в BINARY для укр)
имхо надо менять индекс

.....
stax
15 июн 10, 16:44    [8945215]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить