Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 partition with "unit all"  [new]
essbase.ru
Member

Откуда: http://essbase.ru/about
Сообщений: 1407
Всем привет.

Есть желание сделать механизм партиционирования запроса через unit all

например создаю view
create view tbla_all_v as 
   select 1 a, aa.* from tbl1 aa
    union all
   select 2 a, aa.* from tbl2 aa
  


и хочу что бы при запросе
  select * from tbl_all_v where a=1 
  

sql не заходил во второй под-запрос.

( вместо tbl использую табличные функции )

Есть ли что от из хинтов , что подскажет оптимизатору "правильный" путь ?

ЕР
23 мар 15, 12:55    [17418886]     Ответить | Цитировать Сообщить модератору
 Re: partition with "unit all"  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
essbase.ru, вот тут почитайте: https://www.simple-talk.com/sql/sql-tools/sql-server-partitioning-without-enterprise-edition/
23 мар 15, 12:57    [17418899]     Ответить | Цитировать Сообщить модератору
 Re: partition with "unit all"  [new]
Glory
Member

Откуда:
Сообщений: 104751
essbase.ru
Есть ли что от из хинтов , что подскажет оптимизатору "правильный" путь ?

есть целый раздел хелпа, который так и называется partitioning.
А кнопки "правильный путь" опять нет.
23 мар 15, 12:57    [17418901]     Ответить | Цитировать Сообщить модератору
 Re: partition with "unit all"  [new]
essbase.ru
Member

Откуда: http://essbase.ru/about
Сообщений: 1407
essbase.ru
( вместо tbl использую табличные функции )
23 мар 15, 13:10    [17419031]     Ответить | Цитировать Сообщить модератору
 Re: partition with "unit all"  [new]
Glory
Member

Откуда:
Сообщений: 104751
essbase.ru
essbase.ru
( вместо tbl использую табличные функции )

https://technet.microsoft.com/en-us/library/ms188232(v=sql.105).aspx
23 мар 15, 13:15    [17419086]     Ответить | Цитировать Сообщить модератору
 Re: partition with "unit all"  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9169
Откройте справку CREATE VIEW (Transact-SQL) и найдите раздел Секционированные представления
23 мар 15, 13:15    [17419087]     Ответить | Цитировать Сообщить модератору
 Re: partition with "unit all"  [new]
iap
Member

Откуда: Москва
Сообщений: 47198
essbase.ru
essbase.ru
( вместо tbl использую табличные функции )
Тогда не взлетит.
Должны быть таблицы со специально построенным СHECK констрейнтом
23 мар 15, 13:16    [17419090]     Ответить | Цитировать Сообщить модератору
 Re: partition with "unit all"  [new]
essbase.ru
Member

Откуда: http://essbase.ru/about
Сообщений: 1407
iap
Должны быть таблицы со специально построенным СHECK констрейнтом


inner join с такими таблицами может дать "подсказку" оптимизатору ?
23 мар 15, 13:33    [17419270]     Ответить | Цитировать Сообщить модератору
 Re: partition with "unit all"  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
essbase.ru
вместо tbl использую табличные функции
+ Вот так можно извратиться
use tempdb;
go

create table dbo.t1 (id int primary key, v varchar(10));
create table dbo.t2 (id int primary key, v varchar(10));
go

create function dbo.fn1
(
 @v varchar(10)
)
returns table
as
return (
 select id, v from dbo.t1 where v = @v
);
go 

create function dbo.fn2
(
 @v varchar(10)
)
returns table
as
return (
 select id, v from dbo.t2 where v = @v
);
go

create function dbo.fn3
(
 @p int,
 @v varchar(10)
)
returns table
as
return (
 select
  fn1.id, fn1.v
 from
  dbo.fn1(@v) fn1 cross apply
  (select 1) a(p)
 where
  a.p = @p

 union all

 select
  fn2.id, fn2.v
 from
  dbo.fn2(@v) fn2 cross apply
  (select 2) a(p)
 where
  a.p = @p
);
go

insert into dbo.t1 select top (100) object_id, 't1' from sys.objects;
insert into dbo.t2 select top (100) object_id, 't2' from sys.objects;
go

set statistics xml, io on;
select * from dbo.fn3(1, 't1');
select * from dbo.fn3(2, 't2');
set statistics xml, io off;
go

