Re: FBI? MAX? INDEX FULL SCAN (MIN/MAX)?

On Mar 12, 7:18 am, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> On Mar 12, 4:26 am, "lfree" <a..._at_a.a> wrote:
>
>
>
> > FBI? MAX? INDEX FULL SCAN (MIN/MAX)?
>
> > select * from v$version
> > BANNER
> > ----------------------------------------------------------------
> > Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
> > PL/SQL Release 10.2.0.1.0 - Production
> > CORE 10.2.0.1.0 Production
> > TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
> > NLSRTL Version 10.2.0.1.0 - Production
>
> > scott> create table t as select * from all_objects;
>
> > scott> create index if_t_object_name on t(lower(object_name));
>
> > scott> create index i_t_object_name on t(object_name);
>
> > scott>BEGIN
> > SYS.DBMS_STATS.GATHER_TABLE_STATS (
> > OwnName => 'SCOTT'
> > ,TabName => 'T'
> > ,Method_Opt => 'FOR ALL COLUMNS SIZE 1 '
> > ,Degree => 4
> > ,Cascade => TRUE;
> > END;
>
> > scott> set autotrace traceonly ;
> > scott> select max(object_name) from t ;
>
> > Plan hash value: 348001784
>
> > ---------------------------------------------------------------------------­-
> > ------------------
> > | Id | Operation | Name | Rows | Bytes | Cost
> > (%CPU)| Time |
> > ---------------------------------------------------------------------------­-
> > ------------------
> > | 0 | SELECT STATEMENT | | 1 | 24 | 2
> > (0)| 00:00:01 |
> > | 1 | SORT AGGREGATE | | 1 | 24 |
> > | |
> > | 2 | INDEX FULL SCAN (MIN/MAX)| I_T_OBJECT_NAME | 50031 | 1172K| 2
> > (0)| 00:00:01 |
> > ---------------------------------------------------------------------------­-
> > ------------------
>
> > 0 recursive calls
> > 0 db block gets
> > 2 consistent gets
> > 0 physical reads
> > 0 redo size
> > 439 bytes sent via SQL*Net to client
> > 384 bytes received via SQL*Net from client
> > 2 SQL*Net roundtrips to/from client
> > 0 sorts (memory)
> > 0 sorts (disk)
> > 1 rows processed
>
> > scott> select max(lower(object_name)) from t ;
>
> > Plan hash value: 3320467716
>
> > ---------------------------------------------------------------------------­-
> > --------------
> > | Id | Operation | Name | Rows | Bytes | Cost
> > (%CPU)| Time |
> > ---------------------------------------------------------------------------­-
> > --------------
> > | 0 | SELECT STATEMENT | | 1 | 24 | 57
> > (2)| 00:00:01 |
> > | 1 | SORT AGGREGATE | | 1 | 24 |
> > | |
> > | 2 | INDEX FAST FULL SCAN| IF_T_OBJECT_NAME | 50031 | 1172K| 57
> > (2)| 00:00:01 |
> > ---------------------------------------------------------------------------­-
> > --------------
>
> > 0 recursive calls
> > 0 db block gets
> > 253 consistent gets
> > 0 physical reads
> > 0 redo size
> > 446 bytes sent via SQL*Net to client
> > 384 bytes received via SQL*Net from client
> > 2 SQL*Net roundtrips to/from client
> > 0 sorts (memory)
> > 0 sorts (disk)
> > 1 rows processed
>
> > why use FBI query max(lower(object_name)) ? execute plan is "INDEX FAST FULL
> > SCAN".
>
> Tested on Oracle 10.2.0.2 64 bit:
>
> The setup:
> CREATE TABLE T1 (
> C1 NUMBER(12),
> C2 NUMBER(12,8) NOT NULL,
> PRIMARY KEY (C1));
>
> Insert 7200 rows into the table, and calculate the value of SIN for
> the degree value in C1:
> INSERT INTO
> T1
> SELECT
> ROWNUM C1,
> ROUND(SIN(ROWNUM*3.141592/180),8) C2
> FROM
> DUAL
> CONNECT BY
> LEVEL<=360*20;
>
> COMMIT;
>
> Create two function based indexes on the table, the first of which
> should contain values that are identical to the contents of C2:
> CREATE INDEX IND_T1 ON T1(ROUND(SIN(C1*3.141592/180),8));
> CREATE INDEX IND_T1_2 ON T1(ROUND(COS(C1*3.141592/180),8));
>
> Gather statatistics on the table and indexes:
> EXEC
> DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TABLE_OWNER_HERE',TABNAME=>'T1',CASCADE=>TRUE);
>
> Let DBMS Xplan return additional details:
> ALTER SESSION SET STATISTICS_LEVEL=ALL;
>
> The first test, see if the primary key index on C1 will be used:
> SELECT
> MAX(C1)
> FROM
> T1;
>
> Check the DBMS Xplan output:
> SELECT
> *
> FROM
> TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
>
> PLAN_TABLE_OUTPUT
> =================
> -----------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows |
> A-Rows | A-Time | Buffers |
> -----------------------------------------------------------------------------------------------------
> | 1 | SORT AGGREGATE | | 1 | 1
> | 1 |00:00:00.01 | 2 |
> | 2 | INDEX FULL SCAN (MIN/MAX)| SYS_C0022496 | 1 | 7200
> | 1 |00:00:00.01 | 2 |
> -----------------------------------------------------------------------------------------------------
>
> Now, let's see if Oracle uses the same technique on the first function
> based index that we created:
> SELECT
> MAX(ROUND(SIN(C1*3.141592/180),8))
> FROM
> T1;
>
> SELECT
> *
> FROM
> TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
>
> ------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-
> Rows | A-Time | Buffers |
> ------------------------------------------------------------------------------------------------
> | 1 | SORT AGGREGATE | | 1 | 1 |
> 1 |00:00:00.18 | 24 |
> | 2 | INDEX FAST FULL SCAN| SYS_C0022496 | 1 | 7200 |
> 7200 |00:00:00.01 | 24 |
> ------------------------------------------------------------------------------------------------
>
> Oracle did not even bother to use the function based index. Instead,
> it performed a fast full scan of the primary key index.
>
> Now, let's force Oracle to use the function based index:
> SELECT /*+ INDEX(T1 IND_T1) */
> MAX(ROUND(SIN(C1*3.141592/180),8))
> FROM
> T1;
>
> SELECT
> *
> FROM
> TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
>
> ------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-
> Rows | A-Time | Buffers |
> ------------------------------------------------------------------------------------------------
> | 1 | SORT AGGREGATE | | 1 | 1 |
> 1 |00:00:00.01 | 2 |
> | 2 | FIRST ROW | | 1 | 7200 |
> 1 |00:00:00.01 | 2 |
> | 3 | INDEX FULL SCAN (MIN/MAX)| IND_T1 | 1 | 7200 |
> 1 |00:00:00.01 | 2 |
> ------------------------------------------------------------------------------------------------
>
> Oracle used the function based index this time, and made use of the
> MIN/MAX as it did with the primary key index.
>
> Let's gather statistics like you did and repeat the test.
> BEGIN
> DBMS_STATS.GATHER_TABLE_STATS (
> OwnName => 'TABLE_OWNER_HERE',
> TabName => 'T1',
> Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',
> Degree => 4,
> Cascade => TRUE);
> END;
> /
>
> ALTER SYSTEM FLUSH SHARED_POOL;
>
> The SQL statements produced the same DBMS Xplans.
>
> I suggest that you compare the output of DBMS Xplan with the SET
> AUTOTRACE TRACEONLY output to see if the plans are the same.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Charles,

In 10gR2 at least, autotrace uses dbms_xplan to display the explain plan.
A 10046 trace would reveal that autotrace is calling the following sql to display the explain plan:
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', : 1))

I checked out this issue in 10.2.0.2 and 10.2.0.3 and on first glance (using 10053 trace), it appears that CBO is handling it differently in the two versions.
In 10.2.0.2, it appears that CBO is applying optimizer_index_cost_adj to FFS while in 10.2.0.3 it is not. So in 10.2.0.3 test I did, it appears to have picked up the FBI since the cost after applying o_i_c_a appeared to be less.

However, I read Bug 4483286 and it states that o_i_c_a should not be applied to FFS.

not sure whats going on here...

Anurag Received on Mon Mar 12 2007 - 12:09:49 CDT