Oracleのテーブルサイズ見積 - メモ

coldfusionで作ったけど意味は覚えてない。
このまま忘れてしまうのも勿体ないので残す。
もし必要になったらソースを読んで考える。

<CFSETTING ENABLECFOUTPUTONLY="YES">

<CFSET Variables.owner = "">
<CFSET Variables.tname = "">
<CFSET Variables.records = "">
<CFSET Variables.data_length = 0>
<CFSET Variables.sum_rowsize = 0>
<CFSET Variables.err = FALSE>

<!--- Oracleパラメータ --->
<!--- table --->
<CFSET Variables.ora_KDBH = 14>
<CFSET Variables.ora_KTBIT = 24>
<CFSET Variables.ora_INITRANS = 3>
<CFSET Variables.ora_KTBBH = 48>
<CFSET Variables.ora_UB4 = 4>
<CFSET Variables.ora_KCBH = 20>
<CFSET Variables.ora_DB_BLOCK_SIZE = 8192>
<CFSET Variables.ora_PCTFREE = 10>
<CFSET Variables.ora_KDBT = 4>
<CFSET Variables.ora_UB1 = 1>
<CFSET Variables.ora_SB2 = 2>
<CFSET Variables.hsize = Variables.ora_DB_BLOCK_SIZE
                 - Variables.ora_KCBH
                 - Variables.ora_UB4
                 - Variables.ora_KTBBH
                 - (Variables.ora_INITRANS - 1) * Variables.ora_KTBIT
                 - Variables.ora_KDBH>
<CFSET Variables.availSpace = Ceiling(Variables.hsize * (1 - Variables.ora_PCTFREE / 100))
                 - Variables.ora_KDBT>
<!--- index --->
<CFSET Variables.ora_fixedHeader = 113>
<CFSET Variables.ora_INITRANS = 10>
<CFSET Variables.ora_PCTFERR = 10>
<CFSET Variables.transactionHeader = 24 * Variables.ora_INITRANS>
<CFSET Variables.ora_entryHeader = 2>
<CFSET Variables.ora_RowidLength = 6>
<CFSET Variables.blockHeaderSize = Variables.ora_fixedHeader + Variables.transactionHeader>
<CFSET Variables.availableDataSpaceParBlock = (Variables.ora_DB_BLOCK_SIZE - Variables.blockHeaderSize)
                                            - ((Variables.ora_DB_BLOCK_SIZE - Variables.blockHeaderSize) * (Variables.ora_PCTFERR / 100))>



<!--- form情報を取得する --->
<CFIF IsDefined("form.owner") EQ TRUE>
    <CFSET Variables.owner = UCase(form.owner)>
</CFIF>
<CFIF IsDefined("form.tname") EQ TRUE>
    <CFSET Variables.tname = UCase(form.tname)>
</CFIF>
<CFIF IsDefined("form.records") EQ TRUE AND Trim(form.records) NEQ "">
    <CFSET Variables.records = form.records>
</CFIF>
<CFIF IsDefined("form.memo") EQ TRUE>
    <CFSET Variables.memo = form.memo>
<CFELSE>
    <CFSET Variables.memo = "">
</CFIF>
<CFIF IsDefined("Form.db") EQ TRUE>
    <CFSET Variables.db = Form.db>
<CFELSE>
    <CFSET Variables.db = "0">
</CFIF>

<!--- クリアボタン押下時 --->
<CFIF IsDefined("form.btn_clear") EQ TRUE>
    <CFSET Variables.owner = "">
    <CFSET Variables.tname = "">
    <CFSET Variables.records = "">
    <CFSET Variables.memo = "">
    <CFSET Variables.db = "0">
    <cfoutput>入力欄をクリアしました。<BR></cfoutput>
</CFIF>

<!--- データソースの設定 --->
<CFIF Variables.db EQ "0">
    <CFSET Variables.DATASOURCE = Application.CM_DS>
<CFELSE>
    <CFSET Variables.DATASOURCE = Application.CM_DSS>
</CFIF>

