create table emp1

partition by range(sal) (

partition p1 values less than(1000)

, partition p2 values less than(2000)

, partition p3 values less than(3000)

, partition p4 values less than(MAXVALUE) )

as

select * from scott.emp ;

create index emp_sal_idx on emp1(sal) local;

create table dept1 as select * from scott.dept;

alter table dept1 add constraint dept_pk primary key(deptno);

set autotrace traceonly exp;

select /*+ ordered use_nl(d) full(e) parallel(e 2) */ *

from   emp1 e, dept1 d

where  d.deptno = e.deptno

and    e.sal >= 1000 ;

select /*+ ordered use_nl(d) index(e emp_sal_idx)

parallel_index(e emp_sal_idx 3) */ *

from   emp1 e, dept1 d

where  d.deptno = e.deptno

and    e.sal >= 1000 ;

SQL> create table emp1

2  partition by range(sal) (

3    partition p1 values less than(1000)

4  , partition p2 values less than(2000)

5  , partition p3 values less than(3000)

6  , partition p4 values less than(MAXVALUE) )

7  as

8  select * from scott.emp ;

테이블이 생성되었습니다.

SQL>

SQL> create index emp_sal_idx on emp1(sal) local;

인덱스가 생성되었습니다.

SQL>

SQL> create table dept1 as select * from scott.dept;

테이블이 생성되었습니다.

SQL>

SQL> alter table dept1 add constraint dept_pk primary key(deptno);

테이블이 변경되었습니다.

SQL>

SQL> set autotrace traceonly exp;

SQL>

SQL> select /*+ ordered use_nl(d) full(e) parallel(e 2) */ *

2  from   emp1 e, dept1 d

3  where  d.deptno = e.deptno

4  and    e.sal >= 1000 ;

Execution Plan

----------------------------------------------------------

--------------------------------------------------------------------------------

---------------------------------------------

| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)|

Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------

---------------------------------------------

|   0 | SELECT STATEMENT               |          |    11 |  1287 |     3   (0)|

|       |        |      |            |

|   1 |  PX COORDINATOR                |          |       |       |            |

|       |        |      |            |

|   2 |   PX SEND QC (RANDOM)          | :TQ10000 |    11 |  1287 |     3   (0)|

|       |  Q1,00 | P->S | QC (RAND)  |

|   3 |    NESTED LOOPS                |          |    11 |  1287 |     3   (0)|

|       |  Q1,00 | PCWP |            |

|   4 |     PX BLOCK ITERATOR          |          |    11 |   957 |     3   (0)|

2 |     4 |  Q1,00 | PCWC |            |

|*  5 |      TABLE ACCESS FULL         | EMP1     |    11 |   957 |     3   (0)|

2 |     4 |  Q1,00 | PCWP |            |

|   6 |     TABLE ACCESS BY INDEX ROWID| DEPT1    |     1 |    30 |     1   (0)|

|       |  Q1,00 | PCWP |            |

|*  7 |      INDEX UNIQUE SCAN         | DEPT_PK  |     1 |       |     0   (0)|

|       |  Q1,00 | PCWP |            |

--------------------------------------------------------------------------------

---------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

5 - filter("E"."SAL" IS NOT NULL)

7 - access("D"."DEPTNO"="E"."DEPTNO")

Note

-----

- 'PLAN_TABLE' is old version

SQL>

SQL>

SQL> select /*+ ordered use_nl(d) index(e emp_sal_idx)

2             parallel_index(e emp_sal_idx 3) */ *

3  from   emp1 e, dept1 d

4  where  d.deptno = e.deptno

5  and    e.sal >= 1000 ;

Execution Plan

----------------------------------------------------------

--------------------------------------------------------------------------------

-------------------------------------------------------

| Id  | Operation                             | Name        | Rows  | Bytes | Co

st (%CPU)| Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------

-------------------------------------------------------

|   0 | SELECT STATEMENT                      |             |    11 |  1287 |

7   (0)|       |       |        |      |            |

|   1 |  PX COORDINATOR                       |             |       |       |

|       |       |        |      |            |

|   2 |   PX SEND QC (RANDOM)                 | :TQ10000    |    11 |  1287 |

7   (0)|       |       |  Q1,00 | P->S | QC (RAND)  |

|   3 |    NESTED LOOPS                       |             |    11 |  1287 |

7   (0)|       |       |  Q1,00 | PCWP |            |

|   4 |     PX PARTITION RANGE ITERATOR       |             |    11 |   957 |

6   (0)|     2 |     4 |  Q1,00 | PCWC |            |

|   5 |      TABLE ACCESS BY LOCAL INDEX ROWID| EMP1        |    11 |   957 |

6   (0)|     2 |     4 |  Q1,00 | PCWP |            |

|*  6 |       INDEX RANGE SCAN                | EMP_SAL_IDX |     1 |       |

3   (0)|     2 |     4 |  Q1,00 | PCWP |            |

|   7 |     TABLE ACCESS BY INDEX ROWID       | DEPT1       |     1 |    30 |

1   (0)|       |       |  Q1,00 | PCWP |            |

|*  8 |      INDEX UNIQUE SCAN                | DEPT_PK     |     1 |       |

0   (0)|       |       |  Q1,00 | PCWP |            |

--------------------------------------------------------------------------------

-------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

6 - access("E"."SAL">=1000)

8 - access("D"."DEPTNO"="E"."DEPTNO")

Note

-----

- 'PLAN_TABLE' is old version

SQL>

SQL>