Re: debunking partitioning

:) As soon as I had clicked send .. I kinda guessed (right) what u'r answer would be. Now create a huge hash partitioned table ... gather stats .... (dbms_stats) and create the same as partitioned view (hash partitioning) .. gather stats (dbms_stats)

and then do a query select * from mypartition where partitionedcol = x;

Which one is faster?

.. or actually no ... like Sybrand said ... go ahead and open an iTar (and I don't mean this in a rude way) if you are missing partitioned views so much.
Your last statement is a kinda what partitioning is .. and more (if you mean that oracle itself should create these triggers). And thus I would end my side of the thread.

Anurag

"Mikito Harakiri" <mikharakiri_at_yahoo.com> wrote in message news:bdf69bdf.0212051053.16a86dd0_at_posting.google.com...
> "Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:<uuttcvgh0vm7cd_at_corp.supernews.com>...
> > Ok .. just one example ... tell me how would you do a similar of hash partitioning .. using partitioned views.
>
> Hash partitioning into 3 tables T_1, T_2, T_3:
>
> create view T as
> select * from T_1
> union all
> select * from T_2
> union all
> select * from T_3
>
> CREATE OR REPLACE TRIGGER T_insert
> INSTEAD OF INSERT ON manager_info
> REFERENCING NEW AS n
> FOR EACH ROW
> DECLARE
> rowcnt number;
> BEGIN
> IF dbms_utility.get_hash_value(n.partitioned_col,1,3)=1 THEN
> INSERT INTO T_1;
> ELSE IF =2 THEN
> INSERT INTO T_2;
> ...
> END
>
>
> > Or don't you think a global/local index is easier to maintain than separate indexes on hundreds of other tables.
>
> domain index would do.
>
> > .. or just that seeing one table in your data dictionary makes more sense than seeing hundreds of them.
>
> no difference: there are hundreds of partitions in the dictionary.
>
> If you are complaining about the size of information your dictionary
> query returns, you need to fix the query -- for example add
> aggregation so that it will return just one string
>
> T1..T100
>
> instead of full list of tables
>
> > You think creating and maintaining a trigger is worth the headache?
>
> It could be hidden below the hood. I, as a user, am not supposed to
> develop all the partition mechanics, right? And also partition syntax
> could definetely be more concise than partitioned views. This is
> something that users would be happy delegating to oracle to design.
Received on Fri Dec 06 2002 - 00:59:06 CST