Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: partitioned tables and the optimizer's behaviour
e.g
select * from table A PARTITION (p1)
where condtn
union
select * from table A PARTITION (p2)
where condtn;
Currently, the use of partition-extended and subpartition-extended table names has the following restrictions:
No remote tables: A partition-extended or subpartition-extended table name cannot contain a database link (dblink) or a synonym that translates to a table with a dblink. To use remote partitions and subpartitions, create a view at the remote site that uses the extended table name syntax and then refer to the remote view.
No direct PL/SQL support: A SQL statement using the extended table name syntax cannot be used in a PL/SQL block, although it can be used through dynamic SQL by using the DBMS_SQL package. To refer to a partition or subpartition within a PL/SQL block, use views that in turn use the extended table name syntax.
No synonyms: A partition or subpartition extension must be specified with a base table. You cannot use synonyms, views, or any other objects.
-----Original Message-----
Sent: Monday, July 15, 2002 3:23 PM
To: ORACLE-L
Cc: andreyb
Dear list !
i have a table , which is partitioned by a date column (let's call it
ADAY)
Each day is a separate partition.
I have this sort of a query :
select trunc(ADAY,'DD') , count(...),sum(...),...
from THETABLE
where ADAY >= :start_date
group by trunc(ADAY,'DD')
I.e. i want to perform some calculations for the last couple of days
(governed by the :start_date - passed as a parameter, not fixed).
Now , i want to force the query to perform the Full scan (not an index
scan)
only of the required partitions (i.e. for the required days).
How can i do that , please ?
TIA DBAndrey
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
INET: andreyb_at_elrontelesoft.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: cosltemp-g.manoj_at_orbitech.co.in
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Jul 15 2002 - 07:03:29 CDT