Partitioned table

[sonaliak]

I read that making partitioned table improves performance for large data tables with frequent DMLs.
I also read that when you have to do large deletes(not all data) on a table dropping a partition will be fastest approach.
Can I drop a partition from inside PL/SQl block(stored procedure as it will be getting data from procedure and I would like to clear it at the end of procedure- just for that session).
I know you cannot do DML in Pl/sql. I am using Oracle8. Are partitions treated seperately ?
Is there any thumb rule as to when one should or should not do a partition and what column and values will go in partition, is it PK column based ?
Does it use same index across the partition ?


thanks a lot
Sonali

[marist89]

[QUOTE][i]Originally posted by sonaliak [/i]
[B]I read that making partitioned table improves performance for large data tables with frequent DMLs.
[/B][/QUOTE]
Some times, but not all. If all your partitions are in the same tablespace on the same physical disk, the performance is similar (as compared to an indexed read).

[QUOTE][B]
I also read that when you have to do large deletes(not all data) on a table dropping a partition will be fastest approach.
Can I drop a partition from inside PL/SQl block(stored procedure as it will be getting data from procedure and I would like to clear it at the end of procedure- just for that session).
[/B][/QUOTE]
Can you drop a partition from inside PL/SQL? Sure, you have to use Dynamic SQL, but it can be done. I'm not sure what you mean by the second part.

[QUOTE][B]
I know you cannot do DML in Pl/sql. I am using Oracle8.
[/B][/QUOTE]
Not true. PL/SQL is mostly DML operations.

[QUOTE][B]
Are partitions treated seperately ?
Is there any thumb rule as to when one should or should not do a partition and what column and values will go in partition, is it PK column based ?
Does it use same index across the partition ?
[/B][/QUOTE]
If you can take advantage of partitioning, you should use it. Otherwise, you shouldn't. For example, if you have a table that is partitioned by the PK and most of the table accesses are via the PK, Partitioning probably won't help you. If you have a table that can be partitioned by a date value and most of your queries span multiple partitions and all the partitions are in the same tablespace, partitioning will help slightly. If you have a tables that can be partitioned by a date value and most of your queries span multiple partitions and all the partitions are in different tablespaces on different devices, partitioning will help greatly.

Both tables and indexes can be partitioned.

[sonaliak]

My mistake, I wanted to say
You cannot do DDL in Pl/sql. I am using Oracle8.
So can I drop partition from inside a procedure which is DDL ?
I have never been successful with using dynamic sql in oracle8 procedure.
Can you give me ex. of one to create or drop a table(say).

[marist89]

system@dev815nt.us> create table xyz (x char(1));

Table created.

system@dev815nt.us> declare
2 mySQL varchar2(222);
3 begin
4 mySQL := 'drop table xyz';
5 execute immediate mySQL;
6 end;
7 /

PL/SQL procedure successfully completed.

[sonaliak]

Thanks a lot,
But I think I cannot do this in Oracle 8 or may be I am missing something.

SQLWKS> create table xyz (x char(1));
Statement processed.
SQLWKS> declare mySQL varchar2(222);
2> begin
3> mySQL := 'drop table xyz';
4> execute immediate mySQL;
5> end;
6> /
execute immediate mySQL;
*
ORA-06550: line 4, column 10:
PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:

:= . ( @ % ;

[marist89]

execute immediate is an 8.1 feature. It looks like you are running 8.0.x? If so, you have to use the clunky DBMS_SQL package. ( [url]http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a76936/dbms_sql.htm#998100[/url] )