對列進行連線操作會影響索引的使用

LuiseDalian發表於2014-01-13
原文網址 : http://blog.itpub.net/17013648/viewspace-1069944/
索引

解決辦法：不對列進行連線操作，而是對要比較的字面量應用函式，從而滿足列的特定要求。

點選(此處)摺疊或開啟

  1. --建立表
  2. hr@TESTDB11>create table employees1 as select * from employees;

  4. Table created.

  6. --在first_name和last_name列上分別建立索引
  7. hr@TESTDB11>create index idx_employees1_fn on employees1(first_name);

  9. Index created.

  11. hr@TESTDB11>create index idx_employees1_ln on employees1(last_name);

  13. Index created.

  15. --對列進行字串的連線,沒有走索引
  16. hr@TESTDB11>select e.first_name, e.last_name from employees1 e where first_name || \' \' || last_name = \'Douglas Grant\';

點選(此處)摺疊或開啟

  1. --調整之後走索引的可取的做法
  2. hr@TESTDB11>select e.first_name, e.last_name from employees1 e where first_name = \'Douglas\' and last_name = \'Grant\';


點選(此處)摺疊或開啟

  1. --姓名一起儲存在一個變數中的解決辦法
  2. hr@TESTDB11>select first_name, last_name from employees1
  3.   2 where first_name = substr(\'&&name\', 1, instr(\'&name\', \' \') - 1)
  4.   3 AND last_name = SUBSTR(\'&name\', INSTR(\'&name\', \' \') + 1);
  5.   
  6. old 2: where first_name = substr(\'&&name\', 1, instr(\'&name\', \' \') - 1)
  7. new 2: where first_name = substr(\'Douglas Grant\', 1, instr(\'Douglas Grant\', \' \') - 1)
  8. old 3: AND last_name = SUBSTR(\'&name\', INSTR(\'&name\', \' \') + 1)
  9. new 3: AND last_name = SUBSTR(\'Douglas Grant\', INSTR(\'Douglas Grant\', \' \') + 1)



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17013648/viewspace-1069944/，如需轉載，請註明出處，否則將追究法律責任。

相關文章