xugu数据库函数索引

〇〇 · 发表于 2012-10-10 13:51

〇〇发表于 2012-10-6 22:23
建函数索引后的计划和执行
SQL> create index idx_test_fbi_name on test_fbi(atoi(name));

5.0版居然是好的
Please input server URL:localhost
Please input server port:5138
Please input database name:SYSTEM
please input user name:SYSDBA
please input password:SYSDBA
Connect ok.

SQL>
SQL> create table test_fbi( id int,name char(10));

Use time:15 ms.
ok.

SQL> declare
2 begin
3 for i in 1..100000 loop
4 insert into test_fbi values(i,i);
5 end for;
6 end;

Use time:656 ms.
ok.

SQL> create index idx_test_fbi_name on test_fbi(atoi(name));

Use time:125 ms.
ok.

SQL>

SQL> select * from test_fbi where atoi(name)=100;

Use time:0 ms.

TEST_FBI.ID(INTEGER)  | TEST_FBI.NAME(CHAR(10))  |

-------------------------------------L1-------------------------------------
100 | 100 |

Total 1 records.

SQL>

SQL> explain select * from test_fbi where atoi(name)=100;

Use time:0 ms.

plan_path(VARCHAR(-1))  |

-------------------------------------L1-------------------------------------
IndexScan[cost=700,result_num=40000](table=TEST_FBI,index=IDX_TEST_FBI_NAME) |

Total 1 records.

SQL>

SQL> SHOW VERSION ;

Use time:0 ms.

VERSION(VARCHAR(-1))  |

-------------------------------------L1-------------------------------------
XuGu SQL Server 5.0.0 |

Total 1 records.

SQL>