Oracle PL/SQL之函数索引（Function-based indexes）使用示例

函数索引（Function-based indexes）只有在where条件使用了与索引中相同的基于相同列的函数时才起作用。 duzz$scott@orcl>set autotrace on<br> duzz$scott@orcl>create table t1 as select * from dept;<p> <p>Table created.</p> <p>Elapsed: 00:00:00.01<br> duzz$scott@orcl>create index loc_idx on t1(upper(loc));</p> <p>Index created.</p> <p>Elapsed: 00:00:00.06<br> duzz$scott@orcl>select * from t1 where deptno=20;</p> <p> DEPTNO DNAME LOC<br> ———- —————————————— ————<br> 20 RESEARCH DALLAS</p> <p>Elapsed: 00:00:00.00</p> <p>Execution Plan<br> ———————————————————-<br> Plan hash value: 3617692013</p> <p>————————————————————————–<br> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br> ————————————————————————–<br> | 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |<br> |* 1 | TABLE ACCESS FULL| T1 | 1 | 30 | 3 (0)| 00:00:01 |<br> ————————————————————————–</p> <p>Predicate Information (identified by operation id):<br> —————————————————</p> <p> 1 – filter(“DEPTNO”=20)</p> <p>Note<br> —–<br> – dynamic sampling used for this statement</p> <p>Statistics<br> ———————————————————-<br> 48 recursive calls<br> 0 db block gets<br> 12 consistent gets<br> 0 physical reads<br> 0 redo size<br> 533 bytes sent via SQL*Net to client<br> 385 bytes received via SQL*Net from client<br> 2 SQL*Net roundtrips to/from client<br> 0 sorts (memory)<br> 0 sorts (disk)<br> 1 rows processed</p> <p>duzz$scott@orcl>select * from t1 where loc=’DALLAS’;</p> <p> DEPTNO DNAME LOC<br> ———- —————————————— —————–<br> 20 RESEARCH DALLAS</p> <p>Elapsed: 00:00:00.00</p> <p>Execution Plan<br> ———————————————————-<br> Plan hash value: 3617692013</p> <p>————————————————————————–<br> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br> ————————————————————————–<br> | 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |<br> |* 1 | TABLE ACCESS FULL| T1 | 1 | 30 | 3 (0)| 00:00:01 |<br> ————————————————————————–</p> <p>Predicate Information (identified by operation id):<br> —————————————————</p> <p> 1 – filter(“LOC”=’DALLAS’)</p> <p>Note<br> —–<br> – dynamic sampling used for this statement</p> <p>Statistics<br> ———————————————————-<br> 5 recursive calls<br> 0 db block gets<br> 8 consistent gets<br> 0 physical reads<br> 0 redo size<br> 533 bytes sent via SQL*Net to client<br> 385 bytes received via SQL*Net from client<br> 2 SQL*Net roundtrips to/from client<br> 0 sorts (memory)<br> 0 sorts (disk)<br> 1 rows processed</p> <p>duzz$scott@orcl>select </p> </div> <div class="bq"> <p>转载请注明：<a href="https://94ip.com/" target="_blank">IT运维空间</a> » <a href="https://94ip.com/category-26.html" target="_blank">Mssql</a> » <a href="https://94ip.com/post/27576.html">Oracle PL/SQL之函数索引（Function-based indexes）使用示例</a></p> </div> <div class="related"> <h5>你可能喜欢:</h5> <ul> <li> <a href="https://94ip.com/post/27897.html" title="Oracle数据库PL/SQL Developer查询结果显示问号乱码的解决方法"> <div class="img"> <img src="https://94ip.com/zb_users/theme/ydconcise/include/random/4.jpg" alt="Oracle数据库PL/SQL Developer查询结果显示问号乱码的解决方法"> </div> <p>Oracle数据库PL/SQL Developer查询结果显示问号乱码的解决方法</p> </a> </li> <li> <a href="https://94ip.com/post/27873.html" title="MySQL与Oracle的语法区别详细对比 （转）"> <div class="img"> <img src="https://94ip.com/zb_users/theme/ydconcise/include/random/7.jpg" alt="MySQL与Oracle的语法区别详细对比 （转）"> </div> <p>MySQL与Oracle的语法区别详细对比 （转）</p> </a> </li> <li> <a href="https://94ip.com/post/27867.html" title="sql数据库在登录异常时 ora-03114：未连接到ORACLE怎么办"> <div class="img"> <img src="/zb_users/upload/2023/02/25/20230213102324-63ea0f9c05cb8.jpg" alt="sql数据库在登录异常时 ora-03114：未连接到ORACLE怎么办"> </div> <p>sql数据库在登录异常时 ora-03114：未连接到ORACLE怎么办</p> </a> </li> <li> <a href="https://94ip.com/post/27828.html" title="mysql的sql筛选排重最大值并修改其属性"> <div class="img"> <img src="https://94ip.com/zb_users/theme/ydconcise/include/random/7.jpg" alt="mysql的sql筛选排重最大值并修改其属性"> </div> <p>mysql的sql筛选排重最大值并修改其属性</p> </a> </li> <li> <a href="https://94ip.com/post/27812.html" title="python3 UnicodeEncodeError错误，cx_oracle模块执行sql报错：UnicodeEncodeError: &#8216;ascii&#8217; codec can&#8217;t encode characters in position"> <div class="img"> <img src="https://94ip.com/zb_users/theme/ydconcise/include/random/3.jpg" alt="python3 UnicodeEncodeError错误，cx_oracle模块执行sql报错：UnicodeEncodeError: &#8216;ascii&#8217; codec can&#8217;t encode characters in position"> </div> <p>python3 UnicodeEncodeError错误，cx_oracle模块执行sql报错：UnicodeEncodeError: &#8216;ascii&#8217; codec can&#8217;t encode characters in position</p> </a> </li> <li> <a href="https://94ip.com/post/27809.html" title="MySQL-常用数据库操作SQL汇总"> <div class="img"> <img src="https://94ip.com/zb_users/theme/ydconcise/include/random/6.jpg" alt="MySQL-常用数据库操作SQL汇总"> </div> <p>MySQL-常用数据库操作SQL汇总</p> </a> </li> <li> <a href="https://94ip.com/post/27787.html" title="mysql sql profiler"> <div class="img"> <img src="https://94ip.com/zb_users/theme/ydconcise/include/random/8.jpg" alt="mysql sql profiler"> </div> <p>mysql sql profiler</p> </a> </li> <li> <a href="https://94ip.com/post/27768.html" title="sql 笔记(mysql)"> <div class="img"> <img src="/zb_users/upload/2023/02/25/20230213105429-63ea16e56e13a.jpg" alt="sql 笔记(mysql)"> </div> <p>sql 笔记(mysql)</p> </a> </li> <li> <a href="https://94ip.com/post/27753.html" title="MySQL和Sql Server的sql语句区别"> <div class="img"> <img src="https://94ip.com/zb_users/theme/ydconcise/include/random/9.jpg" alt="MySQL和Sql Server的sql语句区别"> </div> <p>MySQL和Sql Server的sql语句区别</p> </a> </li> <li> <a href="https://94ip.com/post/27690.html" title="My SQL中show命令&#8211;MySQL中帮助查看"> <div class="img"> <img src="https://94ip.com/zb_users/theme/ydconcise/include/random/9.jpg" alt="My SQL中show命令&#8211;MySQL中帮助查看"> </div> <p>My SQL中show命令&#8211;MySQL中帮助查看</p> </a> </li> </ul> </div> </div> <div id="comments" class="comments"> <div class="c_title"> <h5>发表评论</h5> <span><a rel="nofollow" id="cancel-reply" href="#comments" style="display:none;">取消回复</a></span> </div> <div id="comment_post" class="compost"> <div class="cp_avatar"> <img src="https://94ip.com/zb_users/theme/ydconcise/include/avator/6.jpg"> </div> <div class="cp_post"> <form id="frmSumbit" target="_self" method="post" action="https://94ip.com/zb_system/cmd.php?act=cmt&postid=27576&key=c8f0081a69e02a683aa11833a2ce6223"> <input type="hidden" name="inpId" id="inpId" value="27576"/> <input type="hidden" name="inpRevID" id="inpRevID" value="0"/> <div class="cp_box"> <textarea placeholder="说点什么" class="textarea" name="txaArticle" id="txaArticle" cols="100%" rows="5" tabindex="1">