DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Specify Partition name dynamically in Select Clause

  1. #1
    Join Date
    Dec 2004
    Posts
    3

    Question Specify Partition name dynamically in Select Clause

    Hi,

    I need to Specify Partition name dynamically in the Select Clause.

    For Example:

    select 'x'
    From emp Partition (to_char(sysdate,'yyyyMM'))


    Is it possible in Oracle. If so, How to Proceed. Or else any work around for it.

    Because My query is taking more time to give the result. I need to restrict to a single partition.

    I require it in a normal SQL and not in PL/SQL. I use this query in Bussiness Objects Report (Free hand sql).

    Plz reply ASAP.

    Thanks In advance.
    Raja
    Last edited by mmvraja; 12-15-2004 at 01:13 PM.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    dynamic sql

    Of course, if you think you need to do this, your partitioning is probably setup wrong.
    Jeff Hunter

  3. #3
    Join Date
    Dec 2004
    Posts
    3
    Hi Jeff,

    I require it in a normal SQL and not in PL/SQL. I use this query in Bussiness Objects Report (Free hand sql).

    In my environment, new partition is created every month and the partition name will be like YYYYMM.

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Wouldn't it be beter to add (say) a BETWEEN condition on the variable on which the table is partitioned?

    If you select on partition then if ever the partitioning was changed you'd get the wrong answer. It you insist on doing it that way, look at Dynamic SQL in the PL/SQL manual.

  5. #5
    Join Date
    Dec 2004
    Posts
    3
    Hi,

    Say for example, the table is partioned based on Join Date.

    1. How do i specify the partition name in WHERE clause dynamically

    2. How do i specify the partition name in FROM clause dynamically

    Iam not using PL/SQL(not required). I use only SQL.

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    (Assuming an index can not be used) if you specify
    WHERE join_date >= x
    AND join_date < y
    (x & y chosen appropriately) then only the relevant partion should be scanned.

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ========
    Say for example, the table is partioned based on Join Date.

    1. How do i specify the partition name in WHERE clause dynamically

    2. How do i specify the partition name in FROM clause dynamically

    Iam not using PL/SQL(not required). I use only SQL.
    =========

    Table Partition is transparent to the end user/developer. If partition key is correct and the column is used in the predicate, oracle automatically does partition pruning. You don't need to specify the partition name dynamically.

    Basically, it seems to be that your system design may be wrong. Go back to drawing board.

    Can you post the SQL statement that you are trying to do in Business Objects?

    Tamil

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