بتاريخ الخميس، ٢١ فبراير، ٢٠١٣، جاء من 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.
