| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: FBI? MAX? INDEX FULL SCAN (MIN/MAX)?
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'));
| 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);
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.
Received on Mon Mar 12 2007 - 06:18:09 CDT
 |
 |