Home » SQL & PL/SQL » SQL & PL/SQL » Parallel Full Partition Scan
Parallel Full Partition Scan [message #336114] Thu, 24 July 2008 18:51 Go to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
I have the following table
CREATE TABLE test
(
  file_group_id INTEGER,
  batch_num     INTEGER
)
PARTITION BY LIST (file_group_id)
(
  PARTITION p100 VALUES (100),
  PARTITION p101 VALUES (101),
  PARTITION p102 VALUES (102)
);
I would like to scan a single full partition in parallel. I am trying this.
SELECT /*+ FULL(t) PARALLEL(t)*/
     *
FROM test t
WHERE file_group_id = 101;
It is scanning in parallel, but I think it is scanning the whole table. Does anybody know what hints I should be using?

Also does anybody know which Oracle reference the full list of hints and their usage is in. I am looking at Performance Tuning Guide. It has a list of all the hints but it seems pretty sparse on how they are used.
Re: Parallel Full Partition Scan [message #336122 is a reply to message #336114] Thu, 24 July 2008 22:31 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It will scan only the one partition.

It LOOKS like it is scanning the whole table in the Explain Plan until you look at the PSTART and PSTOP columns - they will tell you only one partition is scanned.

You only get the PARTITION LIST SINGLE PARTITION entry in the plan when Oralce knows ONE partition will be scanned, but not WHICH ONE. eg. file_group_id = :var

Ross Leishman
Re: Parallel Full Partition Scan [message #336129 is a reply to message #336122] Thu, 24 July 2008 23:42 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Thanks Ross. I knew you would know this one.
Re: Parallel Full Partition Scan [message #336177 is a reply to message #336122] Fri, 25 July 2008 02:13 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I think in the mentioned query the reason oracle is not giving the following path "PARTITION LIST SINGLE PARTITION" in the explain plan is because of the parallel hint.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

  1  explain plan
  2  for
  3  SELECT /*+ FULL(t) */
  4       *
  5  FROM test t
  6* WHERE file_group_id = 101
SQL> /
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 213508695

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    26 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE|      |     1 |    26 |     2   (0)| 00:00:01 |     2 |     2 |
|   2 |   TABLE ACCESS FULL   | TEST |     1 |    26 |     2   (0)| 00:00:01 |     2 |     2 |
----------------------------------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
-----
   - dynamic sampling used for this statement

13 rows selected.

  1  explain plan
  2    for
  3    SELECT /*+ FULL(t) PARALLEL(t)*/
  4       *
  5  FROM test t
  6* WHERE file_group_id = 101
SQL> /

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3688626617

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |    26 |     2   (0)| 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |     1 |    26 |     2   (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |     1 |    26 |     2   (0)| 00:00:01 |     2 |     2 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| TEST     |     1 |    26 |     2   (0)| 00:00:01 |     2 |     2 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

15 rows selected.

SQL> exec :v_number := 101;

PL/SQL procedure successfully completed.

SQL> explain plan
  2  for
  3  select /*+ full(t) parallel(t) */
  4  *
  5  from test t
  6  where file_group_id = :v_number;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3511416583

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |    26 |     2   (0)| 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |     1 |    26 |     2   (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |     1 |    26 |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| TEST     |     1 |    26 |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

15 rows selected.

SQL> exec :v_number := 101;

PL/SQL procedure successfully completed.

SQL> explain plan
  2  for
  3  select /*+ full(t) */
  4  *
  5  from test t
  6  where file_group_id = :v_number;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 816759895

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    26 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE|      |     1 |    26 |     2   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   TABLE ACCESS FULL   | TEST |     1 |    26 |     2   (0)| 00:00:01 |   KEY |   KEY |
----------------------------------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
-----
   - dynamic sampling used for this statement

13 rows selected.

Regards

Raj

[Updated on: Fri, 25 July 2008 02:15]

Report message to a moderator

Previous Topic: Catalogs in oracle?
Next Topic: Altering Table with an Indexed Column
Goto Forum:
  


Current Time: Thu Dec 08 18:19:39 CST 2016

Total time taken to generate the page: 0.06511 seconds