Re: Advice on a weird query plan
Date: Thu, 17 Apr 2008 00:41:29 -0700 (PDT)
Message-ID: <19d98847-3e5f-497a-ac09-24f9cd75f378@y21g2000hsf.googlegroups.com>
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