1 2 Previous Next 18 Replies Latest reply: Jan 5, 2006 7:56 AM by Michel SALAIS RSS

    Access to HASH PARTITION

    416565
      Hi all,
      I'm trying to find a way to determine the partition number from the partitioning key value.

      Having a simple object table like:

      create type mytype as object (mykey number(12,0), mydata varchar2(32))
      final
      /

      create table mytable of mytype
      ( constraint mytable_pk primary key (mykey) )
      object identifier is primary key
      partition by hash (mykey)
      (
      partition mypart_01,
      partition mypart_02,
      partition mypart_03,
      partition mypart_04
      )
      /

      I would like to be able to calculate the partition number from the value provided for 'mykey'.

      Is there any way to do this?

      Thanks,
      Riccardo
        • 1. Re: Access to HASH PARTITION
          Nicolas.Gasparotto
          Hi,

          You can use EXPLAIN PLAN to know where goes the row :
          SQL> create type mytype as object (mykey number(12,0), mydata varchar2(32))
            2  final
            3  /

          Type created.

          SQL>
          SQL> create table mytable of mytype
            2  ( constraint mytable_pk primary key (mykey) )
            3  object identifier is primary key
            4  partition by hash (mykey)
            5  (
            6  partition mypart_01,
            7  partition mypart_02,
            8  partition mypart_03,
            9  partition mypart_04
          10  )
          11  /

          Table created.

          SQL> insert into mytable values (1,'you');

          1 row created.

          SQL> @$ORACLE_HOME/rdbms/admin/utlxplan

          Table created.

          SQL> explain plan for select * from mytable where mykey = 1;

          Explained.

          SQL> @$ORACLE_HOME/rdbms/admin/utlxpls

          PLAN_TABLE_OUTPUT
          --------------------------------------------------------------------------------------------------

          --------------------------------------------------------------------------------------------------
          | Id  | Operation                          |  Name       | Rows  | Bytes | Cost  | Pstart| Pstop |
          --------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT                   |             |     1 |    31 |     1 |       |       |
          |   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| MYTABLE     |     1 |    31 |     1 | 4 | 4 |
          |*  2 |   INDEX RANGE SCAN                 | MYTABLE_PK  |     1 |       |     1 |       |       |
          --------------------------------------------------------------------------------------------------

          Predicate Information (identified by operation id):
          ---------------------------------------------------

          PLAN_TABLE_OUTPUT
          --------------------------------------------------------------------------------------------------

             2 - access("MYTABLE"."MYKEY"=1)

          Note: cpu costing is off

          15 rows selected.

          SQL> insert into mytable values (2,'two');

          1 row created.

          SQL> explain plan for select * from mytable where mykey = 2;

          Explained.

          SQL> @$ORACLE_HOME/rdbms/admin/utlxpls

          PLAN_TABLE_OUTPUT
          --------------------------------------------------------------------------------------------------

          --------------------------------------------------------------------------------------------------
          | Id  | Operation                          |  Name       | Rows  | Bytes | Cost  | Pstart| Pstop |
          --------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT                   |             |     1 |    31 |     1 |       |       |
          |   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| MYTABLE     |     1 |    31 |     1 | 3 | 3 |
          |*  2 |   INDEX RANGE SCAN                 | MYTABLE_PK  |     1 |       |     1 |       |       |
          --------------------------------------------------------------------------------------------------

          Predicate Information (identified by operation id):
          ---------------------------------------------------

          PLAN_TABLE_OUTPUT
          --------------------------------------------------------------------------------------------------

             2 - access("MYTABLE"."MYKEY"=2)

          Note: cpu costing is off

          15 rows selected.

          SQL> explain plan for select * from mytable;

          Explained.

          SQL> @$ORACLE_HOME/rdbms/admin/utlxpls

          PLAN_TABLE_OUTPUT
          ------------------------------------------------------------------------------------

          ------------------------------------------------------------------------------------
          | Id  | Operation            |  Name       | Rows  | Bytes | Cost  | Pstart| Pstop |
          ------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT     |             |   328 | 10168 |     2 |       |       |
          |   1 |  PARTITION HASH ALL  |             |       |       |       |     1 |     4 |
          |   2 |   TABLE ACCESS FULL  | MYTABLE     |   328 | 10168 |     2 |     1 |     4 |
          ------------------------------------------------------------------------------------

          Note: cpu costing is off

          10 rows selected.

          SQL>
          HTH,

          Nicolas.
          • 2. Re: Access to HASH PARTITION
            Laurent Schneider
            SQL>  select mytable.*, object_name, subobject_name from mytable, user_objects  where dbms_rowid.ROWID_OBJECT(mytable.rowid) = object_id;
                 MYKEY MYDATA                           OBJECT_NAME          SUBOBJECT_NAME
            ---------- -------------------------------- -------------------- ------------------------------
                     1 you                              MYTABLE              MYPART_04
                     2 me                               MYTABLE              MYPART_03
            • 3. Re: Access to HASH PARTITION
              Nicolas.Gasparotto
              Laurent, you're always on top. La classe !

              Nicolas.
              • 4. Re: Access to HASH PARTITION
                Laurent Schneider
                thanks for your comment, ça fait plaisir :-)
                • 5. Re: Access to HASH PARTITION
                  416565
                  Nicolas, Laurent,

                  Thanks both for your suggestions.

                  Riccardo

                  Message was edited by:
                  riccardo
                  • 6. Re: Access to HASH PARTITION
                    Michel SALAIS
                    Hi,
                    I think a litle correction is necessary ...
                    replace object_id by data_object_id
                    SQL> select object_id, data_object_id
                      2  from user_objects
                      3  where object_name = 'BONUS';

                    OBJECT_ID DATA_OBJECT_ID
                    ---------- --------------
                         31701          32440

                    1 ligne selectionnee.

                    SQL>
                    SQL> select bonus.*
                      2       , object_name
                      3       , subobject_name
                      4  from bonus, user_objects
                      5  where dbms_rowid.ROWID_OBJECT(bonus.rowid) = object_id;

                    aucune ligne selectionnee

                    SQL>
                    SQL> select bonus.*
                      2       , object_name
                      3       , subobject_name
                      4  from bonus, user_objects
                      5  where dbms_rowid.ROWID_OBJECT(bonus.rowid) = data_object_id;

                    ENAME      JOB              SAL       COMM OBJECT_NAM SUBOBJECT_
                    ---------- --------- ---------- ---------- ---------- ----------
                    SMITH      CLERK            802            BONUS
                    ALLEN      SALESMAN        1602        300 BONUS
                    Message was edited by:
                    Michel SALAIS

                    A side note: I dont know why [ pre ] and [ /pre ] don't work any more ...
                    • 7. Re: Access to HASH PARTITION
                      Laurent Schneider
                      thanks for the update
                      A side note: I dont know why [ pre ] and [ /pre ] don't work any more ...
                      well, did you change your browser/os? on Linux with firefox, the pre is not monospaced by me
                      • 8. Re: Access to HASH PARTITION
                        Nicolas.Gasparotto
                        Michel,

                        I retested the Laurent's solution, and it works fine, in my case object_id = data_object_id
                          1  select mytable.*, object_name, object_id,data_object_id,subobject_name
                          2  from mytable, user_objects
                          3* where dbms_rowid.ROWID_OBJECT(mytable.rowid) = data_object_id
                        SQL> /

                             MYKEY MYDATA
                        ---------- --------------------------------
                        OBJECT_NAME
                        ---------------------------------------------------------------------------
                        OBJECT_ID DATA_OBJECT_ID SUBOBJECT_NAME
                        ---------- -------------- ------------------------------
                                 1 you
                        MYTABLE
                             71904          71904 MYPART_04
                        I have to say that I have some difficulties to understand the difference between these two columns...

                        Nicolas.
                        • 9. Re: Access to HASH PARTITION
                          Michel SALAIS
                          object_id is the identifier of the obejct wether or not it has a data segment. data_object_id is the identifier of the segment. Most objects have no segments and they have then object_id without data_objet_id ...
                          • 10. Re: Access to HASH PARTITION
                            Michel SALAIS
                            Yes, I recently updated my Firefox from 1.0.6 to 1.5.
                            • 11. Re: Access to HASH PARTITION
                              Nicolas.Gasparotto
                              Oh yes, all become clear now. Thanks Michel...

                              Nicolas.

                              PS-out of subject : I have a new PC (le père Noël a pensé à moi) instead of my old PII 350, I will test 10gR2 in a few days... :-)
                              • 12. Re: Access to HASH PARTITION
                                Nicolas.Gasparotto
                                Yes, I recently updated my Firefox from 1.0.6 to 1.5.
                                I have Firefox 1.5 and [pre] and [[b]/pre] work fine.

                                Nicolas.
                                • 13. Re: Access to HASH PARTITION
                                  Laurent Schneider
                                  me too, I received a new notebook (du père Noël) with 2GB ram...
                                  • 14. Re: Access to HASH PARTITION
                                    Nicolas.Gasparotto
                                    ...with hard disk SATA II, and so on...
                                    hmmm, 2Gb, hum, it's better than my 1Gb, "le père Noël" is more generous in Switzerland...

                                    ;-)

                                    Message was edited by:
                                    N. Gasparotto
                                    1 2 Previous Next