Re: Advice on a weird query plan

On 17 apr, 07:52, Pat <pat.ca..._at_service-now.com> wrote:
> I have a bit of a followup I was hoping folks could help me with. I
> created histograms on the hidden tables in question and my test case
> started using an index range scan. I think my test case was incomplete
> though because a couple of other queries against the store are showing
> different behavior.
>
> Specifically:
>
> select * from sysevent where state='a' <- index range scan
> select * from sysevent where state='ab' <- index range scan
> select * from sysevent where state='abc' <- index range scan
> select * from sysevent where state='abcd' <- index range scan
> select * from sysevent where state='abcde' <- index range scan
> select * from sysevent where state='abcdef' <- table access full
>
> There are no records in the database meeting either of those criteria
> e.g. all queries return nothing.
>
> Seems like any query term with length >=6 is doing a table access
> full.
>
> Did I do my stats wrong or something here?
>
> Any idea why the longer query string would put it into a table scan?
>
> SQL> explain plan for select * from sysevent where "state" = 'abcde';
>
> Explained.
>
> SQL> SELECT * FROM TABLE(dbms_xplan.display);
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------­-----
> Plan hash value: 1415456384
>
> ---------------------------------------------------------------------------­-----
> --------------
>
> | Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
> U)| Time     |
>
> ---------------------------------------------------------------------------­-----
> --------------
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------­-----
> |   0 | SELECT STATEMENT            |                |     1 |   247 |     4   (
> 0)| 00:00:01 |
>
> |   1 |  TABLE ACCESS BY INDEX ROWID| SYSEVENT       |     1 |   247 |
> 4   (
> 0)| 00:00:01 |
>
> |*  2 |   INDEX RANGE SCAN          | SYSEVENT_STATE |     1 |       |     3
> (
> 0)| 00:00:01 |
>
> ---------------------------------------------------------------------------­-----
> --------------
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------­-----
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
>    2 -
> access(NLSSORT("state",'nls_sort=''BINARY_CI''')=HEXTORAW('616263646500')
>  )
>
> 14 rows selected.
>
> SQL> explain plan for select * from sysevent where "state" = 'abcdef';
>
> Explained.
>
> SQL> SELECT * FROM TABLE(dbms_xplan.display);
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------­-----
> Plan hash value: 122562926
>
> ---------------------------------------------------------------------------­---
> | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time
> |
> ---------------------------------------------------------------------------­---
> |   0 | SELECT STATEMENT  |          |  4660K|  1097M| 37452   (2)| 00:07:30
> |
> |*  1 |  TABLE ACCESS FULL| SYSEVENT |  4660K|  1097M| 37452   (2)|
> 00:07:30 |
> ---------------------------------------------------------------------------­---
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------­-----
>
>    1 -
> filter(NLSSORT("state",'nls_sort=''BINARY_CI''')=HEXTORAW('616263
>               64656600') )
>
> 14 rows selected.

Check this Q&A from AskTom:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:707586567563#51452203661753 (and also the follow-up Q&A a litle below it)

HTH.

--
Jeroen