Re: Advice on a weird query plan

On Apr 17, 6:52 am, 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.

hopefully someone else here can confirm. But, I understood that histograms were only considered on the first few (6?) charactaers on a charcter datatype. If correct, this could explain this behaviour. Received on Thu Apr 17 2008 - 02:41:29 CDT