pl/sql 语句的几点优化

最新推荐文章于 2025-08-24 12:50:11 发布

最新推荐文章于 2025-08-24 12:50:11 发布 · 76 阅读

本文探讨了在Oracle数据库中如何正确使用索引以优化SQL查询性能，通过具体示例对比了不同查询条件对索引使用的差异，并给出了提高查询效率的最佳实践。

SQL> desc gjh_a05 ;
Name Type Nullable Default Comments
----- ------------- -------- ------- --------
A0500 VARCHAR2(2) Y
A0501 VARCHAR2(6)
A0502 VARCHAR2(10) Y
A0503 VARCHAR2(10) Y
A0504 VARCHAR2(20) Y
A0505 VARCHAR2(255) Y
A0506 VARCHAR2(1) Y
A0507 VARCHAR2(4) Y
A0508 VARCHAR2(8) Y
A0509 CHAR(1) Y
A0510 VARCHAR2(8) Y
A0511 DATE Y
A0512 DATE Y

SQL>

当对a0506 建索引时。。

select * from gjh_a05 where a0506>to_char(5*2)

SELECT STATEMENT, GOAL = CHOOSE
TABLE ACCESS BY INDEX ROWID Object owner=FZDC Object name=GJH_A05
INDEX RANGE SCAN Object owner=FZDC Object name=GJH_A05_A0506

可是

select * from gjh_a05 where to_number(a0506)*2>5

SELECT STATEMENT, GOAL = CHOOSE
TABLE ACCESS FULL Object owner=FZDC Object name=GJH_A05

尽可能不要对索引计算

select * from gjh_a05 where a0506>'5'

SELECT STATEMENT, GOAL = CHOOSE
TABLE ACCESS BY INDEX ROWID Object owner=FZDC Object name=GJH_A05
INDEX RANGE SCAN Object owner=FZDC Object name=GJH_A05_A0506

select * from gjh_a05 where a0506>5

SELECT STATEMENT, GOAL = CHOOSE
TABLE ACCESS FULL Object owner=FZDC Object name=GJH_A05

2。主表和基表怎么排顺序

SQL> select count(*) from gjh_a05;

COUNT(*)
----------
4753

Executed in 0.031 seconds

SQL> select count(*) from gjh_a06 ;

COUNT(*)
----------
999

Executed in 0.031 seconds

SQL> select * from gjh_a06 a,gjh_a05 b where rownum<2;

Executed in 0.047 seconds

Executed in 0.031 seconds

Executed in 0.032 seconds

Executed in 0.031 seconds

SQL> select * from gjh_a05 a,gjh_a06 b where rownum<2;

Executed in 0.031 seconds

Executed in 0.078 seconds

Executed in 0.047 seconds

gjh_a05 是主表 gjh_a06 是基表可以看的出基表中前面要好一些。。