оптимизировать запрос на ORACLE. проверенный индекс. Oracle индексы оптимизация запросов
database - оптимизировать запрос на ORACLE. проверенный индекс
"вещь MYTAB2 имеет только 1000 записей!"
Да, но соответствующий дататаунт - сколько записей в MYTAB1 соответствует этим тысячам записей? Какой процент всей таблицы соответствует этой цифре? И каково распределение этих записей в MYTAB1?
Если вы хотите удалить 20% строк в MYTAB1, индекс будет только ухудшать производительность (если оптимизатор достаточно глуп, чтобы использовать его). Если вы удаляете только 0,1% записей в MYTAB1, но эти записи распределены по каждому блоку в таблице, снова более эффективным вариантом является Full Table Scan.
В настройке нет простых решений. Это всегда зависит от взаимодействия нескольких разных факторов. Как часто вы хотите запустить это удаление? У вас есть лицензия Enterprise Edition и много запасных возможностей процессора? И так далее.
Если MYID1 является основным ключом MYTAB1, то в этом столбце должен быть индекс UNIQUE. Поэтому вам не нужно создавать новый индекс.
Если вы не являетесь одним из тех мест, которое не мешает применять ограничения целостности ваших таблиц. Это плохая практика. Помимо очевидных преимуществ обеспечения целостности, ограничения дают оптимизатору полезную информацию, которая ведет к лучшим планам исполнения.
В любом случае корень вашей проблемы ясен в плане объяснения, который вы сейчас опубликовали. Вы говорите, что у MYTAB2 есть только одна тысяча строк, но оптимизатор, кажется, думает, что у него четыреста тридцать семь тысяч строк. Итак, ясно, что вам нужно собрать свежую статистику по этому столу:
exec dbms_state.gather_table_stats(ownname=>user, tabname=>'MYTAB2',estimate_percent=>100)Я полагаю, что статистика MYTAB1 верна и имеет порядка 3,7 миллиона строк? Если так, индексированный поиск будет наиболее эффективным. Вам нужно проверить, что у вас есть уникальный индекс в этом столбце первичного ключа:
select i.index_name, i.uniqueness from user_indexes i join user_ind_columns c on ( i.index_name = c.index_name) where i.table_name = 'MYTAB1' and c.column_name = 'MYID1'Если у вас есть индекс, но он не уникален, вы должны, вероятно, отказаться от него и создать уникальный индекс.
Обратите внимание, что если вы ошибаетесь, и этот столбец не является первичным ключом, то есть имеет дубликаты, то этот оператор CREATE INDEX не будет выполнен. В этом случае у вас есть большая проблема, о которой вам нужно подумать.
"однако количество строк, которые содержит [MYTAB2], сильно изменчиво... в основном некоторые строки добавляются в таблицу, затем некоторые удаляются, и процесс продолжается"
В этом случае наличие фиксированных статистических данных является весьма бесполезным. Лучшая идея - заставить оптимизатор динамически генерировать статистику во время выполнения.
exec dbms_state.delete_table_stats(ownname=>user, tabname=>'MYTAB2') exec dbms_state.lock_table_stats(ownname=>user, tabname=>'MYTAB2')Удаление статистики таблицы, а затем ее блокировка заставит базу данных генерировать статистику для таблицы каждый раз, когда вы включаете ее в запрос, при условии, что вы включили динамическую выборку. Это должно создать лучший план выполнения всякий раз, когда вы запускаете этот оператор delete, независимо от того, сколько строк MYTAB2 происходит в этот момент.
Узнать больше.
qaru.site
[indexing] Как выбрать и оптимизировать индексы оракула? [optimization] [oracle]
Рассмотрим ключи индексирования, которые часто используются в предложениях WHERE.
Рассмотрим ключи индексирования, которые часто используются для объединения таблиц в операторы SQL. Дополнительные сведения об оптимизации объединений см. В разделе «Использование кластеров Hash для производительности».
Выберите индексные клавиши с высокой избирательностью. Селективность индекса - это процент строк в таблице с одинаковым значением для индексированного ключа. Селективность индекса является оптимальной, если несколько строк имеют одинаковое значение. Примечание. Oracle автоматически создает индексы или использует существующие индексы для ключей и выражений уникальных и первичных ключей, которые вы определяете с ограничениями целостности. Индексирование столбцов с низкой степенью избирательности может быть полезным, если распределение данных искажено, так что одно или два значения встречаются гораздо реже, чем другие значения.
Не используйте стандартные индексы B-дерева для ключей или выражений с несколькими отдельными значениями. Такие клавиши или выражения обычно имеют низкую избирательность и, следовательно, не оптимизируют производительность, если часто выбранные значения клавиш отображаются менее часто, чем другие значения ключа. Вы можете эффективно использовать растровые индексы в таких случаях, если индекс не изменяется часто, как в случае приложения OLTP с высоким параллелизмом.
Не указывайте часто используемые столбцы. Операторы UPDATE, которые изменяют индексированные столбцы и инструкции INSERT и DELETE, которые изменяют индексированные таблицы, занимают больше времени, чем если бы не было индекса. Такие операторы SQL должны изменять данные в индексах, а также данные в таблицах. Они также генерируют дополнительную отмену и повтор.
Не индексируйте ключи, которые отображаются только в предложениях WHERE с функциями или операторами. Предложение WHERE, которое использует функцию, отличную от MIN или MAX, или оператор с индексированным ключом, не предоставляет доступ к этому пути, который использует индекс, за исключением индексов на основе функций.
Рассмотрим индексирование внешних ключей ограничений ссылочной целостности в случаях, когда большое количество одновременных операторов INSERT, UPDATE и DELETE обращаются к родительским и дочерним таблицам. Такой индекс позволяет UPDATE и DELETEs в родительской таблице без совместного доступа к дочерней таблице.
Выбирая индексировать ключ, подумайте о том, стоит ли увеличение производительности для запросов потерять производительность для INSERT, UPDATE и DELETE и использовать пространство, необходимое для хранения индекса. Вы можете поэкспериментировать, сравнивая время обработки SQL-операторов с индексами и без них. Вы можете измерить время обработки с помощью средства отслеживания SQL.
code-examples.net
| declare @StartDate datetime, @EndDate datetime set @StartDate = '20130101' set @EndDate = '20150825' create table #note ( co_num nvarchar(20), note nvarchar(4000) ) DECLARE SelCoCrs CURSOR LOCAL STATIC FOR select distinct co_num,RowPointer from ( select order_date, credit_hold, co_num, lcr_num, est_num, cust_num, cust_seq, rusdrop_num, rusdrop_seq, createdby, ship_code, charfld1, type, trans_nat, RowPointer from co with (nolock) union all select order_date, 0 credit_hold, co_num, lcr_num, est_num, cust_num, cust_seq, cust_num, cust_seq, createdby, ship_code, charfld1, type, trans_nat, RowPointer from coh with (nolock) ) co WHERE co.order_date >= @StartDate and co.order_date <= @EndDate and co.type <> 'E' OPEN SelCoCrs declare @Severity int, @CoNum nvarchar(20), @RowPointer nvarchar(50), @Note nvarchar(4000) set @Severity = 0 WHILE @Severity = 0 BEGIN set @Note = '' FETCH SelCoCrs INTO @CoNum, @RowPointer IF @@FETCH_STATUS = -1 BREAK SELECT @Note = @Note + ', ' + case when Note like 'ATTACHMENT:%' then Description else [Description] + ' ' + cast([Note] as nvarchar(2000)) end FROM [zip_work].[dbo].[ReportNotesView] where TableName = 'co' and [RefRowPointer] = @RowPointer insert into #note select @CoNum co_num, substring(@Note,3,len(@Note)) note END declare @baseitem TABLE ( [item] [nvarchar](15) NOT NULL, [Description] [nvarchar](100) NOT NULL, [isbase] [int] NOT NULL ) insert into @baseitem SELECT [_Code] collate Cyrillic_General_100_CI_AS item ,[_Description] [Description] ,1 isbase FROM [s01-sq03].[ui].[dbo].[_Reference171] where _Folder = 0x01 and _Fld384 <> 0x00 and [_OwnerIDRRef] = 0xB636852BC90F1C0541826510D7F6B034 --ЗИП and [_Marked] = 0x00 select co.order_date [Дата заказа] , ci.due_date [Плановая дата] , ci.uf_contract_date [Контр. дата] , co.CreatedBy [Ответственный] , ci.item [Код изделия] , (select string from sl_zip.forms_work.dbo.russianstrings where name = 'sItemStatus=' + i.stat) [Статус изделия] , i.description [Изделие] , ISNULL(t.description,'Реализация') [Тип сделки] , ISNULL(ci.lot,'') [Партия] , ISNULL(logo.description,'') [Логотип] , (select string from sl_zip.forms_work.dbo.russianstrings where name = 'sCoItemStatus=' + ci.stat) [Статус ЗК] , ci.co_num [co_num] , cust_lcr.confrm_num [Договор] , ca.name [Клиент] , div_mgr.div_name [Департамент] , dept.description [Отдел] , new_dept.description [Подразделение] , ci.qty_ordered [Кол-во заказано] , ci.qty_ordered * ci.price_conv [Сумма заказано] , iw.qty_reorder [Страховой запас] , ci.qty_shipped [Кол-во отгружено] , ci.qty_shipped * ci.price_conv [Сумма отгружено] , ci.ship_date [Дата отгружки] , ci.qty_ordered - ci.qty_shipped [Колво не отружено] , (ci.qty_ordered - ci.qty_shipped) * ci.price_conv [Сумма не отгружено] , iw.qty_on_hand [Кол-во в наличии] , iw.qty_alloc_co [Кол-во в заказах] , iw.qty_on_hand - iw.qty_alloc_co [Наличие с учетом потребности] , iw.qty_wip [В производстве] , CASE WHEN iw.qty_alloc_co > iw.qty_on_hand + iw.qty_wip THEN iw.qty_alloc_co - iw.qty_on_hand - iw.qty_wip ELSE 0 END [К производству] , shipcode.description [Условия поставки] , CASE WHEN qty_ordered = qty_shipped and qty_packed = qty_shipped THEN 'Отфактурован' WHEN qty_ordered = qty_shipped THEN 'Отгружен' WHEN qty_shipped > 0 THEN 'Частично' WHEN exists (select co_num from ruscocomplect where co.co_num = ruscocomplect.co_num) THEN 'Выписан' ELSE 'Создан' END [Факт отгрузки] , CASE WHEN co.order_date > CAST('2010-01-01' as datetime) THEN DATEDIFF(day, ISNULL(ci.uf_contract_date, ci.due_date), ISNULL(ci.ship_date, GETDATE())) ELSE DATEDIFF(day, ci.due_date, ISNULL(ci.ship_date, GETDATE())) END AS [Просрочено дней] --, DATEDIFF(day, ci.due_date, ISNULL(ci.ship_date, GETDATE())) AS [Просрочено дней] , co.credit_hold [Заблокирован] , ca.credit_hold [Заблокирован клиент] , (SELECT TOP 1 case when Note like 'ATTACHMENT:%' then Description else [Description] + ' ' + cast([Note] as nvarchar(2000)) end Note FROM [zip_work].[dbo].[ReportNotesView] where TableName = 'co' and [RefRowPointer] = co.RowPointer) Note , n.Note all_note ,ISNULL(ca_drop.name, ca_shipto.name) [Грузополучатель] ,ISNULL(co.est_num,'') [Смета] ,hi.description [Род] ,hi.PT_descr [Вид] ,hi.Class_descr [Класс] ,isnull([isbase],0) isbase ,pp.[UDFINT1] ispp ,пр.Наименование [Проект] from ( select due_date, item, co_num, qty_ordered, qty_shipped, qty_packed, ship_date, price_conv, stat, uf_contract_date, lot from coitem with (nolock) union all select due_date, item, co_num, qty_ordered, qty_shipped, qty_packed, ship_date, price_conv, stat, due_date, NULL from citemh with (nolock) ) ci join item i with (nolock) on ci.item = i.item join ( select order_date, credit_hold, co_num, lcr_num, est_num, cust_num, cust_seq, rusdrop_num, rusdrop_seq, createdby, ship_code, charfld1, type, trans_nat, RowPointer from co with (nolock) union all select order_date, 0 credit_hold, co_num, lcr_num, est_num, cust_num, cust_seq, cust_num, cust_seq, createdby, ship_code, charfld1, type, trans_nat, RowPointer from coh with (nolock) ) co on ci.co_num = co.co_num join custaddr ca with (nolock) on co.cust_num = ca.cust_num and ca.cust_seq = 0 join custaddr ca_shipto with (nolock) on co.cust_num = ca_shipto.cust_num and ca_shipto.cust_seq = co.cust_seq left join custaddr ca_drop with (nolock) on co.rusdrop_num = ca_drop.cust_num and co.rusdrop_seq = ca_drop.cust_seq --*** JOIN UserNames with (nolock) ON co.CreatedBy = UserNames.username JOIN user_local with (nolock) ON UserNames.UserId = user_local.UserId LEFT JOIN employee with (nolock) ON user_local.emp_num = employee.emp_num LEFT JOIN dept with (nolock) ON employee.dept = dept.dept LEFT JOIN div_mgr with (nolock) ON dept.div_num = div_mgr.div_num LEFT JOIN shipcode with (nolock) ON co.ship_code = shipcode.ship_code LEFT JOIN ( select item , SUM(qty_reorder) qty_reorder , SUM(qty_on_hand) qty_on_hand , SUM(qty_alloc_co) qty_alloc_co , SUM(qty_wip) qty_wip from itemwhse with (nolock) group by item ) iw ON ci.item = iw.item left join dept new_dept with (nolock) on co.charfld1 = new_dept.dept left join lot with (nolock) on ci.lot = lot.lot and ci.item = lot.item left join userdefinedtypevalues logo with (nolock) on logo.value = lot.charfld2 and logo.typename = 'Логотип' left join trans_nature t with (nolock) on co.trans_nat = t.trans_nat left join cust_lcr with (nolock) ON co.cust_num = cust_lcr.cust_num AND co.lcr_num = cust_lcr.lcr_num left join ( select itemtype ,max(Par_Class) Par_Class ,max(PC_descr) PC_descr ,max(Class_descr) Class_descr ,max(PT_descr) PT_descr ,max(description) description from [s01-sq03].[WHSE].[dbo].[H_Item] group by itemtype ) hi on left(ci.item,9) = hi.itemtype left join @baseitem bi on bi.item = i.item collate Cyrillic_General_CI_AS left join ( SELECT [RowId] ,[UDFINT1] FROM [zip_work].[dbo].[UserDefinedFields] with (nolock) where TableName = 'co' and [UDFINT1] = 1 ) pp on pp.[RowId] = co.RowPointer left join ( SELECT пр.Наименование, кс.КодSL, кс.Номер FROM [S01-SQ03].[ui].[dbo].Документ_КарточкаСделки AS кс LEFT OUTER JOIN [S01-SQ03].[ui].[dbo].Справочник_Проекты AS пр ON кс.Справочник_Проект_Проект = пр.GUIDЭлемента where кс.Справочник_Проект_Проект <> 0x00000000000000000000000000000000 and кс.КодSL <> '' ) пр on пр.КодSL = ci.co_num left join #note n on n.co_num collate Cyrillic_General_100_CI_AS = ci.co_num WHERE co.order_date >= @StartDate and co.order_date <= @EndDate and co.type <> 'E' /* and co.cust_num LIKE REPLACE(@Customer, '*', '%') and ci.item LIKE REPLACE(@Item, '*', '%') and new_dept.description in (@dept) */ order by ci.co_num , ci.item drop table #note |
forundex.ru
| use tempdb go /******** Блок заполнения ************************************************************************************/ IF NOT EXISTS (SELECT schema_name FROM INFORMATION_SCHEMA.SCHEMATA WHERE schema_name = 'buf' ) EXEC sp_executesql N'CREATE SCHEMA buf' CREATE PARTITION FUNCTION [ACTIVE](bit) AS RANGE LEFT FOR VALUES (0) GO CREATE PARTITION SCHEME [ACTIVE] AS PARTITION [ACTIVE] TO ([PRIMARY], [PRIMARY]) GO IF OBJECT_ID('buf.DIC_TEST', 'U') IS NULL BEGIN CREATE TABLE buf.DIC_TEST ( [SOURCE_SYSTEM] int not null, [SOURCE_KEY] int not null, [ATTR] varchar(200) ) CREATE CLUSTERED INDEX [IX] ON [buf].[DIC_TEST] ([SOURCE_KEY] ASC, [SOURCE_SYSTEM] ASC) insert into [buf].[DIC_TEST] select 1, 21, 'апельсин' union all select 1, 22, 'яблоко Гала' union all select 1, 23, 'вишня 5' union all select 2, 21, 'слива' union all select 2, 22, 'яблоко (антоновка)' union all select 2, 23, 'вишня 5' union all select 2, 24, 'апельсин Марокко' union all select 2, 25, 'апельсин' set nocount on DECLARE @i int = 5000000 WHILE @i > 0 BEGIN INSERT INTO [buf].[DIC_TEST]([SOURCE_SYSTEM],[SOURCE_KEY],[ATTR]) VALUES ((@i/3)+5, @i, CONVERT(varchar(200),NEWID())) SET @i = @i - 1 END set nocount off END IF OBJECT_ID('dbo.DIC_TEST_LINK_HISTORY', 'U') IS NULL CREATE TABLE [dbo].[DIC_TEST_LINK_HISTORY]( [ID] [int] IDENTITY(1,1) NOT NULL, [SOURCE_SYSTEM] [int] NOT NULL, [SOURCE_KEY] [int] NOT NULL, [ATTR] [varchar](200) NULL, [VALID_FROM] [date] NULL, [VALID_TO] [date] NULL, [ACTIVE] [bit] NULL, [DATE_CHANGE] [datetime2](3) NULL, CONSTRAINT [PK_DIC_TEST_LINK_HISTORY] PRIMARY KEY NONCLUSTERED ([ID]) ON [PRIMARY] ) ON [ACTIVE]([ACTIVE]) CREATE CLUSTERED INDEX [IX] ON [dbo].[DIC_TEST_LINK_HISTORY] ([SOURCE_KEY] ASC,[SOURCE_SYSTEM] ASC) ON [ACTIVE]([ACTIVE]) /* update [dbo].[DIC_TEST_LINK_HISTORY] set ACTIVE = 0, VALID_TO = getdate()-1, DATE_CHANGE = getdate()-1 where not (SOURCE_SYSTEM = 1 AND SOURCE_KEY = 21) and ACTIVE = 1 */ go create view [dbo].[v_DIC_TEST_LINK_HISTORY] as select * from [dbo].[DIC_TEST_LINK_HISTORY] where $PARTITION.ACTIVE(ACTIVE) = 2 go /******** Блок тестирования ************************************************************************************/ update [buf].[DIC_TEST] set ATTR = 'апельсин ' + convert(varchar(20),RAND()) where SOURCE_SYSTEM = 1 AND SOURCE_KEY = 21 DECLARE @d datetime2(3) = getdate()+(select count(*) from [dbo].[DIC_TEST_LINK_HISTORY] where SOURCE_SYSTEM = 1 AND SOURCE_KEY = 21) INSERT INTO [dbo].[DIC_TEST_LINK_HISTORY] ([SOURCE_SYSTEM],[SOURCE_KEY],[ATTR],[VALID_FROM],[VALID_TO],[ACTIVE],[DATE_CHANGE]) SELECT [SOURCE_SYSTEM], [SOURCE_KEY], [ATTR], CONVERT(date, @d) as [VALID_FROM], CONVERT(date, '99991231') as [VALID_TO], CONVERT(bit, 1) as [ACTIVE], @d as [DATA_CHANGE] FROM ( MERGE [dbo].[v_DIC_TEST_LINK_HISTORY] AS target USING [buf].[DIC_TEST] AS source ON target.SOURCE_SYSTEM = source.SOURCE_SYSTEM AND target.SOURCE_KEY = source.SOURCE_KEY --AND target.ACTIVE = 1 -- заменено на представление, так намного быстрее -- 1) если (активная!) запись изменена в первоисточнике, то помечаем тут -- эту запись как неактуальную и (выше) вставляем новую запись во внешнем INSERT, -- также, если это сегодняшняя запись, то у нее правим атрибуты и дату изменения, -- а все остальное не трогаем WHEN MATCHED AND --(target.ATTR <> source.ATTR) EXISTS (SELECT Source.ATTR EXCEPT SELECT Target.ATTR) THEN UPDATE SET [ACTIVE] = CASE WHEN [VALID_FROM] = CONVERT(date, @d) THEN target.[ACTIVE] ELSE 0 END, [VALID_TO] = CASE WHEN [VALID_FROM] = CONVERT(date, @d) THEN target.[VALID_TO] ELSE DATEADD(dd, -1, @d) END, [ATTR] = CASE WHEN [VALID_FROM] = CONVERT(date, @d) THEN source.[ATTR] ELSE target.ATTR END, [DATE_CHANGE] = @d -- 2) если записи в приемнике нет, то вставляем новую WHEN NOT MATCHED BY TARGET THEN INSERT ([SOURCE_SYSTEM],[SOURCE_KEY],[ATTR],[DATE_CHANGE],[VALID_FROM],[VALID_TO],[ACTIVE]) VALUES (source.[SOURCE_SYSTEM], source.[SOURCE_KEY], source.[ATTR], @d, -- [DATA_CHANGE] CONVERT(date, @d), -- [VALID_FROM] CONVERT(date, '99991231'), -- [VALID_TO] CONVERT(bit, 1)) -- [ACTIVE] -- 3) если запись удалена в первоисточнике, то помечаем старую как неактуальную WHEN NOT MATCHED BY SOURCE AND target.[ACTIVE] = 1 THEN UPDATE SET [ACTIVE] = CONVERT(bit, 0), [VALID_TO] = CONVERT(date, @d), [DATE_CHANGE] = @d OUTPUT source.*, Deleted.[VALID_FROM] as [VALID_FROM_OLD], $action as [ACTION] ) AS MergeOutput WHERE MergeOutput.[ACTION] = 'UPDATE' AND -- если скрипт выполняется в один и тот же день, то [VALID_FROM] останется без изменений и для старой записи, -- и для новой, такие записи не нужно вставлять (и нужно обновлять в п1), -- новую же запись вставляем только при различии [VALID_FROM] (то есть, если изменение атрибутов произошло в разные дни), -- поэтому добавляем такой фильтр MergeOutput.[VALID_FROM_OLD] <> CONVERT(date, @d) OPTION (RECOMPILE) |
forundex.ru
