[20120130]函式索引與取max值的問題1.txt
[20120130]函式索引與取max值的問題1.txt
1.建立測試例子:
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
CREATE TABLE T AS
SELECT ROWNUM id, CASE
WHEN ROWNUM <= 99900
THEN '1'
ELSE '0'
END flag, LPAD ('a', 100, 'a') vc
FROM DUAL
CONNECT BY LEVEL <= 100000;
create index if_t_flag_id on t(nvl(flag,'1'),id);
create index i_t_flag_id on t(flag,id);
exec SYS.DBMS_STATS.GATHER_TABLE_STATS (NULL,'T',Method_Opt=> 'FOR ALL COLUMNS SIZE 1 ',Cascade=> TRUE);
2.執行測試命令:
SQL> select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID a22py0tjbmutp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where
nvl(flag,'1')='1'
Plan hash value: 2113784624
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IF_T_FLAG_ID | 50000 | 341K| 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='1')
SQL> select /*+ gather_plan_statistics */ max(id) from t where flag='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a98amhwysv462, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where flag='1'
Plan hash value: 3307844215
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | FIRST ROW | | 50000 | 341K| 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| I_T_FLAG_ID | 50000 | 341K| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"='1')
20 rows selected.
很奇怪!如果access 沒有使用函式,執行計劃可以走INDEX RANGE SCAN (MIN/MAX),而如果查詢使用nvl(flag,'1')='1',執行計劃是INDEX RANGE SCAN.
3.做10053跟蹤(注意為了要再次硬分析,我修改max=>Max)
SQL> alter session set events '10053 trace name context forever';
Session altered.
SQL> select /*+ gather_plan_statistics */ Max(id) from t where nvl(flag,'1')='1';
MAX(ID)
----------
99900
SQL> alter session set events '10053 trace name context off';
結果如下:
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
****************
QUERY BLOCK TEXT
****************
select /*+ gather_plan_statistics */ Max(id) from t where nvl(flag,'1')='1'
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 bjn=180883 hint_alias="T"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 1581 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T Alias: T
#Rows: 100000 #Blks: 1597 AvgRowLen: 109.00
Index Stats::
Index: IF_T_FLAG_ID Col#: 4 1
LVLS: 1 #LB: 250 #DK: 100000 LB/K: 1.00 DB/K: 1.00 CLUF: 1564.00
Index: I_T_FLAG_ID Col#: 2 1
LVLS: 1 #LB: 250 #DK: 100000 LB/K: 1.00 DB/K: 1.00 CLUF: 1564.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#4): SYS_NC00004$(VARCHAR2)
AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 0.5
Column (#2): FLAG(CHARACTER)
AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 0.5
Table: T Alias: T
Card: Original: 100000 Rounded: 50000 Computed: 50000.00 Non Adjusted: 50000.00
Access Path: TableScan
Cost: 352.86 Resp: 352.86 Degree: 0
Cost_io: 351.00 Cost_cpu: 35372940
Resp_io: 351.00 Resp_cpu: 35372940
Access Path: index (index (FFS))
Index: IF_T_FLAG_ID
resc_io: 56.00 resc_cpu: 20780360
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 57.10 Resp: 57.10 Degree: 1
Cost_io: 56.00 Cost_cpu: 20780360
Resp_io: 56.00 Resp_cpu: 20780360
Access Path: index (Min/Max)
Index: IF_T_FLAG_ID
resc_io: 2.00 resc_cpu: 14443
ix_sel: 2.0000e-05 ix_sel_with_filters: 2.0000e-05
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange Index: IF_T_FLAG_ID
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 50000.00 Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]: T[T]#0
***********************
Best so far: Table#: 0 cost: 2.0008 card: 50000.0000 bytes: 350000
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan: Best join order: 1
Cost: 2.0008 Degree: 1 Card: 50000.0000 Bytes: 350000
Resc: 2.0008 Resc_io: 2.0000 Resc_cpu: 14443
Resp: 2.0008 Resp_io: 2.0000 Resc_cpu: 14443
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT MAX("T"."ID") "MAX(ID)" FROM "SCOTT"."T" "T" WHERE NVL("T"."FLAG",'1')='1'
kkoqbc-end
: call(in-use=28984, alloc=32712), compile(in-use=42728, alloc=46360)
apadrv-end: call(in-use=28984, alloc=32712), compile(in-use=43512, alloc=46360)
sql_id=dh6rg0tp6jsvf.
Current SQL statement for this session:
select /*+ gather_plan_statistics */ Max(id) from t where nvl(flag,'1')='1'
============
Plan Table
============
-----------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 2 | |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | INDEX RANGE SCAN | IF_T_FLAG_ID| 49K | 342K | 2 | 00:00:01 |
-----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("T"."SYS_NC00004$"='1')
Content of other_xml column
===========================
db_version : 10.2.0.3
parse_schema : SCOTT
plan_hash : 2113784624
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T"@"SEL$1" "IF_T_FLAG_ID")
END_OUTLINE_DATA
*/
--感覺10g下這個版本有bug,選擇的如下,cost=2
Access Path: index (Min/Max)
Index: IF_T_FLAG_ID
resc_io: 2.00 resc_cpu: 14443
ix_sel: 2.0000e-05 ix_sel_with_filters: 2.0000e-05
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange Index: IF_T_FLAG_ID
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 50000.00 Bytes: 0
但是執行計劃卻是INDEX RANGE SCAN,而不是INDEX RANGE SCAN (MIN/MAX).但是cost=2也說明問題.
如果查詢使用count(id),也能說明問題,在這樣的情況下,同樣走INDEX RANGE SCAN,cost=129.
SQL> select /*+ gather_plan_statistics index(t,if_t_flag_id) */ count(id) from t where nvl(flag,'1')='1';
COUNT(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bqqa1y80prhwn, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t,if_t_flag_id) */ count(id) from
t where nvl(flag,'1')='1'
Plan hash value: 2113784624
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 129 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IF_T_FLAG_ID | 50492 | 345K| 129 (1)| 00:00:02 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='1')
20 rows selected.
4.在10g下如何最佳化上述sql語句呢?可以把索引反過來建立.
create index if_t_id_flag on t(id,nvl(flag,'1'));
exec SYS.DBMS_STATS.GATHER_TABLE_STATS (NULL,'T',Method_Opt=> 'FOR ALL COLUMNS SIZE 1 ',Cascade=> TRUE);
SQL> select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a22py0tjbmutp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where
nvl(flag,'1')='1'
Plan hash value: 2113784624
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IF_T_FLAG_ID | 50440 | 344K| 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='1')
20 rows selected.
SQL> drop index if_t_flag_id;
Index dropped.
SQL> select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a22py0tjbmutp, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1'
Plan hash value: 4032029066
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 57 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX FAST FULL SCAN| IF_T_ID_FLAG | 50000 | 341K| 57 (2)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."SYS_NC00004$"='1')
19 rows selected.
--可以發現雖然使用索引IF_T_ID_FLAG,但是走的是INDEX FAST FULL SCAN.
--看來這個版本有問題.
1.建立測試例子:
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
CREATE TABLE T AS
SELECT ROWNUM id, CASE
WHEN ROWNUM <= 99900
THEN '1'
ELSE '0'
END flag, LPAD ('a', 100, 'a') vc
FROM DUAL
CONNECT BY LEVEL <= 100000;
create index if_t_flag_id on t(nvl(flag,'1'),id);
create index i_t_flag_id on t(flag,id);
exec SYS.DBMS_STATS.GATHER_TABLE_STATS (NULL,'T',Method_Opt=> 'FOR ALL COLUMNS SIZE 1 ',Cascade=> TRUE);
2.執行測試命令:
SQL> select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID a22py0tjbmutp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where
nvl(flag,'1')='1'
Plan hash value: 2113784624
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IF_T_FLAG_ID | 50000 | 341K| 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='1')
SQL> select /*+ gather_plan_statistics */ max(id) from t where flag='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a98amhwysv462, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where flag='1'
Plan hash value: 3307844215
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | FIRST ROW | | 50000 | 341K| 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| I_T_FLAG_ID | 50000 | 341K| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"='1')
20 rows selected.
很奇怪!如果access 沒有使用函式,執行計劃可以走INDEX RANGE SCAN (MIN/MAX),而如果查詢使用nvl(flag,'1')='1',執行計劃是INDEX RANGE SCAN.
3.做10053跟蹤(注意為了要再次硬分析,我修改max=>Max)
SQL> alter session set events '10053 trace name context forever';
Session altered.
SQL> select /*+ gather_plan_statistics */ Max(id) from t where nvl(flag,'1')='1';
MAX(ID)
----------
99900
SQL> alter session set events '10053 trace name context off';
結果如下:
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
****************
QUERY BLOCK TEXT
****************
select /*+ gather_plan_statistics */ Max(id) from t where nvl(flag,'1')='1'
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 bjn=180883 hint_alias="T"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 1581 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T Alias: T
#Rows: 100000 #Blks: 1597 AvgRowLen: 109.00
Index Stats::
Index: IF_T_FLAG_ID Col#: 4 1
LVLS: 1 #LB: 250 #DK: 100000 LB/K: 1.00 DB/K: 1.00 CLUF: 1564.00
Index: I_T_FLAG_ID Col#: 2 1
LVLS: 1 #LB: 250 #DK: 100000 LB/K: 1.00 DB/K: 1.00 CLUF: 1564.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#4): SYS_NC00004$(VARCHAR2)
AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 0.5
Column (#2): FLAG(CHARACTER)
AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 0.5
Table: T Alias: T
Card: Original: 100000 Rounded: 50000 Computed: 50000.00 Non Adjusted: 50000.00
Access Path: TableScan
Cost: 352.86 Resp: 352.86 Degree: 0
Cost_io: 351.00 Cost_cpu: 35372940
Resp_io: 351.00 Resp_cpu: 35372940
Access Path: index (index (FFS))
Index: IF_T_FLAG_ID
resc_io: 56.00 resc_cpu: 20780360
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 57.10 Resp: 57.10 Degree: 1
Cost_io: 56.00 Cost_cpu: 20780360
Resp_io: 56.00 Resp_cpu: 20780360
Access Path: index (Min/Max)
Index: IF_T_FLAG_ID
resc_io: 2.00 resc_cpu: 14443
ix_sel: 2.0000e-05 ix_sel_with_filters: 2.0000e-05
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange Index: IF_T_FLAG_ID
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 50000.00 Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]: T[T]#0
***********************
Best so far: Table#: 0 cost: 2.0008 card: 50000.0000 bytes: 350000
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan: Best join order: 1
Cost: 2.0008 Degree: 1 Card: 50000.0000 Bytes: 350000
Resc: 2.0008 Resc_io: 2.0000 Resc_cpu: 14443
Resp: 2.0008 Resp_io: 2.0000 Resc_cpu: 14443
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT MAX("T"."ID") "MAX(ID)" FROM "SCOTT"."T" "T" WHERE NVL("T"."FLAG",'1')='1'
kkoqbc-end
: call(in-use=28984, alloc=32712), compile(in-use=42728, alloc=46360)
apadrv-end: call(in-use=28984, alloc=32712), compile(in-use=43512, alloc=46360)
sql_id=dh6rg0tp6jsvf.
Current SQL statement for this session:
select /*+ gather_plan_statistics */ Max(id) from t where nvl(flag,'1')='1'
============
Plan Table
============
-----------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 2 | |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | INDEX RANGE SCAN | IF_T_FLAG_ID| 49K | 342K | 2 | 00:00:01 |
-----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("T"."SYS_NC00004$"='1')
Content of other_xml column
===========================
db_version : 10.2.0.3
parse_schema : SCOTT
plan_hash : 2113784624
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T"@"SEL$1" "IF_T_FLAG_ID")
END_OUTLINE_DATA
*/
--感覺10g下這個版本有bug,選擇的如下,cost=2
Access Path: index (Min/Max)
Index: IF_T_FLAG_ID
resc_io: 2.00 resc_cpu: 14443
ix_sel: 2.0000e-05 ix_sel_with_filters: 2.0000e-05
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange Index: IF_T_FLAG_ID
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 50000.00 Bytes: 0
但是執行計劃卻是INDEX RANGE SCAN,而不是INDEX RANGE SCAN (MIN/MAX).但是cost=2也說明問題.
如果查詢使用count(id),也能說明問題,在這樣的情況下,同樣走INDEX RANGE SCAN,cost=129.
SQL> select /*+ gather_plan_statistics index(t,if_t_flag_id) */ count(id) from t where nvl(flag,'1')='1';
COUNT(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bqqa1y80prhwn, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t,if_t_flag_id) */ count(id) from
t where nvl(flag,'1')='1'
Plan hash value: 2113784624
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 129 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IF_T_FLAG_ID | 50492 | 345K| 129 (1)| 00:00:02 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='1')
20 rows selected.
4.在10g下如何最佳化上述sql語句呢?可以把索引反過來建立.
create index if_t_id_flag on t(id,nvl(flag,'1'));
exec SYS.DBMS_STATS.GATHER_TABLE_STATS (NULL,'T',Method_Opt=> 'FOR ALL COLUMNS SIZE 1 ',Cascade=> TRUE);
SQL> select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a22py0tjbmutp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where
nvl(flag,'1')='1'
Plan hash value: 2113784624
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IF_T_FLAG_ID | 50440 | 344K| 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='1')
20 rows selected.
SQL> drop index if_t_flag_id;
Index dropped.
SQL> select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a22py0tjbmutp, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1'
Plan hash value: 4032029066
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 57 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX FAST FULL SCAN| IF_T_ID_FLAG | 50000 | 341K| 57 (2)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."SYS_NC00004$"='1')
19 rows selected.
--可以發現雖然使用索引IF_T_ID_FLAG,但是走的是INDEX FAST FULL SCAN.
--看來這個版本有問題.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-715313/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20120201][補充]函式索引與取max值的問題1.txt函式索引
- [20120131]函式索引與取max值的問題2.txt函式索引
- [20120131]函式索引與取max值的問題3.txt函式索引
- 函式索引的問題函式索引
- 二元函式的極值與最值問題函式
- 關於函式索引的問題?函式索引
- Mysql的max()函式9大於10問題MySql函式
- 0427建立Extended Statistics函式索引問題函式索引
- 0429建立Extended Statistics函式索引問題函式索引
- 利用函式索引解決複雜的約束問題函式索引
- 函式呼叫引數變數傳值的問題函式變數
- 取石子的幾個找sg函式的問題函式
- 練習 讀取函式裡的值函式
- [20180509]函式索引問題.txt函式索引
- 一類初等函式下取點問題函式
- dev_queue_xmit()函式返回值問題devMIT函式
- jquery的get()函式快取問題解決方案jQuery函式快取
- js中 函式引數的 傳值/傳引用 問題JS函式
- javascript建構函式的返回值問題介紹JavaScript函式
- jQuery獲取CSS樣式中的顏色值的問題jQueryCSS
- 【Analytic】分析函式之MAX函式函式
- [20180212]函式索引問題.txt函式索引
- 建立聯合函式索引解決top sql效能問題函式索引SQL
- （求教）關於js函式按值、引用傳遞的問題JS函式
- 函式索引使用細節——自定義函式的索引化函式索引
- ORACLE單行函式與多行函式之三：數值函式Oracle函式
- [20150803]使用函式索引注意的問題.txt函式索引
- Oracle-空值null和數字相加的問題-nvl函式OracleNull函式
- 函式呼叫棧的問題函式
- itoa函式的奇怪問題函式
- 複合索引與函式索引優化一例索引函式優化
- 關於Hash 函式 雜湊索引表 解決位置衝突的問題函式索引
- [20190918]關於函式索引問題.txt函式索引
- 基於函式的索引函式索引
- 函式索引的儲存函式索引
- shell指令碼獲取函式返回值指令碼函式
- NULL 值與索引Null索引
- 函式索引陷阱函式索引