pl/sql 语句的几点优化

1.

     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

Executed in 0.047 seconds

 

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