Specify Partition name dynamically in Select Clause

[mmvraja]

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

[marist89]

dynamic sql

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

[mmvraja]

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.

[DaPi]

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.

[mmvraja]

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.

[DaPi]

(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.

[tamilselvan]

========
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