5686968004e4f665c3.jpg (305.85 KB, 下载次数: 64)
2025-4-17 08:41 上传
我看了一下，这个表很简单的表，怎么不走索引呢，难道没有索引，
于是我远程看了一下
SQL> r
1* select INDEX_OWNER,INDEX_NAME,COLUMN_NAME,COLUMN_POSITION,table_name from dba_ind_columns where table_name='YS_ZY_JBZD' order by 2,4
INDEX_OWNER INDEX_NAME COLUMN_NAME COLUMN_POSITION TABLE_NAME
------------------------------ ------------------------------ ------------------------------ --------------- ------------------------------
HIS IDX_YS_ZY_JBZD_JBDM JBDM 1 YS_ZY_JBZD
HIS IDX_YS_ZY_JBZD_JZHM JZHM 1 YS_ZY_JBZD
HIS PK_YS_ZY_JBZD JLBH 1 YS_ZY_JBZD
HIS PK_YS_ZY_JBZD1 JLBH 1 YS_ZY_JBZD
HIS PK_YS_ZY_JBZD1 JBDM 2 YS_ZY_JBZD
再次验证，看看计划情况
SQL> explain plan
2 for
3 select * from his.YS_ZY_JBZD where JLBH=929;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 913959468
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 3951 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| YS_ZY_JBZD | 1 | 133 | 3951 (1)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - filter(TO_NUMBER("JLBH")=929)
13 rows selected.
发现，走了 TO_NUMBER("JLBH")=929 ，看来进行了类型转换
再看表定义
SQL> desc his.YS_ZY_JBZD
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
JLBH NOT NULL VARCHAR2(18)
JZHM NOT NULL VARCHAR2(18)
BRBH NOT NULL VARCHAR2(18)
ZXLB NUMBER(1)
ZDLB NOT NULL VARCHAR2(20)
JBZH NOT NULL VARCHAR2(2)
ZNXH NUMBER(2)
ZZBJ NUMBER(1)
JBDM VARCHAR2(18)
JBMC VARCHAR2(50)
FJBS NUMBER(18)
MSZD VARCHAR2(100)
ZGQK VARCHAR2(20)
ZDYS NOT NULL VARCHAR2(10)
ZDSJ NOT NULL DATE
ZFBZ NUMBER(1)
TJBZ NUMBER(1)
BKZT NUMBER(1)
QZBZ NUMBER(1)
。。。
是一个字符类型，你要去和数字对应，必须进行类型的隐式转换，导致给字段加了函数，当然不走索引了。
我们给加一个字符试一下：
SQL> explain plan
2 for
3 select * from his.YS_ZY_JBZD where JLBH='929';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1874344706
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| YS_ZY_JBZD | 1 | 133 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_YS_ZY_JBZD | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("JLBH"='929')
14 rows selected.
SQL>
你看不是走上了吗
| 欢迎光临 重庆思庄Oracle、、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |