对引索优化问题

Question

1、尽量避免对索引列进行计算    根据这句话的意思我开始了自己的测试
SQL> desc a05;
Name  Type          Nullable Default Comments                                      
----- ------------- -------- ------- --------------------------------------------- 
A0500 VARCHAR2(2)   Y                门店                                          
A0501 VARCHAR2(6)                    人员编码                                      
A0502 VARCHAR2(10)  Y                人员姓名                                      
A0503 VARCHAR2(10)  Y                简称                                          
A0504 VARCHAR2(32)  Y                密码                                          
A0505 VARCHAR2(255) Y                后台权限                                      
A0506 VARCHAR2(1)   Y                前台权限: 0-超级 1-收款组长 2-普通款员 5-禁用 
A0507 VARCHAR2(4)   Y                所属部门                                      
A0508 VARCHAR2(8)   Y                前台角色                                      
A0509 CHAR(1)       Y                查询权限:0-个人 1-部门 2-分店 3-总店          
A0510 VARCHAR2(8)   Y                后台角色                                      
A0511 DATE          Y                入职日期                                      
A0512 DATE          Y                离职日期    (1)
   我先不建索引走了一下。SQL> select count(*) from gjh_a05;  COUNT(*)
----------
      4753Executed in 0.078 secondsSQL> select * from gjh_a05 where a0506>'5' and rownum<2;A0500 A0501  A0502      A0503      A0504                A0505                                                                            A0506 A0507 A0508    A0509 A0510    A0511       A0512
----- ------ ---------- ---------- -------------------- -------------------------------------------------------------------------------- ----- ----- -------- ----- -------- ----------- -----------
10    5166   宋宁                  bxtp                 5                                                                                6     0603  13       0     0                    Executed in 0.078 secondsSQL> select * from gjh_a05 where a0506>5 and rownum<2;A0500 A0501  A0502      A0503      A0504                A0505                                                                            A0506 A0507 A0508    A0509 A0510    A0511       A0512
----- ------ ---------- ---------- -------------------- -------------------------------------------------------------------------------- ----- ----- -------- ----- -------- ----------- -----------
10    5166   宋宁                  bxtp                 5                                                                                6     0603  13       0     0                    Executed in 0.109 secondsSQL> select * from gjh_a05 where a0506>5 and rownum<2;A0500 A0501  A0502      A0503      A0504                A0505                                                                            A0506 A0507 A0508    A0509 A0510    A0511       A0512
----- ------ ---------- ---------- -------------------- -------------------------------------------------------------------------------- ----- ----- -------- ----- -------- ----------- -----------
10    5166   宋宁                  bxtp                 5                                                                                6     0603  13       0     0                    Executed in 0.031 secondsSQL> select * from gjh_a05 where a0506>'5' and rownum<2;A0500 A0501  A0502      A0503      A0504                A0505                                                                            A0506 A0507 A0508    A0509 A0510    A0511       A0512
----- ------ ---------- ---------- -------------------- -------------------------------------------------------------------------------- ----- ----- -------- ----- -------- ----------- -----------
10    5166   宋宁                  bxtp                 5                                                                                6     0603  13       0     0                    Executed in 0.078 secondsSQL> select * from gjh_a05 where a0506>'5' and rownum<2;A0500 A0501  A0502      A0503      A0504                A0505                                                                            A0506 A0507 A0508    A0509 A0510    A0511       A0512
----- ------ ---------- ---------- -------------------- -------------------------------------------------------------------------------- ----- ----- -------- ----- -------- ----------- -----------
10    5166   宋宁                  bxtp                 5                                                                                6     0603  13       0     0                    Executed in 0.093 seconds
根据这些数我无语了。a0506 是一个varchar 型。。它和''比较效率应该更高才对。可是怎么就低了呢。。

Answer 1

SQL> create index gjh_a05_a0506 on gjh_a05(a0506) ;Index createdExecuted in 0.078 secondsSQL> select * from gjh_a05 where a0506*5>4 and rownum<2;A0500 A0501  A0502      A0503      A0504                A0505                                                                            A0506 A0507 A0508    A0509 A0510    A0511       A0512
----- ------ ---------- ---------- -------------------- -------------------------------------------------------------------------------- ----- ----- -------- ----- -------- ----------- -----------
02    7018   王淑敏                s                                                                                                     5     52    0        0     0                    Executed in 0.031 secondsSQL> select * from gjh_a05 where a0506>4/5 and rownum<2;A0500 A0501  A0502      A0503      A0504                A0505                                                                            A0506 A0507 A0508    A0509 A0510    A0511       A0512
----- ------ ---------- ---------- -------------------- -------------------------------------------------------------------------------- ----- ----- -------- ----- -------- ----------- -----------
02    7018   王淑敏                s                                                                                                     5     52    0        0     0                    Executed in 0.047 secondsSQL> 

Answer 2

解决一个。。
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>5SELECT STATEMENT, GOAL = CHOOSE
 TABLE ACCESS FULL Object owner=FZDC Object name=GJH_A05

Answer 3

我猜一个可能：虽然a0506是varchar类型，但是在比较大小的时候，被隐式转化成了number再比较的大小，楼主实验证明一下。