select from partition table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: select from partition table

  1. #1
    Join Date
    Jan 2001
    Posts
    72
    Hello,
    I have a table that I was able to cout the number of rows e.g

    select count(*) from emp

    This emp table has been partitioned on the sal column into months. Each partition on the sal column
    is called:
    sal_jan2000
    sal_feb2000
    sal_mar2000
    sal_apr2000
    sal_may2000
    sal_june2000,etc

    My question is how do I count the number of rows from each partition? Is that possible?
    If so how? Can anybody do an example?

    Thanks

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    select count(*) from emp partition (sal_jan2000) will get the info from one partition.

    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Posts
    212
    select count(*) from emp partition (sal_jan2000 )



  4. #4
    Join Date
    Jan 2001
    Posts
    72
    Thanks for the prompt reply.
    I tried the earlier suggested method to this particular scenario below but it did not work.

    Below is the exact scenario:

    I have a table called SALES and it has been altered and partitions have been added to it. This is the way it was altered to add the partitions like I have below:


    alter table sales
    add partition jan_2000 values less than ('01-feb-2000')

    How do I count all the rows in jan_2000 partition using select count(*)?

  5. #5
    Join Date
    Nov 2000
    Posts
    212
    >I tried the earlier suggested method to this particular >scenario below but it did not work.
    How it did not worked: some Oracle version does not support this syntax or has bugs (version 815 hasthe syntax. As for bugs, there are some, like CBO and RULE based optimizer modes returns different number of rows.)


  6. #6
    Join Date
    Jan 2001
    Posts
    72
    Okay lets start again assuming I never asked the previous question.

    This is the query that I got when I selected table_name, Partition_name, tablespace_name from dba_tab_partition

    TABLE_NAME PARTITION_NAME TABLESPACE_NAME
    --------------- --------------------- ----------------------
    SALES SALES_JAN2000 SALES_TABLESPACE


    How do I count the number of rows in partition sales_jan2000?

    NOTE: I am running 8.I.5 on Sun solaris

    [Edited by mybear on 02-01-2001 at 07:55 AM]

  7. #7
    Join Date
    Nov 2000
    Posts
    212
    NORMALLY it works:
    select count(*) from SALES
    partition (SALES_JAN2000)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width