{"id":346,"date":"2012-10-10T09:51:11","date_gmt":"2012-10-10T00:51:11","guid":{"rendered":"http:\/\/www.freesens.com\/x\/?p=346"},"modified":"2024-01-16T09:50:30","modified_gmt":"2024-01-16T00:50:30","slug":"table-function%ea%b3%bc-join","status":"publish","type":"post","link":"http:\/\/www.freesens.com\/x\/?p=346","title":{"rendered":"Table Function\uacfc Join"},"content":{"rendered":"

\ucd9c\ucc98 :\u00a0http:\/\/ukja.tistory.com\/197<\/a><\/p>\n

 <\/p>\n

Table Function\uc744 \uc990\uaca8 \uc0ac\uc6a9\ud558\ub294 \uc0ac\ub78c\ub4e4\uc774 \uc788\uc744 \uac83\uc774\ub2e4.<\/p>\n

Table Function\uacfc \uad00\ub828\ud574\uc11c \uc798 \uc54c\ub824\uc9c0\uc9c0 \uc54a\uc740 \uc0ac\uc2e4 \uc911 \ud558\ub098\ub294\u00a0Join\uc5d0 \uad00\ud55c \uac83\uc774\ub2e4. Table Function\uc758 \uacb0\uacfc\ub294 \ub9d0 \uadf8\ub300\ub85c Table\uacfc \uac19\uc73c\ubbc0\ub85c Join\uc5d0 \ubb38\uc81c\uac00\u00a0 \uc5c6\uc5b4\uc57c \ud55c\ub2e4.<\/p>\n

\ud558\uc9c0\ub9cc \uc5b4\ub5bb\uac8c?<\/p>\n

\uac04\ub2e8\ud55c \uc608\ub97c \ud1b5\ud574 Table Function\uc744 Join\uc5d0\uc11c \uc5b4\ub5bb\uac8c \uc0ac\uc6a9\ud558\ub294\uc9c0 \uc54c\uc544 \ubcf4\uc790.<\/p>\n

\ub2e4\uc74c\uacfc \uac19\uc774 Object Type\uacfc Collection Type\uc744 \uc120\uc5b8\ud55c\ub2e4.<\/p>\n

— create objects
\ncreate or replace type obj_type1 as object (
\nc1 int,
\nc2 int
\n);
\n\/<\/p>\n

create or replace type obj_tbl_type1 as table of obj_type1;
\n\/<\/p><\/div>\n

Collection Type\uc744 Return\ud558\ub294 Pipelined Function\uc744 \uc0dd\uc131\ud55c\ub2e4.<\/p>\n

create or replace function func1
\nreturn obj_tbl_type1
\npipelined
\nis
\nv_obj obj_type1;
\nbegin
\nfor idx in 1 .. 100 loop
\nv_obj := obj_type1(idx, idx);
\npipe row(v_obj);
\nend loop;
\nend;
\n\/<\/div>\n

\ub2e4\uc74c\uacfc \uac19\uc774 \uc0ac\uc6a9\ub41c\ub2e4.<\/p>\n

select * from table(func1());<\/p>\n

C1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 C2
\n———- ———-
\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1
\n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2
\n3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3
\n4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4
\n5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5
\n…
\n99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 99
\n100\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 100<\/p><\/div>\n

\uc880 \ub354 \uc7ac\ubbf8\uc788\ub294 \ud14c\uc2a4\ud2b8\ub97c \uc704\ud574 \ub2e4\uc74c\uacfc \uac19\uc774 Argument\ub97c \ubc1b\ub294 Function\uc744 \uc0dd\uc131\ud55c\ub2e4.<\/p>\n

create or replace function func2(p1 int, p2 int, p3 int)
\nreturn obj_tbl_type1
\npipelined
\nis
\nv_obj obj_type1;
\nbegin
\nfor idx in 1 .. p3 loop
\nv_obj := obj_type1(p1+idx, p2+idx);
\n<\/object>\u00a0\u00a0\u00a0 pipe row(v_obj);
\nend loop;
\nend;
\n\/<\/div>\n

\ub2e4\uc74c\uacfc \uac19\uc774 \uc0ac\uc6a9\ub41c\ub2e4.<\/p>\n

select * from table(func2(1, 1, 10))
\n;
\nC1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 C2
\n———- ———-
\n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2
\n3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3
\n4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4
\n5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5
\n6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6
\n7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7
\n8\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8
\n9\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9
\n10\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 10
\n11\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 11<\/div>\n

\uc774 \ud568\uc218\ub97c \uc5b4\ub5bb\uac8c \ub2e4\ub978 Table\uacfc \uc870\uc778\ud558\ub294\uac00?<\/p>\n

drop table t1 purge;
\ncreate table t1(c1)
\nas
\nselect level from dual connect by level <= 100
\n;<\/div>\n

\uc774\ub7f0 \ubc29\uc2dd\uc740 \uc9c0\uc6d0\ub418\uc9c0 \uc54a\ub294\ub2e4.<\/p>\n

select *
\nfrom t1, table(func2(p1, p2, 10))\u00a0x
\nwhere t1.c1 = x.c1
\n;<\/div>\n

\ub2e4\uc74c\uacfc \uac19\uc740 \ubb38\ubc95\uc774 \uc0ac\uc6a9\ub41c\ub2e4.<\/p>\n

select *
\nfrom t1, table(func2(t1.c1, t1.c1, 10))
\n;<\/p>\n

C1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 C1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 C2
\n———- ———- ———-
\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2
\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3
\n…<\/p><\/div>\n

\uc989, t1\uc758 \uacb0\uacfc\uac00 Function\uc758 \uc778\uc790\ub85c \ubc14\ub85c \uc0ac\uc6a9\ub41c\ub2e4. \uc774\ub54c \uc21c\uc11c\uac00 \uc911\uc694\ud558\ub2e4.
\n\ub2e4\uc74c\uacfc \uac19\uc774 \uc21c\uc11c\uac00 \ubc14\ub00c\uba74 Oracle\uc740 \ucc98\ub9ac\ud558\uc9c0 \ubabb\ud55c\ub2e4.<\/p>\n

select *
\nfrom table(func2(t1.c1, t1.c1, 10)), t1
\n;
\nERROR at line 2:
\nORA-00904: “T1”.”C1″: invalid identifier<\/div>\n

\uc774 \uc0ac\uc2e4\uc744 \uc751\uc6a9\ud558\uba74 \ub2e4\uc74c\uacfc \uac19\uc774 \uc790\uc720\ub86d\uac8c\u00a0Join\uc5d0 \uc0ac\uc6a9\ud560 \uc218 \uc788\ub2e4.<\/p>\n

select *
\nfrom
\n(select null as c1, null as c2 from dual connect by level <= 100) s,
\ntable(func2(s.c1, s.c1, 10))
\n;<\/div>\n

\uc798 \uc774\uc6a9\ud558\uba74 \ub9e4\uc6b0 \uac15\ub825\ud55c Query\ub97c \ub9cc\ub4e4 \uc218 \uc788\ub2e4.<\/p>\n

\uac00\ub839 \uc544\ub798 Query\ub97c \ubcf4\uc790.\u00a0Shared Pool(v$sql)\uc5d0 Cache\ub418\uc5b4 \uc788\ub294 Query\ub4e4 \uc911 buffer_gets(logical reads) \uc218\uce58\uac00 \ub192\uc740 \uc21c\uc73c\ub85c Runtime \uc2e4\ud589 \uacc4\ud68d\uc744 \ucd94\ucd9c\ud55c\ub2e4. \uc774\ub7f0 \ubcf5\uc7a1\ud574 \ubcf4\uc774\ub294 \uc694\uad6c \uc0ac\ud56d\ub3c4 Table Function\uc758 Join \uae30\ub2a5\uc744 \uc798 \uc774\uc6a9\ud558\uba74 \ub9e4\uc6b0 \uac04\ub2e8\ud55c\uac8c \uad6c\ud604\ud560 \uc218 \uc788\ub2e4.<\/p>\n

select plan_table_output
\nfrom
\n(select * from
\n(select s.sql_id, s.child_number
\nfrom v$sql s
\nwhere exists(select 1 from v$sql_plan p where p.plan_hash_value = s.plan_hash_value)
\norder by s.buffer_gets desc)
\nwhere rownum <= 10
\n) s,
\ntable(dbms_xplan.display_cursor(s.sql_id, s.child_number, ‘allstats last’))
\n;<\/p>\n

(\ucd9c\ub825 \ubb38\uc81c\ub85c \uc9e4\ub9bc)
\nPLAN_TABLE_OUTPUT
\n——————————————————————————-
\nSQL_ID\u00a0 803b7z0t84sq7, child number 0
\n————————————-
\nselect job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (
\n((last_date is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and
\n(this_date is null) order by next_date, job<\/p>\n

Plan hash value: 1846751226<\/p>\n

——————————————————————————-
\n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name | Starts | E-Rows | A-Rows |\u00a0\u00a0 A-Time\u00a0\u00a0 | Buf
\n——————————————————————————-
\n|\u00a0\u00a0 1 |\u00a0 SORT ORDER BY\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |00:00:00.01 |
\n|*\u00a0 2 |\u00a0\u00a0 TABLE ACCESS FULL| JOB$ |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |00:00:00.01 |
\n——————————————————————————-<\/p>\n

Predicate Information (identified by operation id):
\n—————————————————<\/p>\n

2 – filter((((“NEXT_DATE”>=:1 AND “NEXT_DATE”<:2) OR (“LAST_DATE” IS NULL AN
\n(“FIELD1″=:4 OR (‘Y’=:5 AND “FIELD1″=0)) AND “THIS_DATE” IS NULL)<\/p>\n

SQL_ID\u00a0 96g93hntrzjtr, child number 0
\n————————————-
\nselect \/*+ rule *\/ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample
\nminimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln
\nhist_head$ where obj#=:1 and intcol#=:2<\/p>\n

Plan hash value: 2239883476<\/p>\n

——————————————————————————-
\n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Starts | A-Rows |\u00a0\u00a0 A
\n——————————————————————————-
\n|\u00a0\u00a0 1 |\u00a0 TABLE ACCESS BY INDEX ROWID| HIST_HEAD$\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |00:0
\n|*\u00a0 2 |\u00a0\u00a0 INDEX RANGE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | I_HH_OBJ#_INTCOL# |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |00:0
\n——————————————————————————-<\/p>\n

Predicate Information (identified by operation id):
\n—————————————————<\/p>\n

2 – access(“OBJ#”=:1 AND “INTCOL#”=:2)<\/p>\n

Note
\n—–
\n– rule based optimizer used (consider using cbo)
\n…<\/p>\n

(Pipelined) Table Function\uc740 \uc801\uc7ac\uc801\uc18c\uc5d0 \uc798 \uc0ac\uc6a9\ud558\uba74 \ub9e4\uc6b0 \uc138\ub828\ub41c Query\uc640 Application\uc744 \uac00\ub2a5\ud558\uac8c \ud574 \uc8fc\ub294 \uc88b\uc740 \uae30\ub2a5\uc774\ub77c\uace0 \uc0dd\uac01\ub41c\ub2e4.<\/p>\n","protected":false},"excerpt":{"rendered":"

\ucd9c\ucc98 :\u00a0http:\/\/ukja.tistory.com\/197   Table Function\uc744 \uc990\uaca8 \uc0ac\uc6a9\ud558\ub294 \uc0ac\ub78c\ub4e4\uc774 \uc788\uc744 \uac83\uc774\ub2e4. Table Function\uacfc \uad00\ub828\ud574\uc11c \uc798 \uc54c\ub824\uc9c0\uc9c0 \uc54a\uc740 \uc0ac\uc2e4 \uc911 \ud558\ub098\ub294\u00a0Join\uc5d0 \uad00\ud55c \uac83\uc774\ub2e4. Table Function\uc758 \uacb0\uacfc\ub294 \ub9d0 \uadf8\ub300\ub85c Table\uacfc \uac19\uc73c\ubbc0\ub85c Join\uc5d0 \ubb38\uc81c\uac00\u00a0 \uc5c6\uc5b4\uc57c \ud55c\ub2e4. \ud558\uc9c0\ub9cc \uc5b4\ub5bb\uac8c? \uac04\ub2e8\ud55c \uc608\ub97c \ud1b5\ud574 Table Function\uc744 Join\uc5d0\uc11c \uc5b4\ub5bb\uac8c \uc0ac\uc6a9\ud558\ub294\uc9c0 \uc54c\uc544 \ubcf4\uc790. \ub2e4\uc74c\uacfc \uac19\uc774 Object Type\uacfc Collection Type\uc744 \uc120\uc5b8\ud55c\ub2e4. — create objects create … Continue reading Table Function\uacfc Join<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[24,28],"class_list":["post-346","post","type-post","status-publish","format-standard","hentry","category-etc","tag-oracle","tag-28"],"_links":{"self":[{"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=\/wp\/v2\/posts\/346","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=346"}],"version-history":[{"count":1,"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=\/wp\/v2\/posts\/346\/revisions"}],"predecessor-version":[{"id":347,"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=\/wp\/v2\/posts\/346\/revisions\/347"}],"wp:attachment":[{"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=346"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=346"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=346"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}