Solved

TO find MAX Partition of table

Posted on 2011-02-17
8
1,739 Views
Last Modified: 2012-06-27
Hi,

I would appreciate if I can get help in finding max partition of table which has date.

Ex: table name xyz partitioned by date from dec 1 to dec 30 like below
P_20101201, .....until.....P_20101230. Lets consider that only until the P_20101220 has data and other partitions starting from P_20101221 is empy. no data. So what I need here is MAX partition which has data. In the above example that would be P_20101220.

I need a sql, pl/sql  blocl or proc....anything would be fine...
0
Comment
Question by:pal2k
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 34917962
make sure your statistics are up to date then try this...

I'm assuming all of your partition names follow the format you have specified
if not, the ordering won't work



ee.txt
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34918544
select * from user_tab_partitions
where table_name = 'xyz'
order by high_value

--> the high_value will indicate the partition with the highest value
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34918630
http:#34918544  won't work.


high_value is a LONG column,  you can't order by that
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 34918640
you'll get a syntax error if you even try it  (ORA-00997)
0
 
LVL 18

Expert Comment

by:sventhan
ID: 34918859
How about Partition_position column?

select max(partition_position) from user_tab_partitions where table_name = YourTabName
0
 
LVL 32

Accepted Solution

by:
awking00 earned 167 total points
ID: 34926032
See attached.
query.txt
0
 
LVL 5

Assisted Solution

by:manzoor_dba
manzoor_dba earned 166 total points
ID: 34942798
Hi,

You can use the below pl/sql block the get the result you need.



Thanks..
declare
type tt is ref cursor;
kk   tt;
stmt 	  varchar2(500);
dt_alone  date;
mm 	  number;
part_name varchar2(30);
begin
      select max(partition_name) into part_name from user_tab_partitions where table_name = 'TEST_PART';
       loop
       open  kk for 'select count(*) from test_part partition ( ' || part_name || ' ) ';
       fetch kk into mm;
       close kk;
       exit when mm > 0;
         if mm = 0 then
           dt_alone  := to_date(substr(part_name,3,8),'YYYYMMDD');
           dt_alone  := dt_alone - 1;
           part_name := 'P_' || to_char(dt_alone,'YYYYMMDD');
           end if;
     end loop;
       dbms_output.put_line('Max Part Name ' || part_name);
       dbms_output.put_line('count ' || mm);
end;
/

Open in new window

0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 167 total points
ID: 34943110
if you must iterate though all partitions then I suggest NOT using count and simply check for existence

something like this...

select null from your_table partition (some_partition) where rownum = 1

if that returns a row then your partition has data,  if it generates a no_data_found then your partition does not have data.

or, if you don't want to catch an exception then

select count(*) from your_table partition (some_partition) where rownum = 1

this will only count 1 row which is sufficient to prove existence of data.

If your partitions are large, this can be significant time savings.



0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question