بتاريخ الخميس، ٢١ فبراير، ٢٠١٣، جاء من ddf orat...@msn.com:
> > > On Wednesday, February 20, 2013 12:50:09 PM UTC-7, Ninja Li wrote: >> >> Thanks for your replay. >> >> There is b-tree index on last_name, Partition is actually on a different >> column bill_num, which is also used for search. However in this case, it is >> the last name search that is slow. >> >> I should also mention that the p_in_lastname input parameter is always >> upper, e.g. 'SMITH%'. The search, e,g, 'SMITH%' can return multiple users, >> e.g. John Smith, Bill Smith, etc. >> >> Ning >> >> >>> >>> That doesn't answer the question of whether or not Oracle is actually > using your index -- it can, of course, since the wildcard is at the end of > the string: > > SQL> create table poobah( > 2 poobah_id number, > 3 last_name varchar2(40), > 4 details varchar2(30), > 5 poobah_dt date, > 6 constraint poobah_pk > 7 primary key(poobah_id) > 8 ); > > Table created. > > SQL> > SQL> create index poobah_lastnm_idx > 2 on poobah(last_name); > > Index created. > > SQL> > SQL> create table yamp( > 2 poobah_id number, > 3 fishsauce varchar2(30) > 4 ); > > Table created. > > SQL> > SQL> create index yamp_poobah_id on > 2 yamp(poobah_id); > > Index created. > > SQL> > SQL> alter table yamp add constraint yamp_poobah_fk foreign key > (poobah_id) references poobah; > > Table altered. > > SQL> > SQL> begin > 2 for i in 1..10000 loop > 3 if mod(i, 47) = 0 then > 4 insert into poobah > 5 values(i, 'SMITH'||i, 'Wow this is cool stuff', > sysdate); > 6 else > 7 insert into poobah > 8 values(i, 'SMOOTH'||i, 'Wow this is cool stuff', > sysdate); > 9 end if; > 10 insert into yamp > 11 values (i, 'My elbows need lots of lotion'); > 12 end loop; > 13 > 14 commit; > 15 > 16 end; > 17 / > > PL/SQL procedure successfully completed. > > SQL> > SQL> set autotrace on > SQL> > SQL> select p.poobah_id, p.last_name, p.details, y.fishsauce > 2 from poobah p join yamp y on (y.poobah_id = p.poobah_id) > 3 where p.last_name like upper('&1'||'%'); > Enter value for 1: smith > old 3: where p.last_name like upper('&1'||'%') > new 3: where p.last_name like upper('smith'||'%') > > POOBAH_ID LAST_NAME DETAILS > FISHSAUCE > ---------- ---------------------------------------- > ------------------------------ ------------------------------ > 1034 SMITH1034 Wow this is cool stuff > My elbows need lots of lotion > 1081 SMITH1081 Wow this is cool stuff > My elbows need lots of lotion > 1128 SMITH1128 Wow this is cool stuff > My elbows need lots of lotion > 1175 SMITH1175 Wow this is cool stuff > My elbows need lots of lotion > 1222 SMITH1222 Wow this is cool stuff > My elbows need lots of lotion > 1269 SMITH1269 Wow this is cool stuff > My elbows need lots of lotion > 1316 SMITH1316 Wow this is cool stuff > My elbows need lots of lotion > 1363 SMITH1363 Wow this is cool stuff > My elbows need lots of lotion > 141 SMITH141 Wow this is cool stuff > My elbows need lots of lotion > 1410 SMITH1410 Wow this is cool stuff > My elbows need lots of lotion > 1457 SMITH1457 Wow this is cool stuff > My elbows need lots of lotion > > POOBAH_ID LAST_NAME DETAILS > FISHSAUCE > ---------- ---------------------------------------- > ------------------------------ ------------------------------ > 1504 SMITH1504 Wow this is cool stuff > My elbows need lots of lotion > 1551 SMITH1551 Wow this is cool stuff > My elbows need lots of lotion > 1598 SMITH1598 Wow this is cool stuff > My elbows need lots of lotion > 1645 SMITH1645 Wow this is cool stuff > My elbows need lots of lotion > 1692 SMITH1692 Wow this is cool stuff > My elbows need lots of lotion > 1739 SMITH1739 Wow this is cool stuff > My elbows need lots of lotion > 1786 SMITH1786 Wow this is cool stuff > My elbows need lots of lotion > 1833 SMITH1833 Wow this is cool stuff > My elbows need lots of lotion > 188 SMITH188 Wow this is cool stuff > My elbows need lots of lotion > 1880 SMITH1880 Wow this is cool stuff > My elbows need lots of lotion > 1927 SMITH1927 Wow this is cool stuff > My elbows need lots of lotion > > POOBAH_ID LAST_NAME DETAILS > FISHSAUCE > ---------- ---------------------------------------- > ------------------------------ ------------------------------ > 1974 SMITH1974 Wow this is cool stuff > My elbows need lots of lotion > 2021 SMITH2021 Wow this is cool stuff > My elbows need lots of lotion > 2068 SMITH2068 Wow this is cool stuff > My elbows need lots of lotion > 2115 SMITH2115 Wow this is cool stuff > My elbows need lots of lotion > 2162 SMITH2162 Wow this is cool stuff > My elbows need lots of lotion > 2209 SMITH2209 Wow this is cool stuff > My elbows need lots of lotion > 2256 SMITH2256 Wow this is cool stuff > My elbows need lots of lotion > 2303 SMITH2303 Wow this is cool stuff > My elbows need lots of lotion > 235 SMITH235 Wow this is cool stuff > My elbows need lots of lotion > 2350 SMITH2350 Wow this is cool stuff > My elbows need lots of lotion > 2397 SMITH2397 Wow this is cool stuff > My elbows need lots of lotion > > POOBAH_ID LAST_NAME DETAILS > FISHSAUCE > ---------- ---------------------------------------- > ------------------------------ ------------------------------ > 2444 SMITH2444 Wow this is cool stuff > My elbows need lots of lotion > 2491 SMITH2491 Wow this is cool stuff > My elbows need lots of lotion > 2538 SMITH2538 Wow this is cool stuff > My elbows need lots of lotion > 2585 SMITH2585 Wow this is cool stuff > My elbows need lots of lotion > 2632 SMITH2632 Wow this is cool stuff > My elbows need lots of lotion > 2679 SMITH2679 Wow this is cool stuff > My elbows need lots of lotion > 2726 SMITH2726 Wow this is cool stuff > My elbows need lots of lotion > 2773 SMITH2773 Wow this is cool stuff > My elbows need lots of lotion > 282 SMITH282 Wow this is cool stuff > My elbows need lots of lotion > 2820 SMITH2820 Wow this is cool stuff > My elbows need lots of lotion > 2867 SMITH2867 Wow this is cool stuff > My elbows need lots of lotion > > POOBAH_ID LAST_NAME DETAILS > FISHSAUCE > ---------- ---------------------------------------- > ------------------------------ ------------------------------ > 2914 SMITH2914 Wow this is cool stuff > My elbows need lots of lotion > 2961 SMITH2961 Wow this is cool stuff > My elbows need lots of lotion > 3008 SMITH3008 Wow this is cool stuff > My elbows need lots of lotion > 3055 SMITH3055 Wow this is cool stuff > My elbows need lots of lotion > 3102 SMITH3102 Wow this is cool stuff > My elbows need lots of lotion > 3149 SMITH3149 Wow this is cool stuff > My elbows need lots of lotion > 3196 SMITH3196 Wow this is cool stuff > My elbows need lots of lotion > 3243 SMITH3243 Wow this is cool stuff > My elbows need lots of lotion > 329 SMITH329 Wow this is cool stuff > My elbows need lots of lotion > 3290 SMITH3290 Wow this is cool stuff > My elbows need lots of lotion > 3337 SMITH3337 Wow this is cool stuff > My elbows need lots of lotion > > POOBAH_ID LAST_NAME DETAILS > FISHSAUCE > ---------- ---------------------------------------- > ------------------------------ ------------------------------ > 3384 SMITH3384 Wow this is cool stuff > My elbows need lots of lotion > 3431 SMITH3431 Wow this is cool stuff > My elbows need lots of lotion > 3478 SMITH3478 Wow this is cool stuff > My elbows need lots of lotion > 3525 SMITH3525 Wow this is cool stuff > My elbows need lots of lotion > 3572 SMITH3572 Wow this is cool stuff > My elbows need lots of lotion > 3619 SMITH3619 Wow this is cool stuff > My elbows need lots of lotion > 3666 SMITH3666 Wow this is cool stuff > My elbows need lots of lotion > 3713 SMITH3713 Wow this is cool stuff > My elbows need lots of lotion > 376 SMITH376 Wow this is cool stuff > My elbows need lots of lotion > 3760 SMITH3760 Wow this is cool stuff > My elbows need lots of lotion > 3807 SMITH3807 Wow this is cool stuff > My elbows need lots of lotion > > POOBAH_ID LAST_NAME DETAILS > FISHSAUCE > ---------- ---------------------------------------- > ------------------------------ ------------------------------ > 3854 SMITH3854 Wow this is cool stuff > My elbows need lots of lotion > 3901 SMITH3901 Wow this is cool stuff > My elbows need lots of lotion > 3948 SMITH3948 Wow this is cool stuff > My elbows need lots of lotion > 3995 > -- -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en --- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To unsubscribe from this group and stop receiving emails from it, send an email to oracle-plsql+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.