Toad World® Forums

how to speed up creation of ddl from partitioned/subpartitioned table



DDL Creattion from partitioned/subpartitioned table takes a long time for my. I’m making ddl from tables but in pl/sql programs, for example in Ora 10g database,ddl from simple subpartitioned table with about 30 subpartitions takes about 50s for sqlnav. My program generates ddl in below 1s. So , i traced creation of ddl and I observed that generating values list takes almost all time of creation ddl. Building values list must be done in loop of single statements, because values are stored in long column, so it means that every single select is important. I have table with tousants of subartitions, i don’t want even try generate ddl from this!

Two things i notced, first select for values should be done with bind variables, it would be less expensive or database and speeds response. Second, I observed in 10G that sometimes selects from dictionaries are slow. Usually setting RULE hint helps! In this way try generate values list using RULE hint. For exmple try this sql for any subpartitioned table:

  1. select /*+ rule */ high_value from all_tab_subpartitions
    where subpartition_name = :subpartition_name and table_owner = :table_owner and
    table_name = :table_name

2.(this is generated by sqlnav, of course without bind variables)
select high_value from all_tab_subpartitions
where subpartition_name = :subpartition_name and table_owner = :table_owner and
table_name = :table_name

Which one is faster? Of corse you must put your variables into sql.
Test it …
Regards Piter


Hi Piter,

Thanks for sending in the info and your feedback.

We will raise a request on your behalf and will have Bernard to look into this for the next release, we will try to get this in an early Beta build post 5.5 for you.

Thanks and regards,


Easiest test;

  1. Extract ddl from subpartitioned table (if more subpartitions than better results), and measure generating time.
  2. Execute
    alter session set optimizer_mode=RULE

then extract ddl once again, measure time! Which one mode is faster…

Regards Piter