[실무오라클힌트]USE_NL_WITH_INDEX, Oracle Hint,중첩루프조인,NetedLoop join, inner table,[자바개발자교육/자바교육/자바강좌/자바교육잘하는곳/자바교육추천/자바실무교육/JAVA/JAVA교육/JAVA학원/JAVA실무교육]
중첩루프조인(Nested Loop Join)에서 USE_NL_WITH_INDEX에 기술한 내부(inner)테이블의 과 조인시 인덱스를 사용하여 조인하도록 명시하는 힌트이다.
If no index is specified, then the optimizer must be able to use some index with at least one join predicate as the index key.
If an index is specified, then the optimizer must be able to use that index with at least one join predicate as the index key.
[예]
SELECT /*+ USE_NL_WITH_INDEX(l item_product_ix) */ *
FROM orders h, order_items l
WHERE l.order_id = h.order_id
AND l.order_id > 2400;
[실습]
실습을 위한 테이블은 다음 URL참조
http://www.oraclejavanew.kr/bbs/board.php?bo_table=LecHINT&wr_id=117
SQL> create index idx_myemp1_ename on myemp1(ename);
인덱스가 생성되었습니다.
경 과: 00:01:36.53
SQL>
SQL> create index idx_myemp1_old_ename on myemp1_old(ename);
인덱스가 생성되었습니다.
경 과: 00:00:01.76
SQL> select /*+ ordered use_nl(e1) */
2 count(e1.ename)
3 from myemp1_old e2, myemp1 e1
4 where e1.empno = e2.empno
5 and e1.ename = e2.ename ;
경 과: 00:00:09.01
Execution Plan
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2002K (
1)| 06:40:29 |
| 1 | SORT AGGREGATE | | 1 | 39 |
| |
| 2 | NESTED LOOPS | | | |
| |
| 3 | NESTED LOOPS | | 999K| 37M| 2002K (
1)| 06:40:29 |
| 4 | TABLE ACCESS FULL | MYEMP1_OLD | 999K| 17M| 1596 (
1)| 00:00:20 |
|* 5 | INDEX UNIQUE SCAN | SYS_C0012349 | 1 | | 1 (
0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| MYEMP1 | 1 | 21 | 2 (
0)| 00:00:01 |
--------------------------------------------------------------------------------
SQL> select /*+ ordered use_nl_with_index(e1 idx_myemp1_ename) */
2 count(e1.ename)
3 from myemp1_old e2, myemp1 e1
4 where e1.empno = e2.empno
5 and e1.ename = e2.ename ;
COUNT(E1.ENAME)
---------------
199998
경 과: 00:00:53.20
Execution Plan
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 3002
K (1)| 10:00:33 |
| 1 | SORT AGGREGATE | | 1 | 39 |
| |
| 2 | NESTED LOOPS | | | |
| |
| 3 | NESTED LOOPS | | 999K| 37M| 3002
K (1)| 10:00:33 |
| 4 | TABLE ACCESS FULL | MYEMP1_OLD | 999K| 17M| 1596
(1)| 00:00:20 |
|* 5 | INDEX RANGE SCAN | IDX_MYEMP1_ENAME | 1 | | 2
(0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| MYEMP1 | 1 | 21 | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------------
SQL> select /*+ ordered use_nl_with_index(e2 idx_myemp1_old_ename) */
2 count(e1.ename)
3 from myemp1 e1, myemp1_old e2
4 where e1.empno = e2.empno
5 and e1.ename = e2.ename ;
COUNT(E1.ENAME)
---------------
199998
경 과: 00:00:46.78
Execution Plan
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | C
ost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 |
41M (1)|136:51:03 |
| 1 | SORT AGGREGATE | | 1 | 39 |
| |
| 2 | NESTED LOOPS | | | |
| |
| 3 | NESTED LOOPS | | 999K| 37M|
41M (1)|136:51:03 |
| 4 | TABLE ACCESS FULL | MYEMP1 | 20M| 400M| 3
9988 (1)| 00:08:00 |
|* 5 | INDEX RANGE SCAN | IDX_MYEMP1_OLD_ENAME | 1 | |
2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| MYEMP1_OLD | 1 | 18 |
3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
오라클자바커뮤니티에서 운영, 개발자 전문교육, 개인80%환급 오엔제이프로그래밍실무교육센터(www.onjprogramming.co.kr)중첩루프조인(Nested Loop Join)에서 USE_NL_WITH_INDEX에 기술한 내부(inner)테이블의 과 조인시 인덱스를 사용하여 조인하도록 명시하는 힌트이다.
If no index is specified, then the optimizer must be able to use some index with at least one join predicate as the index key.
If an index is specified, then the optimizer must be able to use that index with at least one join predicate as the index key.
[예]
SELECT /*+ USE_NL_WITH_INDEX(l item_product_ix) */ *
FROM orders h, order_items l
WHERE l.order_id = h.order_id
AND l.order_id > 2400;
[실습]
실습을 위한 테이블은 다음 URL참조
http://www.oraclejavanew.kr/bbs/board.php?bo_table=LecHINT&wr_id=117
SQL> create index idx_myemp1_ename on myemp1(ename);
인덱스가 생성되었습니다.
경 과: 00:01:36.53
SQL>
SQL> create index idx_myemp1_old_ename on myemp1_old(ename);
인덱스가 생성되었습니다.
경 과: 00:00:01.76
SQL> select /*+ ordered use_nl(e1) */
2 count(e1.ename)
3 from myemp1_old e2, myemp1 e1
4 where e1.empno = e2.empno
5 and e1.ename = e2.ename ;
경 과: 00:00:09.01
Execution Plan
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2002K (
1)| 06:40:29 |
| 1 | SORT AGGREGATE | | 1 | 39 |
| |
| 2 | NESTED LOOPS | | | |
| |
| 3 | NESTED LOOPS | | 999K| 37M| 2002K (
1)| 06:40:29 |
| 4 | TABLE ACCESS FULL | MYEMP1_OLD | 999K| 17M| 1596 (
1)| 00:00:20 |
|* 5 | INDEX UNIQUE SCAN | SYS_C0012349 | 1 | | 1 (
0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| MYEMP1 | 1 | 21 | 2 (
0)| 00:00:01 |
--------------------------------------------------------------------------------
SQL> select /*+ ordered use_nl_with_index(e1 idx_myemp1_ename) */
2 count(e1.ename)
3 from myemp1_old e2, myemp1 e1
4 where e1.empno = e2.empno
5 and e1.ename = e2.ename ;
COUNT(E1.ENAME)
---------------
199998
경 과: 00:00:53.20
Execution Plan
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 3002
K (1)| 10:00:33 |
| 1 | SORT AGGREGATE | | 1 | 39 |
| |
| 2 | NESTED LOOPS | | | |
| |
| 3 | NESTED LOOPS | | 999K| 37M| 3002
K (1)| 10:00:33 |
| 4 | TABLE ACCESS FULL | MYEMP1_OLD | 999K| 17M| 1596
(1)| 00:00:20 |
|* 5 | INDEX RANGE SCAN | IDX_MYEMP1_ENAME | 1 | | 2
(0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| MYEMP1 | 1 | 21 | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------------
SQL> select /*+ ordered use_nl_with_index(e2 idx_myemp1_old_ename) */
2 count(e1.ename)
3 from myemp1 e1, myemp1_old e2
4 where e1.empno = e2.empno
5 and e1.ename = e2.ename ;
COUNT(E1.ENAME)
---------------
199998
경 과: 00:00:46.78
Execution Plan
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | C
ost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 |
41M (1)|136:51:03 |
| 1 | SORT AGGREGATE | | 1 | 39 |
| |
| 2 | NESTED LOOPS | | | |
| |
| 3 | NESTED LOOPS | | 999K| 37M|
41M (1)|136:51:03 |
| 4 | TABLE ACCESS FULL | MYEMP1 | 20M| 400M| 3
9988 (1)| 00:08:00 |
|* 5 | INDEX RANGE SCAN | IDX_MYEMP1_OLD_ENAME | 1 | |
2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| MYEMP1_OLD | 1 | 18 |
3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
평일주간(9:30~18:30) 개강
(4/07)[기업100%환급]SQL기초에서 Schema Object까지
(4/07)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지
(4/14)C#4.0,ADO.NET,Network 프로그래밍
(4/14)[기업100%환급]Spring ,MyBatis,Hibernate실무과정
(4/14)[기업100%환급]PL/SQL,ORACLE HINT,TUNING
평일야간(19:00~22:00) 개강
(4/07)SQL초보에서실전전문가까지
(4/08)Spring3.X, MyBatis, Hibernate실무과정
(4/10)C#,ASP.NET마스터
(4/10)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
(4/14)웹퍼블리싱 마스터
+ (4/22)안드로이드개발자과정
주말(10:00~18:00) 개강
(4/12)웹퍼블리싱 마스터
(4/12)SQL초보에서실전전문가까지
(4/12)안드로이드개발자과정
(4/12)JAVA기초에서실무까지
(4/19)C#,ASP.NET마스터
(4/19)Spring3.X, MyBatis, Hibernate실무과정
댓글 없음:
댓글 쓰기