Re: Advice on a weird query plan

From: Pat <pat.casey_at_service-now.com>
Date: Wed, 16 Apr 2008 22:52:19 -0700 (PDT)
Message-ID: <54a55e5f-0dc2-4628-8f06-bf1982f95e31@b64g2000hsa.googlegroups.com>


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. Received on Thu Apr 17 2008 - 00:52:19 CDT

Original text of this message