drop function dbo.fn1, dbo.fn2, dbo.fn3;
drop table dbo.t1, dbo.t2;
go
23 мар 15, 13:47    [17419374]     Ответить | Цитировать Сообщить модератору
 Re: partition with "unit all"  [new]
iap
Member

Откуда: Москва
Сообщений: 47198
essbase.ru
iap
Должны быть таблицы со специально построенным СHECK констрейнтом


inner join с такими таблицами может дать "подсказку" оптимизатору ?
Не JOIN, а UNION ALL.
Не может, а даёт.
Вам же дали ссылки на "секционированные представления"?
23 мар 15, 13:53    [17419410]     Ответить | Цитировать Сообщить модератору
 Re: partition with "unit all"  [new]
essbase.ru
Member

Откуда: http://essbase.ru/about
Сообщений: 1407
invm
Вот так можно извратиться


- спасибо за пример )

один вопрос
не понятно выражение a(p) в
  (select 2) a(p) 

- как мне его трактовать для себя ?
23 мар 15, 13:53    [17419413]     Ответить | Цитировать Сообщить модератору
 Re: partition with "unit all"  [new]
essbase.ru
Member

Откуда: http://essbase.ru/about
Сообщений: 1407
iap
Вам же дали ссылки на "секционированные представления"?


essbase.ru
( вместо tbl использую табличные функции )
23 мар 15, 13:54    [17419418]     Ответить | Цитировать Сообщить модератору
 Re: partition with "unit all"  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
essbase.ru,

Зависит от того какого типа функции у вас, если это табличные inline, то оптимизатор сможет упростить такой запрос без всяких хинтов, при том условии, что вы написали (т.е. без параметризации или каких-то сложных выражений).
Если же у вас multystatement табличная функция, то оптимизатор не сможет упростить ничего, т.к. для него это будет все равно, что работа с табличной переменной, о содержимой которой он не догадывается и не может упрощать, чтобы не нарушить смысл запроса.
+
use tempdb;
go
create table t1(b int);
insert t1 values (1);
create table t2(b int);
insert t2 values (2);
go
-- Inline
create function dbo.uf_a()
returns table
return (select b from t1)
go
create function dbo.uf_b()
returns table
return (select b from t2)
go
create view dbo.v
as
select a = 1, b from dbo.uf_a()
union all
select a = 2, b from dbo.uf_b()
go
-- Plans
set statistics xml on
select * from dbo.v; -- two tables
select * from dbo.v where a = 1; --one table
set statistics xml off
go
drop function dbo.uf_a, dbo.uf_b
go
-- Multistatement
create function dbo.uf_a()
returns @t table(b int) 
as
begin
	insert @t
	select b from t1
	return
end
go
create function dbo.uf_b()
returns @t table(b int) 
as
begin
	insert @t
	select b from t1
	return
end
go
set statistics xml on
select * from dbo.v; -- two tables
select * from dbo.v where a = 1; --two tables
set statistics xml off
go
--clean
drop view dbo.v;
drop function dbo.uf_a, dbo.uf_b
drop table t1,t2;

ИМХО,
я конечно не знаю вашей задачи, но такие вопросы навевают мысль о том, что, возможно, вы выбрали не самый удачный путь ее реализации. Учитывая предыдущие вопросы и ваш оракловый опыт, может быть имеет смысл огласить задачу целиком, возможно подскажут более удачный путь/архитектуру решения.
Удачи.
23 мар 15, 13:59    [17419459]     Ответить | Цитировать Сообщить модератору
 Re: partition with "unit all"  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
SomewhereSomehow
essbase.ru,

Зависит от того какого типа функции у вас, если это табличные inline, то оптимизатор сможет упростить такой запрос без всяких хинтов, при том условии, что вы написали (т.е. без параметризации или каких-то сложных выражений).
Если же у вас multystatement табличная функция, то оптимизатор не сможет упростить ничего, т.к. для него это будет все равно, что работа с табличной переменной, о содержимой которой он не догадывается и не может упрощать, чтобы не нарушить смысл запроса.

