not getting same plan
bmk, March 17, 2011 - 6:32 am UTC
Dear Tom,
I did exactly as you did but did get different plan. Instead of index fast full scan it was full table scan.
SQL> select count(*) from t where owner = 'SCOTT';
Execution Plan
----------------------------------------------------------
Plan hash value: 1842905362
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 276 (2)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| T | 587 | 9979 | 276 (2)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NLSSORT("OWNER",'nls_sort=''BINARY_CI''')=HEXTORAW('73636F
747400') )
Note
-----
- dynamic sampling used for this statement (level=2)
One more interesting thing is that though there are only 18 rows for SCOTT in the plan it is showing 587 rows.
SQL> select count(*) from t where owner='SCOTT';
COUNT(*)
----------
18
Why there is such difference? I am using 11gR2.
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
March 17, 2011 - 8:38 am UTC
look at your filter, you have different NLS settings that are preventing your index from being used.
Thank you very much
Gopal Biswal, March 18, 2011 - 5:08 am UTC
Hi Tom, Thank you very much for the clarification!
Gopal Biswal, March 18, 2011 - 5:34 am UTC
As you mentioned and demonstrated that the below statement is not true
" and at least one column in the index key has the NOT NULL constraint."
But I could see a discussion here
http://forums.oracle.com/forums/message.jspa?messageID=2389020#2389020 mentioning that
the column should be NOT NULL. Am I missing something. Thanks for your time.
Regards;
Gopal
March 18, 2011 - 8:10 am UTC
I wanna performe an index full scan on the table instead of a full table scan although that a full table sacn is likely to be faster than an index full scan.
So, i wrote down a following sql statement
SELECT /*+ INDEX(t XPKGROUP_CUSTOMER)*/t.* FROM group_customer t;
that is the beginning of that discussion. In that case - the index XPKGROUP_CUSTOMER must have at least one NOT NULL attribute because every single row in T is going to be counted and if there is a row in T with all of the indexed attributes "NULL" - it won't appear in the index. The index does not point to EVERY single row in the table.
My counter example demonstrated an index fast full scan - where by we used the smaller index instead of the table - to find all of the rows in the table SUCH THAT "filter("OWNER"='SCOTT')". Now, if OWNER = 'SCOTT', then owner is known to be NOT NULL for the set of rows of interest - therefore any index that contained OWNER would be good enough to fast full scan to count those rows.
Understand the concepts here:
a b*tree index on a set of attributes that are all NULLABLE does not point to every row in the table (does not HAVE to point to every row in the table is more accurate).
if you have to access EVERY ROW in the table, you cannot use that index.
if you have to access a subset of rows in the table, and the subset is defined as a predicate on at least one of the indexed columns AND that predicate is not of the form "column is null", then we CAN use the index to find those rows - since we know now that that attribute is NOT NULL and hence would appear in the index.
Gopal Biswal, March 18, 2011 - 8:57 am UTC
Hi Tom,
I understood it now. Thank you very much!
Regards;
Gopal
Expecting Index Full Scan but got INDEX_FFS
A reader, January 04, 2012 - 12:42 pm UTC
I was trying to reproduce your example earlier in this thread. I was expecting an index full scan, but instead got index fast full scan. Can you please explain why it's not using index_FFS?
SQL> CREATE TABLE t AS SELECT * FROM all_objects;
Table created.
SQL>
SQL> CREATE INDEX t_idx ON t(status, owner);
Index created.
SQL>
SQL> set autotrace traceonly explain
SQL> SELECT COUNT(*) FROM t WHERE owner = 'SCOTT';
Execution Plan
----------------------------------------------------------
Plan hash value: 995313729
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 8 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | INDEX FULL SCAN| T_IDX | 1 | 17 | 8 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
filter("OWNER"='SCOTT')
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> set autotrace off
SQL>
SQL> SELECT * FROM v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
January 04, 2012 - 3:31 pm UTC
index full scan is there? where do you see something else?
it would look like this for an index fast full scan:
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> SELECT /*+ index_ffs( t t_idx ) */ COUNT(*) FROM t WHERE owner = 'SCOTT';
Execution Plan
----------------------------------------------------------
Plan hash value: 1058879072
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 69 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | INDEX FAST FULL SCAN| T_IDX | 12 | 204 | 69 (2)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SCOTT')
Expecting INDEX_FFS but got Index Full Scan
A reader, January 04, 2012 - 4:04 pm UTC
Sorry I meant to say "expecting INDEX_FFS but got index full scan."
So without using the INDEX_FFS hint, under what conditions would Oracle use an INDEX_FFS? You mentioned that using INDEX_FFS is equivalent to using a skinnier version of the table. So I'd expect Oracle would use a IDNEX_FFS for the following SQL, but it uses an index full scan instead.
SQL> SELECT status, owner FROM t;
Execution Plan
----------------------------------------------------------
Plan hash value: 2277287974
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7399 | 101K| 6 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | T_IDX | 7399 | 101K| 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
January 04, 2012 - 5:49 pm UTC
interestingly, I get the FFS right out of the box:
ops$tkyte%ORA11GR2> CREATE TABLE t AS SELECT * FROM all_objects;
Table created.
ops$tkyte%ORA11GR2> CREATE INDEX t_idx ON t(status, owner);
Index created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> SELECT COUNT(*) FROM t WHERE owner = 'SCOTT';
Execution Plan
----------------------------------------------------------
Plan hash value: 1058879072
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 69 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | INDEX FAST FULL SCAN| T_IDX | 12 | 204 | 69 (2)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SCOTT')
Note
-----
- dynamic sampling used for this statement (level=2)
ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> SELECT COUNT(*) FROM t WHERE owner = 'SCOTT';
COUNT(*)
----------
4
ops$tkyte%ORA11GR2> select * from table( dbms_xplan.display_cursor() );
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1ydhgvzv0z1w9, child number 0
-------------------------------------
SELECT COUNT(*) FROM t WHERE owner = 'SCOTT'
Plan hash value: 1058879072
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 69 (100)| |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | INDEX FAST FULL SCAN| T_IDX | 12 | 204 | 69 (2)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SCOTT')
Note
-----
- dynamic sampling used for this statement (level=2)
23 rows selected.
what is the output of:
select name || '='|| value from v$parameter where isdefault='FALSE';
on your system? that cost looks really really low.
A reader, January 05, 2012 - 10:09 am UTC
SQL> select name || '='|| value from v$parameter where isdefault='FALSE';
NAME||'='||VALUE
-----------------------------------------------------------------------------------------------------------------------------------
processes=1000
sessions=1524
timed_statistics=TRUE
nls_length_semantics=CHAR
disk_asynch_io=FALSE
memory_target=3221225472
control_files=C:\APP\ADMINISTRATOR\ORADATA\PROTECT\CONTROL01.CTL, C:\APP\ADMINISTRATOR\ORADATA\PROTECT\CONTROL02.CTL, C:\APP\ADMINI
STRATOR\ORADATA\PROTECT\CONTROL03.CTL
db_block_size=8192
compatible=11.2.0.0.0
db_file_multiblock_read_count=16
fast_start_mttr_target=0
undo_management=AUTO
undo_tablespace=UNDOTBS1
undo_retention=10800
remote_login_passwordfile=EXCLUSIVE
db_domain=
job_queue_processes=10
session_max_open_files=20
db_name=protect
open_cursors=300
star_transformation_enabled=FALSE
optimizer_index_cost_adj=25
optimizer_index_caching=90
query_rewrite_enabled=FALSE
aq_tm_processes=1
25 rows selected.
January 05, 2012 - 10:21 am UTC
shame on you:
optimizer_index_cost_adj=25
optimizer_index_caching=90
that is why, you made single block IO really, artificially cheap.
it'll make it so you cannot really ask me questions here - you are unique, nothing will work the same for you as it does for 99.9999% of the rest of the world.
I would not permit the use of these:
nls_length_semantics=CHAR
db_file_multiblock_read_count=16
optimizer_index_cost_adj=25
optimizer_index_caching=90
nls_length - set it at the session level when you really really understand and know that you want it. If you have this set to char - and install any 3rd party code - you are likely unsupportable by that vendor.
db_file_multiblock_read_count should never be set since version 10g.
optimizer_index_* should be deprecated in favor of system statistics - they should not be set.
A reader, January 05, 2012 - 6:33 pm UTC
"optimizer_index_* should be deprecated in favor of system statistics - they should not be set."
As of what version does this statement apply? On p. 311 of "Effective Oracle by Design, you stated that
"The settings of OPTIMIZER_INDEX_CACHING= 90 and OPTIMIZER_INDEX_COST_ADJ = 25. These are typically appropriate for many transactoinal/OLTP systems."
January 10, 2012 - 9:08 pm UTC
as of about 10g - maybe even 9ir2 (that was the beginning of system statistics)
Effective Oracle by Design was written when 9ir2 was production, prior to 10g R1 hitting the streets.
NLS_LENGTH
A reader, January 05, 2012 - 6:36 pm UTC
Can you please elaborate on this statement? Thanks.
"nls_length - set it at the session level when you really really understand and know that you want it. If you have this set to char - and install any 3rd party code - you are likely unsupportable by that vendor."
January 10, 2012 - 9:10 pm UTC
I don't know what else to say
don't set it in the init.ora
set it in the session that really wants it - the session that meant to use the "varchar2(10 char) instead of just varchar2(10), but didn't"
use it with an alter session, not at the system level.
ops$tkyte%ORA11GR2> alter session set nls_length_semantics = char;
Session altered.
ying broman, July 09, 2012 - 2:41 am UTC
Hi,Tom,
I got exactly same question on index full scan and index fast full scan in the real application.
There r two server,one is prodution server,one is test server.test server is a copy from production server on Feb.
its 20s slower on production server than test server when user runs one same query,test server took only 16s .
I checked the explain sql
production server
SELECT STATEMENT ALL_ROWS Cost: 199.313
22 SORT ORDER BY Cost: 199.313 Bytes: 101.904.638 Cardinality: 243.791
21 FILTER
1 TABLE ACCESS FULL TABLE MAXIMO.WORKORDER Cost: 26.050 Bytes: 815.237.522 Cardinality: 1.950.329
20 UNION-ALL
8 NESTED LOOPS Cost: 4 Bytes: 414 Cardinality: 6
6 NESTED LOOPS Cost: 2 Bytes: 50 Cardinality: 1
4 NESTED LOOPS Cost: 2 Bytes: 68 Cardinality: 2
2 INDEX FAST FULL SCAN INDEX MAXIMO.MAXGROUP_NDX2 Cost: 2 Bytes: 3.135 Cardinality: 209
3 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.GROUPUSER_NDX1 Cost: 0 Bytes: 19 Cardinality: 1
5 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.SITEAUTH_NDX1 Cost: 0 Bytes: 16 Cardinality: 1
7 INDEX RANGE SCAN INDEX (UNIQUE) MAXIMO.APPAUTH_NDX1 Cost: 2 Bytes: 342 Cardinality: 18
19 FILTER
13 NESTED LOOPS Cost: 2 Bytes: 50 Cardinality: 1
11 NESTED LOOPS Cost: 2 Bytes: 34 Cardinality: 1
9 INDEX FAST FULL SCAN INDEX MAXIMO.MAXGROUP_NDX2 Cost: 2 Bytes: 1.305 Cardinality: 87
10 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.GROUPUSER_NDX1 Cost: 0 Bytes: 19 Cardinality: 1
12 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.SITEAUTH_NDX1 Cost: 0 Bytes: 16 Cardinality: 1
18 NESTED LOOPS Cost: 4 Bytes: 53 Cardinality: 1
16 NESTED LOOPS Cost: 2 Bytes: 34 Cardinality: 1
14 INDEX FAST FULL SCAN INDEX MAXIMO.MAXGROUP_NDX2 Cost: 2 Bytes: 1.305 Cardinality: 87
15 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.GROUPUSER_NDX1 Cost: 0 Bytes: 19 Cardinality: 1
17 INDEX RANGE SCAN INDEX (UNIQUE) MAXIMO.APPAUTH_NDX1 Cost: 2 Bytes: 38 Cardinality: 2
-------
test server
SELECT STATEMENT ALL_ROWS Cost: 149.155
22 SORT ORDER BY Cost: 149.155 Bytes: 74.874.478 Cardinality: 201.818
21 FILTER
1 TABLE ACCESS FULL TABLE MAXIMO.WORKORDER Cost: 21.289 Bytes: 598.995.824 Cardinality: 1.614.544
20 UNION-ALL
8 NESTED LOOPS Cost: 3 Bytes: 350 Cardinality: 5
6 NESTED LOOPS Cost: 1 Bytes: 50 Cardinality: 1
4 NESTED LOOPS Cost: 1 Bytes: 68 Cardinality: 2
2 INDEX FULL SCAN INDEX MAXIMO.MAXGROUP_NDX2 Cost: 1 Bytes: 2.985 Cardinality: 199
3 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.GROUPUSER_NDX1 Cost: 0 Bytes: 19 Cardinality: 1
5 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.SITEAUTH_NDX1 Cost: 0 Bytes: 16 Cardinality: 1
7 INDEX RANGE SCAN INDEX (UNIQUE) MAXIMO.APPAUTH_NDX1 Cost: 2 Bytes: 340 Cardinality: 17
19 FILTER
13 NESTED LOOPS Cost: 1 Bytes: 50 Cardinality: 1
11 NESTED LOOPS Cost: 1 Bytes: 34 Cardinality: 1
9 INDEX FULL SCAN INDEX MAXIMO.MAXGROUP_NDX2 Cost: 1 Bytes: 1.215 Cardinality: 81
10 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.GROUPUSER_NDX1 Cost: 0 Bytes: 19 Cardinality: 1
12 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.SITEAUTH_NDX1 Cost: 0 Bytes: 16 Cardinality: 1
18 NESTED LOOPS Cost: 3 Bytes: 54 Cardinality: 1
16 NESTED LOOPS Cost: 1 Bytes: 34 Cardinality: 1
14 INDEX FULL SCAN INDEX MAXIMO.MAXGROUP_NDX2 Cost: 1 Bytes: 1.215 Cardinality: 81
15 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.GROUPUSER_NDX1 Cost: 0 Bytes: 19 Cardinality: 1
17 INDEX RANGE SCAN INDEX (UNIQUE) MAXIMO.APPAUTH_NDX1 Cost: 2 Bytes: 40 Cardinality: 2
can u tell me what caused these?
A reader, March 14, 2013 - 10:23 am UTC
what makes optimizer switch between index full scan and index fast full scan
dhairyasheel, August 07, 2013 - 3:43 pm UTC
hello tom,
im facing issue at cust site wherein the only difference in the xplain plan for the same sql id is that for a particular index during good times we see index fast full scan and during bad times we see index full scan. the wait events observed are db file sequential read (good time) and direct path read temp (bad time). db is 11g r2. what i need to know is what factors can make the optimizer switch between these two types of index scans for a particular sql id
August 08, 2013 - 5:15 pm UTC
care to share the SQL and the schema (just as much of the schema as we need to understand the query), that'll help.
it'll be related to cardinality estimates and the presence or lack thereof of an order by.
New AP rejected (non-guess): favored index only range scan
Yuan, February 28, 2014 - 4:07 pm UTC
I ran across this message in a 10053 trace output:
New AP rejected (non-guess): favored index only range scan
The access path that was rejected was the one with the lowest cost according to the trace output. If I force its use by using an index hint, the performance is drastically better. Why is the optimizer rejecting the index?
I am on 11.1.0.7. I tried using all rows and first rows optimizer goal with no change in behavior. I did a Google search and this Russian article is the only result I found:
http://iusoltsev.wordpress.com/2012/09/21/optimizer_index_cost_adj-cbo-11-1/ The behavior described in the article is the same except that the magic value of optimizer_index_cost_adj for me is different.