<!--- 計算ボタン押下時 --->
<CFIF IsDefined("form.btn_calc") EQ TRUE>
    <!--- 入力チェック --->
    <CFIF Trim(Variables.owner) EQ "">
        <cfoutput><font color="red">テーブル所有者は必須入力です。<BR></font></cfoutput>
        <CFSET Variables.err = TRUE>
    </CFIF>
    <CFIF Trim(Variables.tname) EQ "">
        <cfoutput><font color="red">テーブル名は必須入力です。<BR></font></cfoutput>
        <CFSET Variables.err = TRUE>
    </CFIF>
    <CFIF Trim(Variables.records) EQ "">
        <cfoutput><font color="red">想定件数は必須入力です。<BR></font></cfoutput>
        <CFSET Variables.err = TRUE>
    <CFELSE>
        <CFIF IsNumeric(Variables.records) EQ FALSE>
            <cfoutput><font color="red">想定件数には数値を入れて下さい。<BR></font></cfoutput>
            <CFSET Variables.err = TRUE>
        </CFIF>
    </CFIF>
    <CFIF Variables.err EQ FALSE>
        <CFQUERY NAME="GET_TABLEINFO" DATASOURCE="#Variables.DATASOURCE#" DBTYPE="#Application.CM_DT#">
            SELECT
                column_id, 
                column_name,
                data_type,
                DECODE(data_type,'NUMBER',data_precision,data_length) ||
                    DECODE(data_scale,0,NULL,NULL,NULL, ','||data_scale) as col_size,
                DECODE(nullable,'Y','NULL','NOT NULL') as null_opt,
                DECODE(data_type,'NUMBER',data_precision,data_length) as data_length
            FROM
                ALL_TAB_COLUMNS
            WHERE
                owner = '#Variables.owner#' AND
                table_name = '#Variables.tname#'
            ORDER by
                column_id
        </CFQUERY>
        <CFIF GET_TABLEINFO.RecordCount EQ 0>
            <cfoutput><font color="red">データがありません。<BR></font></cfoutput>
            <CFSET Variables.err = TRUE>
        </CFIF>
    </CFIF>

    <CFIF Variables.err EQ FALSE>
        <CFQUERY NAME="GET_INDEX" DATASOURCE="#Variables.DATASOURCE#" DBTYPE="#Application.CM_DT#">
            SELECT
                owner,
                index_name
            FROM
                ALL_INDEXES
            WHERE
                owner = '#Variables.owner#' AND
                table_owner = '#Variables.owner#' AND
                table_name = '#Variables.tname#'
            ORDER BY
                index_name
        </CFQUERY>
    </CFIF>

</CFIF>


<!--- 
****************************************************
                    画面表示
****************************************************
 --->
<CFSETTING ENABLECFOUTPUTONLY="NO">
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html lang="ja">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=Shift_JIS">
    <title>データベース領域見積試算画面</title>
    <link rel="stylesheet" type="text/css" href="../default.css">
    <style TYPE="text/css">
    <!--
        div.query_item { margin-left: 2em; }
        table { margin-left: 2em }
    -->
    </style>
</head>
<body>
<h1>データベース領域見積</h1>
<p>開発環境の定義情報と想定件数からテーブル、インデックスに必要なサイズを見積ます。
<!--- 
**************************************
            検索条件表示
**************************************
 --->
<div class="query_item">
<form name="f1" method="post" action="DbEstimate.cfm">
    <CFOUTPUT>
    環境：
    <cfif Variables.db EQ "0">
        <input type="radio" name="db" value="0" checked>本番
        <input type="radio" name="db" value="1">整備
    <cfelse>
        <input type="radio" name="db" value="0">本番
        <input type="radio" name="db" value="1" checked>整備
    </cfif><br>

    テーブル所有者：<input type="text" name="owner" value="#Variables.owner#">
    テーブル名：<input type="text" size="40" name="tname" value="#Variables.tname#">
    想定件数：<input type="text" name="records" value="#Variables.records#">
    <br>
    備考：<br>
    <textarea name="memo" cols=80 rows=4>#Variables.memo#</textarea><br>
    </CFOUTPUT>
    <input type="submit" name="btn_calc" value="計算">
    <input type="submit" name="btn_clear" value="入力クリア"><br>
</form>
</div>
<br>
<!--- クリアボタン押下時 --->
<CFIF IsDefined("form.btn_clear") EQ TRUE>
    <CFABORT>
</CFIF>

<!--- 
**************************************
            結果表示
**************************************
 --->
