Re: Advice on a weird query plan
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