Уточню. В первом случае (Inline), речь про доступ именно к таблицам внутри функций - доступ только к одной таблице, во втором (multistatement) речь про доступ ко временным таблицам, в которые помещается результат функций - доступ к обеим таблицам - оптимизатор это не упростит. Но код наполняющий таблицу для второго вызова (a=2), не выполнится. Может это то что вы хотите (в реальности, в сложных выражениях, упрощения может и не произойти, так что проверяйте планы в любом случае). Но повторюсь, имхо, куда-то не туда вы копаете.
23 мар 15, 14:19    [17419622]     Ответить | Цитировать Сообщить модератору
 Re: partition with "unit all"  [new]
essbase.ru
Member

Откуда: http://essbase.ru/about
Сообщений: 1407
SomewhereSomehow
Но повторюсь, имхо, куда-то не туда вы копаете.


это просто от того что я sql использую как калькулятор. поэтому мои задачки и кажутся извратом для true-native sql developer )


Всем спасибо за идеи. буду проверять.
23 мар 15, 14:24    [17419661]     Ответить | Цитировать Сообщить модератору
 Re: partition with "unit all"  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
essbase.ru
один вопрос
не понятно выражение a(p) в
  (select 2) a(p) 


- как мне его трактовать для себя ?
Это алиас. Никак не трактуйте, а сделайте проще:
create function dbo.fn3
(
 @p int,
 @v varchar(10)
)
returns table
as
return (
 select
  1 as p, fn1.id, fn1.v
 from
  dbo.fn1(@v) fn1
 where
  @p = 1

 union all

 select
  2 as p, fn2.id, fn2.v
 from
  dbo.fn2(@v) fn2
 where
  @p = 2
);
go

Как уже писал SomewhereSomehow, в случае multi-statement TVF, в плане будут присутствовать обе функции.
Результат какой из них реально читается видно и в плане и в статистике IO.
А еще можно посмотреть для каких из них этот самый результат формируется:
+
use tempdb;
go

create table dbo.t1 (id int primary key, v varchar(10));
create table dbo.t2 (id int primary key, v varchar(10));
go

create function dbo.fn1_m
(
 @v varchar(10)
)
returns @r table (id int primary key, v varchar(10))
as
begin
 insert into @r (id, v) select id, v from dbo.t1 where v = @v;
 return;
end
go 

create function dbo.fn2_m
(
 @v varchar(10)
)
returns @r table (id int primary key, v varchar(10))
as
begin
 insert into @r (id, v) select id, v from dbo.t2 where v = @v;
 return;
end
go 

create function dbo.fn3_m
(
 @p int,
 @v varchar(10)
)
returns table
as
return (
 select
  1 as p, fn1.id, fn1.v
 from
  dbo.fn1_m(@v) fn1
 where
  @p = 1

 union all

 select
  2 as p, fn2.id, fn2.v
 from
  dbo.fn2_m(@v) fn2
 where
  @p = 2
);
go

insert into dbo.t1 select top (100) object_id, 't1' from sys.objects;
insert into dbo.t2 select top (100) object_id, 't2' from sys.objects;
go

declare @v varchar(10), @lsn nvarchar(30);
select @lsn = max([Current LSN]) from sys.fn_dblog(null, null)

set statistics xml, io on;
select @v = v from dbo.fn3_m(1, 't1');
set statistics xml, io off;

select
 object_name(c.object_id), count(*) as [rows inserted]
from
 sys.columns c join
 sys.partitions p on p.object_id = c.object_id join
 sys.allocation_units au on au.container_id = p.partition_id join
 sys.fn_dblog(null, null) l on [Current LSN] > @lsn and l.[AllocUnitId] = au.allocation_unit_id and Operation = N'LOP_INSERT_ROWS'
where
 c.name = 'v' and
 object_name(c.object_id) like '#%'
group by
 object_name(c.object_id);
go

drop function dbo.fn1_m, dbo.fn2_m, dbo.fn3_m;
drop table dbo.t1, dbo.t2;
go
23 мар 15, 15:13    [17420038]     Ответить | Цитировать Сообщить модератору
 Re: partition with "unit all"  [new]
essbase.ru
Member

Откуда: http://essbase.ru/about
Сообщений: 1407
что бы поставить точку в вопросе.
в моих выкрутасах ничего не подошло -

- сделал воркараунд через генерации имени запрпашиваемой таблицы


ЕР
30 мар 15, 18:17    [17451400]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить