oracle 动态选择分区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22357232/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamically selecting partitions
提问by filippo
I have a table with a few hundred partitions and I am generally interested on the latest 35.
我有一张有几百个分区的表，我通常对最新的 35 个分区感兴趣。
Accordingly I am trying to create views which would access these dynamically. i.e. always use the latest in case ones are created.
因此，我正在尝试创建可以动态访问这些视图的视图。即总是使用最新的以防万一。
The query:
查询：
select PARTITION_NAME,
PARTITION_POSITION,
NUM_ROWS,
AVG_ROW_LEN
from all_tab_partitions
where
table_name = 'MY_TABLE'
AND PARTITION_NAME <> 'P_LAST'
AND PARTITION_POSITION < (SELECT MAX(PARTITION_POSITION)
FROM all_tab_partitions) - 35
order by 2 DESC
;
Seems to return me the partition names I'm interested, however, I don't manage to use it's results to select the partitions. e.g.:
似乎向我返回了我感兴趣的分区名称，但是，我无法使用它的结果来选择分区。例如：
CREATE OR REPLACE VIEW MY_VIIEW AS
WITH t AS ( [Above query] )
SELECT * FROM
MY_TABLE PARTITION (SELECT /*+ FIRST_ROWS(1) */ PARTITION_NAME
from t);
(not the actual view, just an example)
（不是实际视图，只是一个例子）
So how do I do that? How do I create a view which will acess always the latest partition (execpt of "MAX")?
那我该怎么做呢？如何创建一个始终访问最新分区的视图（“MAX”的执行）？
I am using Oracle 10g
我正在使用 Oracle 10g
thanks
谢谢
采纳答案by Falco
May be you can construct view's query using batch of union all statements with partition name in each statement, e.g.
可能您可以在每个语句中使用带有分区名称的一批 union all 语句构造视图查询，例如
create view p as
select * from my_table partition (part1)
union all
select * from my_table partition (part1)
...
union all
select * from my_table partition (part35)
回答by Naeel Maqsudov
You can do it using PL/SQL only
您只能使用 PL/SQL 来完成
create or replace package my_table_ is
type t_records is table of my_table%rowtype;
function getpart(c_parts sys_refcursor) return t_records pipelined;
end;
create or replace package body my_table_ is
function getpart(c_parts sys_refcursor) return t_records pipelined is
v_partition all_tab_partitions.partition_name%type;
v_row my_table%rowtype;
c_mytab sys_refcursor;
begin
loop
fetch c_parts into v_partition;
exit when c_parts%notfound;
open c_mytab for 'select * from my_table partition ('||v_partition||')';
loop
fetch c_mytab into v_row;
exit when c_mytab%notfound;
pipe row (v_row);
end loop;
end loop;
end;
end;
Now you can
现在你可以
select * from table(my_table_.getpart(cursor(<QUERY_RETURNING_PARTITION_NAMES>)));
回答by Falco
Ok... I don't think your can use the Partition-Names, but you can use the Starting-Values of the Partitions to select the Data matching these Partitions...
好的...我认为您不能使用分区名称，但是您可以使用分区的起始值来选择与这些分区匹配的数据...
So you View would look like this:
所以你的视图看起来像这样：
SELECT * FROM my_table WHERE date_col > get_part_limit( 'my_table', 35 ):
Where date_col is the column you use for partitioning - and get_part_limit is a stored function you write like this:
其中 date_col 是您用于分区的列 - get_part_limit 是您这样编写的存储函数：
...
BEGIN
SELECT high_value FROM all_tab_partitions
INTO local_var
WHERE table_name = parameter_name
AND PARTITION_POSITION = MAX... - 35
EXECUTE IMMEDIATE 'SELECT '||local_var||' FROM DUAL' INTO local_return_value;
RETURN local_return_value;
END;
回答by vav
partitions are designed to be transparent for the data, so when you write a query, you simply don't know how your data is stored.
分区被设计为对数据透明，因此当您编写查询时，您根本不知道您的数据是如何存储的。
I see only one possibility to hit a particular partition: your WHERE clause should match values to the partitioned columns of latest (or latest 5) partition.
我只看到命中特定分区的一种可能性：您的 WHERE 子句应该将值与最新（或最新 5 个）分区的分区列匹配。
Next question is to build this WHERE clause on the fly. You already know that there is plenty of information in oracle dictionary. So you will read that and create a constructor to convert metadata conditions back into SQL.
下一个问题是动态构建这个 WHERE 子句。你已经知道oracle字典中有很多信息。因此，您将阅读该内容并创建一个构造函数以将元数据条件转换回 SQL。
回答by eliatou
irl we do exactly the same thing and use falco's solution like. Here is my code:
irl 我们做完全相同的事情并使用 falco 的解决方案。这是我的代码：
create or replace function longToDate( myOwner varchar2,
mytable_name in varchar2,
mypartition_name in varchar2
) return date
as
cDate date;
cvar varchar2(1024);
rq varchar2(1024);
infiniteValue EXCEPTION;
PRAGMA EXCEPTION_INIT(infiniteValue, -00904);
begin
select high_value into cvar FROM dba_tab_partitions t where t.table_owner=myOwner and table_name=mytable_name and partition_name=mypartition_name;
rq:='select '||cvar||' from dual';
execute immediate rq into cDate;
return cdate;
EXCEPTION
WHEN infiniteValue
then return'01 jan 3000';
when others
then return null;
end longToDate;
Ant the view is something like this
蚂蚁的观点是这样的
create or replace view last_35 as
with maxdate as
(select longToDate(p.table_owner,p.table_name,p.partition_name) mydate,
rank()over(order by p.partition_position desc) mypos,
p.* from all_tab_partitions p
where p.table_name='MY_TABLE'
)
select /*+full(a)*/* from MY_TABLE a, maxdate
where MY_TABLE.partition_name>maxdate.mydate
and maxdate.mypos=35