<!--- 計算ボタン押下時 --->
<CFIF IsDefined("form.btn_calc") EQ TRUE AND Variables.err EQ FALSE>
    <h2>テーブル</h2>
    <cfoutput>
    <h3>#Variables.tname#</h3>
    <table border="1">
    <!--- <caption>#Variables.tname#</caption> --->
    </cfoutput>
        <th>No</th>
        <th>カラム名</th>
        <th>属性</th>
        <th>項目長</th>
        <th>NULL制約</th>
        <th>データ長</th>
    <CFOUTPUT QUERY="GET_TABLEINFO">
        <tr>
            <!--- カラム番号 --->
            <td align="right">#GET_TABLEINFO.column_id#</td>

            <!--- カラム名 --->
            <td>#GET_TABLEINFO.column_name#</td>

            <!--- データ属性 --->
            <td>#GET_TABLEINFO.data_type#</td>

            <!--- 項目長 --->
            <td align="right">#GET_TABLEINFO.col_size#</td>

            <!--- NULL制約 --->
            <td>#GET_TABLEINFO.null_opt#</td>

            <!--- データ長 --->
            <CFIF GET_TABLEINFO.data_length GT 250>
                <CFSET Variables.data_length = GET_TABLEINFO.data_length + 3>
            <CFELSE>
                <CFSET Variables.data_length = GET_TABLEINFO.data_length + 1>
            </CFIF>
            <CFSET Variables.sum_rowsize = Variables.sum_rowsize + Variables.data_length>
            <td align="right">#Variables.data_length#</td>
        </tr>
    </CFOUTPUT>
    </table><br>


    <!--- 
    **************************************
                DBサイズ見積
    **************************************
    --->
    <CFOUTPUT>
    <CFSET Variables.sum_rowsize = Variables.sum_rowsize + ( 3 * 1 )>
    <table border="1">
    <!--- <caption>見積り情報</caption> --->
        <CFIF Variables.ora_UB1*3 + Variables.ora_UB4 + Variables.ora_SB2 GT Variables.sum_rowsize>
            <CFSET Variables.row_space = (Variables.ora_UB1*3 + Variables.ora_UB4 + Variables.ora_SB2) + Variables.ora_SB2>
        <CFELSE>
            <CFSET Variables.row_space = Variables.sum_rowsize + Variables.ora_SB2>
        </CFIF>
        <tr><td>１レコードが使用するサイズ</td><td align="right">#Variables.row_space# byte<br></td></tr>

        <CFSET Variables.NumOfRowsInBlock = Fix( Variables.availSpace / Variables.row_space )>
        <tr><td>１ブロック内に格納できるレコード件数</td><td align="right">#Variables.NumOfRowsInBlock# 件<br></td></tr>

        <tr><td>想定レコード件数(未入力の場合１件とする)</td><td align="right">#Variables.records# 件<br></td></tr>

        <CFSET Variables.use_blocks = Ceiling( Variables.records / Variables.NumOfRowsInBlock )>
        <tr><td>使用するブロック数</td><td align="right">#Variables.use_blocks# ブロック<br></td></tr>

        <CFSET Variables.table_size = Variables.use_blocks * Variables.ora_DB_BLOCK_SIZE / 1024>
        <tr><td><b>必要なサイズ</b></td><td align="right"><b>#Variables.table_size#K</b></td></tr>

        <CFSET Variables.init_records = (Variables.table_size / 8) * Variables.NumOfRowsInBlock>
        <tr><td>格納可能件数</td><td align="right">#Variables.init_records# 件</td></tr>
    </table>
    </CFOUTPUT>
    <br>

    <CFIF GET_INDEX.RecordCount GT 0>
    <h2>インデックス情報</h2>
        <CFSET Variables.preColPos = 0>

        <CFOUTPUT QUERY="GET_INDEX">
            <CFSET Variables.total_length = 0>

    <h3>#GET_INDEX.index_name#</h3>
            <table border="1">
            <!--- <caption>#GET_INDEX.index_name#</caption> --->
                <th>No</th>
                <th>カラム名</th>
                <th>項目長</th>

            <CFQUERY NAME="GET_INDEXINFO" DATASOURCE="#Variables.DATASOURCE#" DBTYPE="#Application.CM_DT#">
                SELECT
                    I.column_position,
                    I.column_name,
                    DECODE(T.data_type,'NUMBER',T.data_precision,T.data_length) as column_length
                FROM
                    ALL_IND_COLUMNS I,
                    ALL_TAB_COLUMNS T
                WHERE
                    I.table_owner = T.owner AND
                    I.table_name = T.table_name AND
                    I.column_name = T.column_name AND
                    I.index_owner = '#GET_INDEX.owner#' AND
                    I.index_name = '#GET_INDEX.index_name#'
                ORDER BY
                    column_position
            </CFQUERY>
            <CFLOOP QUERY="GET_INDEXINFO">
                <tr>
                    <td>#GET_INDEXINFO.column_position#<br></td>
                    <td>#GET_INDEXINFO.column_name#<br></td>
                    <td>#GET_INDEXINFO.column_length#<br></td>
                </tr>
                <CFSET Variables.total_length = Variables.total_length + GET_INDEXINFO.column_length>
            </CFLOOP>
            </table><br>
            <!--- 
            **************************************
                    インデックスサイズ見積
            **************************************
            --->
            <table border="1">
            <!--- <caption>見積り情報</caption> --->
                <tr><td>項目長の合計</td><td align="right">#Variables.total_length#<br></td></tr>
                <tr><td>カラム数</td><td align="right">#GET_INDEXINFO.RecordCount#<br></td></tr>

                <CFSET Variables.avgEntrySize = Variables.ora_entryHeader
                                              + Variables.ora_RowidLength
                                              + GET_INDEXINFO.RecordCount
                                              + Variables.total_length>
                <tr><td>avg_entry_size</td><td align="right">#Variables.avgEntrySize#<br></td></tr>

                <CFSET Variables.blockForIndex = 1.05 * (
                                                    (Variables.init_records * 1.3 * Variables.avgEntrySize)
                                                   / (Fix(Variables.availableDataSpaceParBlock/Variables.avgEntrySize) * Variables.avgEntrySize))>
                <tr><td>block for index</td><td align="right">#Variables.blockForIndex#<br></td></tr>

                <CFIF Variables.ora_DB_BLOCK_SIZE * Ceiling(Variables.blockForIndex) / 1024 EQ 4>
                    <CFSET Variables.index_size = 8>
                <CFELSE>
                    <CFSET Variables.index_size = Variables.ora_DB_BLOCK_SIZE * Ceiling(Variables.blockForIndex) / 1024>
                </CFIF>
                <tr><td><b>必要なサイズ</b></td><td align="right"><b>#Variables.index_size#K</b></td></tr>
            </table><br>
        </CFOUTPUT>
    </CFIF>
</CFIF>
</body>
</html>