OraFAQ Forum: Performance Tuning » select partition issue

Home » RDBMS Server » Performance Tuning » select partition issue (11.2.0.1.0 Windos XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

select partition issue [message #523171]

Thu, 15 September 2011 20:06

andy huang
Messages: 498
Registered: July 2011

Senior Member

Dear all,
I was confused by partitioed table, when i select a partition of table, how does oracle to scan blocks? it scan all blocks of table or scan a single partition blocks only?

SQL> Explain Plan For
  2  Select Count(1) From Tb_Hxl_List Partition(p_L3);

Explained.

SQL> Select * From Table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |     1 |    18   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE        |             |     1 |            |          |
|   2 |   PARTITION LIST SINGLE|             | 33115 |    18   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL   | TB_HXL_LIST | 33115 |    18   (0)| 00:00:01 |

Report message to a moderator

Re: select partition issue [message #523172 is a reply to message #523171]

Thu, 15 September 2011 20:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I was confused by partitioed table, when i select a partition of table, how does oracle to scan blocks?
>it scan all blocks of table or scan a single partition blocks only?

Oracle does what is required to return proper result set.
If result set resides in single partition, then only one partition is accessed.
If result set requires multiple partitions, then multiple partitions are accessed.

Report message to a moderator