{"id":1298,"date":"2016-02-15T03:56:31","date_gmt":"2016-02-15T03:56:31","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=1298"},"modified":"2016-02-15T03:56:31","modified_gmt":"2016-02-15T03:56:31","slug":"%ec%98%a4%eb%9d%bc%ed%81%b4-dbms-12c-%eb%82%b4%ec%9a%a9-%ec%a4%91-sql-plsql","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=1298","title":{"rendered":"\uc624\ub77c\ud074 DBMS 12c \ub0b4\uc6a9 \uc911 SQL & PLSQL"},"content":{"rendered":"

[<\/span>\uc624\ub77c\ud074 DBMS 12c <\/span>\ub0b4\uc6a9
\n\uc911 SQL & PLSQL \uc8fc\uc694 \uae30\ub2a5]<\/span><\/p>\n


<\/span><\/p>\n

<\/p>\n

1. Bitamp <\/span>\uc778\ub371\uc2a4\uc640 B-tree <\/span>\uc778\ub371\uc2a4 \uba85 \uc911\ubcf5 \uac00\ub2a5<\/b><\/span><\/p>\n

<\/p>\n

<\/p>\n

Duplicate indexes<\/p>\n

\u2013\u202f\u201cORA-01408: such column list already indexed\u201d<\/p>\n

\u2013\u202fCan have both B-tree and bitmap index on samecolumn(s)<\/span><\/p>\n

<\/p>\n

<\/p>\n

2. with<\/span>\uc808 \uc0ac\uc6a9\uc2dc 4<\/span>\ubc30\uc5d0\uc11c 8<\/span>\ubc30
\n\uae4c\uc9c0 \ube68\ub77c\uc9d0<\/b><\/span><\/p>\n

<\/p>\n

WITH-plsql function<\/p>\n

\u2013\u202f4-8x faster execution<\/p>\n

<\/p>\n

WITH<\/p>\n

function is_number(n varchar2)<\/p>\n

return char is<\/p>\n

begin<\/p>\n

<check if n is a number><\/p>\n

end<\/p>\n

select * from <table><\/p>\n

where is_number(sal) = \u2018NO\u2019;<\/p>\n

<\/p>\n

<\/p>\n

<\/p>\n

3. Varchar2 4000 -> 32K <\/span>\uae4c\uc9c0 \uc0ac\uc6a9 \uac00\ub2a5 LOB<\/span>\uc744
\n\uc0ac\uc6a9 \uc548 \ud574\ub3c4 \ub428<\/b><\/span><\/p>\n

<\/p>\n

Varchar2(32K)<\/p>\n

\u2013\u202fCurrently max is 4000 bytes<\/p>\n

<\/p>\n

<\/p>\n

4. Table <\/span>\uc0dd\uc131 \uc2dc Default <\/span>\uc808\uc5d0 Sequence <\/span>\uc0ac\uc6a9 \uac00\ub2a5<\/b><\/span><\/p>\n

<\/p>\n

\u2022\u202fAuto-populate column from a sequence<\/p>\n

\u2013\u202fUsing the DEFAULT clause for a column<\/p>\n

<\/p>\n

<\/p>\n<\/p>\n

5. <\/span>\uc624\ub77c\ud074 \uc635\ud2f0\ub9c8\uc774\uc800\uac00 \uc88b\uc544\uc838\uc11c \uc545\uc131 \ucffc\ub9ac \uac00 \uac10\uc18c\ud568 ( <\/span>\ubc18\ub300\ub85c \ud50c\ub79c\uc774
\n\ubc14\ub014 \uc218 \uc788\uc74c<\/b>)<\/b><\/span><\/p>\n


<\/span><\/p>\n

\u2022\u202f Very often caused by bad execution plans<\/p>\n

\u2013\u202fUsually due to bad statistics<\/p>\n

\u2022\u202f Old or missing statistics<\/p>\n

\u2022 \u202fHard to predict number of returned rows<\/p>\n

\u2013\u202fComplex predicates<\/p>\n

\u2022\u202f where substr(to_char(edate,\u2019YYMMDD\u2019),2,2) > 8<\/p>\n

\u2013\u202fJoin cardinalities<\/p>\n

<\/span><\/p>\n

\u2013\u202fData skew, correlatio<\/p>\n

<\/p>\n

\u2022\u202f Solution<\/span><\/p>\n

\u2013\u202fAdaptive cursor sharing (11g)<\/p>\n

\u2013\u202fAdaptive statistics (12c)<\/p>\n

<\/span><\/p>\n

\u2013\u202fAdaptive execution plans (12c)<\/p>\n

<\/p>\n

<\/p>\n


<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

[\uc624\ub77c\ud074 DBMS 12c \ub0b4\uc6a9 \uc911 SQL & PLSQL \uc8fc\uc694 \uae30\ub2a5] 1. Bitamp \uc778\ub371\uc2a4\uc640 B-tree \uc778\ub371\uc2a4 \uba85 \uc911\ubcf5 \uac00\ub2a5 Duplicate indexes \u2013\u202f\u201cORA-01408: such column list already indexed\u201d \u2013\u202fCan have both B-tree and bitmap index on samecolumn(s) 2. with\uc808 \uc0ac\uc6a9\uc2dc 4\ubc30\uc5d0\uc11c 8\ubc30 \uae4c\uc9c0 \ube68\ub77c\uc9d0 WITH-plsql function \u2013\u202f4-8x faster execution WITH function is_number(n varchar2) return char is begin […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[20],"tags":[710,711,74,712],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1298"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1298"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1298\/revisions"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1298"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1298"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1298"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}