5 Replies Latest reply: Aug 26, 2014 4:28 PM by 2738642 RSS

    Question about Partitioning

    2738642

      Hi guys

       

      I am doing some practices in Oracle 11g about partitioning. I have built some tables, etc..

      But I want to be sure in what partitions are some rows. I mean. I want to know in what partition is each row I am inserting.

      With what views I can see that information ?

       

      Thanks

       

      Syed Lal

        • 1. Re: Question about Partitioning
          sol.beach

          post CREATE TABLE statement for partitioned table

          • 2. Re: Question about Partitioning
            2738642

            I will post the code here but.. the code is not related to my question. My question is how I can verify my rows are being inserted in the partitions I want..

             

            CREATE TABLE sales

              ( prod_id NUMBER(6)

              , cust_id NUMBER

              , time_id DATE

              , channel_id CHAR(1)

              , promo_id NUMBER(6)

              , quantity_sold NUMBER(3)

              , amount_sold NUMBER(10,2)

              )

            STORAGE (INITIAL 100K NEXT 50K) LOGGING

            PARTITION BY RANGE (time_id)

            ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))

              TABLESPACE tsa STORAGE (INITIAL 20K NEXT 10K)

            , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))

              TABLESPACE tsb

            , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))

              TABLESPACE tsc

            , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))

              TABLESPACE tsd

            )

            ENABLE ROW MOVEMENT;

            • 3. Re: Question about Partitioning
              rp0428

              You just query the data for a partition and see if it is the data that should be in there.

              SELECT * FROM sales PARTITION (sales_q3_2006)

              Examine the result set and see if that is the right data.

              • 4. Re: Question about Partitioning
                Bhanu.Oradba-Oracle

                Using a view you can only see the partition info like high value, partition position, num_rows but you don't get the entire row info.

                 

                We can do one thing here, while inserting the row check the value of the partition key column. Using the value we can check in user_tab_partitions to identify the partition.

                 

                For eg:

                select partition_name, partition_position, high_value, num_rows, is_nested from user_tab_partitions

                where table_name='<table_name>' ;

                 

                Here high value will show which row falls into which partition. It's slightly different for range, list, and interval partitions.

                 

                I hope it would be useful.

                 

                Thanks,

                Bhanu

                • 5. Re: Question about Partitioning
                  2738642

                  Excellent answer rp0428

                   

                  I will test it.. Thank you for helping me!