• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

sql statement based on partition

hi
    i need to create sql based on the table given below i.e. i need to generate least partition clause
chunk_id      schema_name  in_list    partition_name
1      NAYAGE01      1      P1
2      NAYAGE01      23      P2
3      NAYAGE01      39      P39
4      NAYAGE01      7      P7
5      NAYAGE01      15      P15
6      NAYAGE01      19      P19
7      NAYAGE01      13      P13
8      NAYAGE01      17      P17
9      NAYAGE01      25      P25
10      NAYAGE01      50      P50
11      NAYAGE01      52      P52
12      NAYAGE01      77      P77
0
thota198
Asked:
thota198
  • 9
  • 8
  • 5
1 Solution
 
slightwv (䄆 Netminder) Commented:
>>i need to generate least partition clause

I do not understand.  Please provide more information.

If you mean the min in that list:

select min(partition_name) from whatever_generated_that_list;

0
 
sdstuberCommented:
If you're looking for the row with the smallest partition name, try this...

select * from
(select t.*, row_number() over(order by to_number(substr(partition_name,2) ) rn from yourtable t)
where rn = 1



the to_number and substring are to correct mis-comparisons of numeric data as strings.  So, I sort the partitions by the numeric piece, skipping the "P"
0
 
thota198Author Commented:
write procedure or func based on the sql in the table given generating least partition clause
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
slightwv (䄆 Netminder) Commented:
>>write procedure or func based on the sql in the table given generating least partition clause

Is that a spec or task definition you have been given and need to create the work product for it?

Anyway, it is not making sense to me.
0
 
sdstuberCommented:
if the query I wrote isn't what you're looking for.

please post what you expect the results to be.  (rows/columns)
0
 
thota198Author Commented:
i will create table based on that sql generating partition clause
0
 
slightwv (䄆 Netminder) Commented:
That still doesn't tell me anything.

I doubt this is really what you are asking but with the vague information you posted:

To create a table with those results:
create table as
select --the select that generated that list
/


0
 
thota198Author Commented:
Write the sql based on generating the list partition clause
0
 
sdstuberCommented:
you keep using the same words.  we're asking for you to tell us something new.


please post the results you want  ( a sql, a function is not a result, it's a method to get a result)  

what results do you want  returned, from the data you have posted above?
0
 
slightwv (䄆 Netminder) Commented:
vague sentences are not getting your point across.

Please provide sample data and expected results.

0
 
thota198Author Commented:
Based on the table that i gave i would like to know how we can build partitions
0
 
slightwv (䄆 Netminder) Commented:
So, you want to take that tale and generate the SQL to actually run the DDL to create a partitioned table?

What type of partition?  range, list, hash, combo???

I'm not seeing the logic in the table you provided.

I should also ask if this is a one-time task or something that you would need to do on an on-going basis since and 'create table' DDL would require dropping the table first.  Probably not something you want to do.
0
 
thota198Author Commented:
we have to generate the sql dynamically  partition is list
this is a one time task
0
 
sdstuberCommented:
when you say partition, what do you mean?

are you asking for PHYSICAL table storage partitions?   -- if this, it can't be dynamic, lists are static

or

are you asking for partitions as in groupings of data in result sets?  -if this,  then I'll go back to same thing I've asked before, please, show expected results
0
 
slightwv (䄆 Netminder) Commented:
>>this is a one time task

Get the syntax from the online docs and type the range values into notepad.  You could have had this done hours ago.

There is no need to create SQL from the table.


http://download.oracle.com/docs/cd/E11882_01/owb.112/e10935/orcl_data_objx.htm#BEHGEGGI

0
 
slightwv (䄆 Netminder) Commented:
So if in_list is the column range for the partition...

Guessing here but this took 3 minutes using copy/paste.

CREATE TABLE NAYAGE01.SOME_TABLE_NAME (
col1   VARCHAR2(30),
col2   DATE,
col3     NUMBER)
PARTITION BY RANGE(col3) (
PARTITION P1 VALUES LESS THAN (2) TABLESPACE part1,
PARTITION P7 VALUES LESS THAN (7) TABLESPACE part7,
PARTITION P13 VALUES LESS THAN (13) TABLESPACE part13,
...
... continue through your list
...
PARTITION P77 VALUES LESS THAN (77) TABLESPACE part77,
PARTITION P_unknown VALUES LESS THAN (MAXVALUE) TABLESPACE part_unk
);

0
 
thota198Author Commented:
if this is not a one time task and i have to do it dynamically generate sql statement with list partition clause how would u do it, do u need any further data to give
0
 
sdstuberCommented:
do it one time manually please.  What are you expecting the results to be?

slightwv showed an example of doing range partitions manually.  What is your list partition supposed to look like?

Do it once to show me what you're trying to get and I can automate it from there
0
 
slightwv (䄆 Netminder) Commented:
>>if this is not a one time task  ...

'new' table or for changes to an existing table?

If new:
Just loop through the values in the table and generate a string containing the SQL I posted with any additional storage parameters you might want and execute it with execute immediate.


If existing:

You would need to loop through user_tab_partitions for the table in question and figure out what were 'new' partitions and what partitions have been deleted/changed in the table values.

Then generate the correct 'alter table' SQL and execute it.

It will be very difficult to do this and you would have to test it quite a bit to make sure it will work for ALL scenarios.
0
 
thota198Author Commented:
I created the following proc as said above according the present scenario
CREATE OR REPLACE PROCEDURE NAYAGE01.build_list_partition_table (i_table IN VARCHAR2) AUTHID CURRENT_USER IS

/******************************************************************************
   NAME:       build_list_partition_table
   PURPOSE:    

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        10/31/2011   shivar01       1. Created this procedure.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     build_list_partition_table
      Sysdate:         10/31/2011
      Date and Time:   10/31/2011, 9:21:36 AM, and 10/31/2011 9:21:36 AM
      Username:        shivar01 (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)


******************************************************************************/
v_table varchar2(100);
v_chunkid run_chunk.chunk_id%type;
v_inlist run_chunk.in_list%type;
v_sql_text varchar2(4000);

 CURSOR cur_tab_ddl(i_table in varchar2) IS
  Select DDL ||' '|| tablespace_name
   FROM table_DDL
   WHERE table_name=i_table;
   
   CURSOR cur_runchunk IS
    SELECT chunk_id, in_list
     FROM run_chunk;
--     WHERE in_list > (SELECT MIN(in_list)
--                      FROM run_chunk);
     
   BEGIN
   
      v_table := UPPER (i_table);
       
      OPEN cur_tab_ddl (v_table);
       
      FETCH cur_tab_ddl
      INTO  v_sql_text;
     
       CLOSE cur_tab_ddl;
       
       
       OPEN cur_runchunk;
       
        FETCH cur_runchunk
         INTO  v_chunkid, v_inlist;
         
        v_sql_text := v_sql_text
            || ' PARTITION BY LIST (mktkey) (PARTITION P_'
            || v_chunkid
            || ' VALUES ('
            || v_inlist
            || ')) ';

         EXECUTE IMMEDIATE v_sql_text;
         
         LOOP
             FETCH cur_runchunk
             INTO v_chunkid, v_inlist;
             
              EXIT WHEN NOT cur_runchunk%FOUND;
             
               v_sql_text :=
                           'ALTER TABLE'
                           || v_table
                           || 'ADD PARTITION p_'
                           || v_chunkid
                           || 'VALUES ('
                           || v_inlist
                           || ')';
                           
                EXECUTE IMMEDIATE v_sql_text;
         END LOOP;
                 
   
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END build_list_partition_table;
/

When i run this proc iam getting the error
Execution (3: 1): ORA-00972: identifier is too long
ORA-06512: at "NAYAGE01.BUILD_LIST_PARTITION_TABLE", line 89
ORA-06512: at line 1

Also in looping can i use the cursor attribute cur_runchunk%FOUND
0
 
slightwv (䄆 Netminder) Commented:
First: in pl/sql the max varchar2 size is 32K.  If your sql_text can exceed that, there are problems.

I think 11gR2 can execute a CLOB but that is going from memory(the docs can confirm).

Second:
Your loop that has this "v_sql_text := v_sql_text
            || ' PARTITION BY LIST (mktkey) (PARTITION P_'"

Doesn't generate valid SQL so the execute immediate will fail.

You need to generate SQL that mirrors the syntax I posted above.

Depending on the 32K limits you might need to create the table first and alter to add partitions in the loop.

This is probably how I would write it.

Third:
You didn't account for: what if the table already exists?
0
 
thota198Author Commented:
it was complete
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 9
  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now