oracle kkoqbc-subheap,两列复合索引中(交换索引中列的顺序)，选索引的原则是 因为索引名字ascii码小？...

于 2021-04-15 11:27:46 发布
阅读量145 收藏
点赞数
文章标签： oracle kkoqbc-subheap

对于一个两列的复合索引，where条件中都是用 “=” 号 和 and,

选取索引时，为什么选择 索引名 ascii码小的？？

在天师 老托拉呱 的指点下，  不用动态采样+cbo, 还是一样的结果 。

用rbo的话，会一直有同一个。但是 不晓得 cbo的原因 。

​最终在 天师的指示下看到Handling

of equally ranked (RBO) or costed (CBO) indexes (Doc ID

73167.1)

create table t11 as select * from dba_objects;

create index idx_1 on t11(object_id,object_name);

create index idx_2 on t11(object_name,object_id);

Alter session set events '10053 trace name context forever,

level 1';

select * from t11 where object_name='ICOL$' and

object_id=20;

Alter session set events '10053 trace name context off';

Access Path: TableScan

Cost:

139.65  Resp: 139.65

Degree: 0

Cost_io: 139.00  Cost_cpu: 20044626

Resp_io: 139.00  Resp_cpu: 20044626

Access Path: index (AllEqRange)

Index: IDX_1

resc_io: 2.00

resc_cpu: 15683

ix_sel: 1.6000e-05

ix_sel_with_filters: 1.6000e-05

Cost: 2.00

Resp: 2.00  Degree: 1

Access Path: index (AllEqRange)

Index: IDX_2

resc_io: 2.00

resc_cpu: 15683

ix_sel: 1.6000e-05

ix_sel_with_filters: 1.6000e-05

Cost: 2.00

Resp: 2.00  Degree: 1

****** trying bitmap/domain indexes

******

****** finished trying bitmap/domain

indexes ******

Best:: AccessPath: IndexRange

Index: IDX_1

Cost: 2.00

Degree: 1  Resp: 2.00

Card: 5.75

Bytes:

***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS

***************************************

GENERAL PLANS

***************************************

Considering cardinality-based initial join order.

Permutations for Starting Table :0

***********************

Join order[1]:  T11[T11]#0

***********************

Best so far: Table#: 0  cost: 2.0005

card: 5.7533  bytes: 1062

(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.0005  Degree: 1

Card: 6.0000  Bytes: 1062

Resc: 2.0005  Resc_io:

2.0000  Resc_cpu: 15683

Resp: 2.0005  Resp_io:

2.0000  Resc_cpu: 15683

kkoipt: Query block SEL$1 (#0)

******* UNPARSED QUERY IS *******

SELECT "T11"."OWNER" "OWNER","T11"."OBJECT_NAME"

"OBJECT_NAME","T11"."SUBOBJECT_NAME"

"SUBOBJECT_NAME","T11"."OBJECT_ID"

"OBJECT_ID","T11"."DATA_OBJECT_ID"

"DATA_OBJECT_ID","T11"."OBJECT_TYPE" "OBJECT_TYPE","T11"."CREATED"

"CREATED","T11"."LAST_DDL_TIME" "LAST_DDL_TIME","T11"."TIMESTAMP"

"TIMESTAMP","T11"."STATUS" "STATUS","T11"."TEMPORARY"

"TEMPORARY","T11"."GENERATED" "GENERATED","T11"."SECONDARY"

"SECONDARY" FROM "SONG"."T11" "T11" WHERE

"T11"."OBJECT_NAME"='ICOL$' AND "T11"."OBJECT_ID"=20

kkoqbc-subheap (delete addr=0x2b271cfabe38, in-use=17008,

alloc=18400)

kkoqbc-end

: call(in-use=37416,

alloc=65448), compile(in-use=48936, alloc=50504)

apadrv-end: call(in-use=37416, alloc=65448),

compile(in-use=49808, alloc=50504)

sql_id=7a6qgwc26kkq4.

Current SQL statement for this session:

select * from t11 where object_name='ICOL$' and

object_id=20

============

Plan Table

============

-----------------------------------------------+-----------------------------------+

| Id  | Operation  | Name

| Rows  |

Bytes | Cost  | Time  |

-----------------------------------------------+-----------------------------------+

| 0  | SELECT STATEMENT  |  |

|

|

2 |  |

| 1  |  TABLE ACCESS BY

INDEX ROWID | T11  |

6 |  1062 |

2 |

00:00:01 |

| 2  |  INDEX RANGE SCAN

| IDX_1  |

1 |  |  1 |  00:00:01 |

-----------------------------------------------+-----------------------------------+

Predicate Information:

----------------------

2 - access("OBJECT_ID"=20 AND "OBJECT_NAME"='ICOL$')

Content of other_xml column

===========================

db_version  : 10.2.0.4

parse_schema  :

SONG

dynamic_sampling: yes

plan_hash  : 1193947757

Outline Data:

select * from dba_objects;

alter system flush buffer_cache;

alter system flush shared_pool;

drop index idx_1;

create index idx_3 on t11(object_id,object_name);

Alter session set events '10053 trace name context forever,

level 1';

select * from t11 where object_name='ICOL$' and

object_id=20;

Alter session set events '10053 trace name context off';

Access Path: TableScan

Cost:

139.65  Resp: 139.65

Degree: 0

Cost_io: 139.00  Cost_cpu: 20044626

Resp_io: 139.00  Resp_cpu: 20044626

Access Path: index (AllEqRange)

Index: IDX_2

resc_io: 2.00

resc_cpu: 15683

ix_sel: 1.6000e-05

ix_sel_with_filters: 1.6000e-05

Cost: 2.00

Resp: 2.00  Degree: 1

Access Path: index (AllEqRange)

Index: IDX_3

resc_io: 2.00

resc_cpu: 15683

ix_sel: 1.6000e-05

ix_sel_with_filters: 1.6000e-05

Cost: 2.00

Resp: 2.00  Degree: 1

****** trying bitmap/domain indexes

******

****** finished trying bitmap/domain

indexes ******

Best:: AccessPath: IndexRange

Index: IDX_2

Cost: 2.00

Degree: 1  Resp: 2.00

Card: 5.75  Bytes: 0

***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS

***************************************

GENERAL PLANS

***************************************

Considering cardinality-based initial join order.

Permutations for Starting Table :0

***********************

Join order[1]:  T11[T11]#0

***********************

Best so far: Table#: 0  cost: 2.0005

card: 5.7533  bytes: 1062

(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.0005  Degree: 1

Card: 6.0000  Bytes: 1062

Resc: 2.0005  Resc_io:

2.0000  Resc_cpu: 15683

Resp: 2.0005  Resp_io:

2.0000  Resc_cpu: 15683

kkoipt: Query block SEL$1 (#0)

******* UNPARSED QUERY IS *******

SELECT "T11"."OWNER" "OWNER","T11"."OBJECT_NAME"

"OBJECT_NAME","T11"."SUBOBJECT_NAME"

"SUBOBJECT_NAME","T11"."OBJECT_ID"

"OBJECT_ID","T11"."DATA_OBJECT_ID"

"DATA_OBJECT_ID","T11"."OBJECT_TYPE" "OBJECT_TYPE","T11"."CREATED"

"CREATED","T11"."LAST_DDL_TIME" "LAST_DDL_TIME","T11"."TIMESTAMP"

"TIMESTAMP","T11"."STATUS" "STATUS","T11"."TEMPORARY"

"TEMPORARY","T11"."GENERATED" "GENERATED","T11"."SECONDARY"

"SECONDARY" FROM "SONG"."T11" "T11" WHERE "T11"."OBJECT_ID"=20 AND

"T11"."OBJECT_NAME"='ICOL$'

kkoqbc-subheap (delete addr=0x2aca370cbe38, in-use=17008,

alloc=18400)

kkoqbc-end

: call(in-use=37416,

alloc=65448), compile(in-use=48936, alloc=50504)

apadrv-end: call(in-use=37416, alloc=65448),

compile(in-use=49808, alloc=50504)

sql_id=7a6qgwc26kkq4.

Current SQL statement for this session:

select * from t11 where object_name='ICOL$' and

object_id=20

============

Plan Table

============

-----------------------------------------------+-----------------------------------+

| Id  | Operation  | Name

| Rows  |

Bytes | Cost  | Time  |

-----------------------------------------------+-----------------------------------+

| 0  | SELECT STATEMENT  |  |

|

|

2 |  |

| 1  |  TABLE ACCESS BY

INDEX ROWID | T11  |

6 |  1062 |

2 |

00:00:01 |

| 2  |  INDEX RANGE SCAN

| IDX_2  |

1 |  |  1 |  00:00:01 |

-----------------------------------------------+-----------------------------------+

Predicate Information:

----------------------

2 - access("OBJECT_NAME"='ICOL$' AND "OBJECT_ID"=20)

Content of other_xml column

===========================

db_version  : 10.2.0.4

parse_schema  :

SONG

dynamic_sampling: yes

plan_hash  : 4084186866

Outline Data:

alter system flush buffer_cache;

alter system flush shared_pool;

drop index idx_3;

create index idx on t11(object_id,object_name);

Alter session set events '10053 trace name context forever,

level 1';

select * from t11 where object_name='ICOL$' and

object_id=20;

Alter session set events '10053 trace name context off';

Access Path: TableScan

Cost:

139.65  Resp: 139.65

Degree: 0

Cost_io: 139.00  Cost_cpu: 20044626

Resp_io: 139.00  Resp_cpu: 20044626

Access Path: index (AllEqRange)

Index: IDX

resc_io: 2.00

resc_cpu: 15683

ix_sel: 1.6000e-05

ix_sel_with_filters: 1.6000e-05

Cost: 2.00

Resp: 2.00  Degree: 1

Access Path: index (AllEqRange)

Index: IDX_2

resc_io: 2.00

resc_cpu: 15683

ix_sel: 1.6000e-05

ix_sel_with_filters: 1.6000e-05

Cost: 2.00

Resp: 2.00  Degree: 1

****** trying bitmap/domain indexes

******

****** finished trying bitmap/domain

indexes ******

Best:: AccessPath: IndexRange

Index: IDX

Cost: 2.00

Degree: 1  Resp: 2.00

Card: 5.75  Bytes: 0

***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS

***************************************

GENERAL PLANS

***************************************

Considering cardinality-based initial join order.

Permutations for Starting Table :0

***********************

Join order[1]:  T11[T11]#0

***********************

Best so far: Table#: 0  cost: 2.0005

card: 5.7533  bytes: 1062

(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.0005  Degree: 1

Card: 6.0000  Bytes: 1062

Resc: 2.0005  Resc_io:

2.0000  Resc_cpu: 15683

Resp: 2.0005  Resp_io:

2.0000  Resc_cpu: 15683

kkoipt: Query block SEL$1 (#0)

******* UNPARSED QUERY IS *******

SELECT "T11"."OWNER" "OWNER","T11"."OBJECT_NAME"

"OBJECT_NAME","T11"."SUBOBJECT_NAME"

"SUBOBJECT_NAME","T11"."OBJECT_ID"

"OBJECT_ID","T11"."DATA_OBJECT_ID"

"DATA_OBJECT_ID","T11"."OBJECT_TYPE" "OBJECT_TYPE","T11"."CREATED"

"CREATED","T11"."LAST_DDL_TIME" "LAST_DDL_TIME","T11"."TIMESTAMP"

"TIMESTAMP","T11"."STATUS" "STATUS","T11"."TEMPORARY"

"TEMPORARY","T11"."GENERATED" "GENERATED","T11"."SECONDARY"

"SECONDARY" FROM "SONG"."T11" "T11" WHERE

"T11"."OBJECT_NAME"='ICOL$' AND "T11"."OBJECT_ID"=20

kkoqbc-subheap (delete addr=0x2b431ecbbe38, in-use=17008,

alloc=18400)

kkoqbc-end

: call(in-use=37416,

alloc=65448), compile(in-use=48936, alloc=50504)

apadrv-end: call(in-use=37416, alloc=65448),

compile(in-use=49808, alloc=50504)

sql_id=7a6qgwc26kkq4.

Current SQL statement for this session:

select * from t11 where object_name='ICOL$' and

object_id=20

============

Plan Table

============

-----------------------------------------------+-----------------------------------+

| Id  | Operation  | Name

| Rows  |

Bytes | Cost  | Time  |

-----------------------------------------------+-----------------------------------+

| 0  | SELECT STATEMENT  |  |

|

|

2 |  |

| 1  |  TABLE ACCESS BY

INDEX ROWID | T11  |

6 |  1062 |

2 |

00:00:01 |

| 2  |  INDEX RANGE SCAN

| IDX  |  1 |

|

1 |

00:00:01 |

-----------------------------------------------+-----------------------------------+

Predicate Information:

----------------------

2 - access("OBJECT_ID"=20 AND "OBJECT_NAME"='ICOL$')

Content of other_xml column

===========================

db_version  : 10.2.0.4

parse_schema  :

SONG

dynamic_sampling: yes

plan_hash  : 1540313942

Outline Data:

SINGLE TABLE ACCESS PATH

-----------------------------------------

BEGIN Single Table Cardinality

Estimation

-----------------------------------------

*** 2014-05-21 23:57:46.850

** Performing dynamic sampling initial checks. **

** Not using dynamic sampling since sampling disabled or level

equals 0.

Column (#2): OBJECT_NAME(VARCHAR2)

NO STATISTICS (using defaults)

AvgLen: 66.00 NDV: 1812

Nulls: 0 Density: 5.5179e-04

Column (#4): OBJECT_ID(NUMBER)

NO STATISTICS (using defaults)

AvgLen: 13.00 NDV: 1812

Nulls: 0 Density: 5.5179e-04

Table: T11  Alias: T11

Card: Original: 57993

Rounded: 6  Computed: 5.80

Non Adjusted: 5.80

-----------------------------------------

END  Single Table

Cardinality Estimation

-----------------------------------------

Access Path: TableScan

Cost:

139.66  Resp: 139.66

Degree: 0

Cost_io: 139.00  Cost_cpu: 20164479

Resp_io: 139.00  Resp_cpu: 20164479

Access Path: index (AllEqRange)

Index: IDX

resc_io: 2.00

resc_cpu: 15683

ix_sel: 1.9926e-05

ix_sel_with_filters: 1.9926e-05

Cost: 2.00

Resp: 2.00  Degree: 1

Access Path: index (AllEqRange)

Index: IDX_2

resc_io: 2.00

resc_cpu: 15683

ix_sel: 1.9926e-05

ix_sel_with_filters: 1.9926e-05

Cost: 2.00

Resp: 2.00  Degree: 1

****** trying bitmap/domain indexes

******

****** finished trying bitmap/domain

indexes ******

Best:: AccessPath: IndexRange

Index: IDX

Cost: 2.00

Degree: 1  Resp: 2.00

Card: 5.80  Bytes: 0

***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS

***************************************

GENERAL PLANS

***************************************

Considering cardinality-based initial join order.

Permutations for Starting Table :0

***********************

Join order[1]:  T11[T11]#0

***********************

Best so far: Table#: 0  cost: 2.0005

card: 5.7993  bytes: 1062

(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.0005  Degree: 1

Card: 6.0000  Bytes: 1062

Resc: 2.0005  Resc_io:

2.0000  Resc_cpu: 15683

Resp: 2.0005  Resp_io:

2.0000  Resc_cpu: 15683

kkoipt: Query block SEL$1 (#0)

******* UNPARSED QUERY IS *******

SELECT "T11"."OWNER" "OWNER","T11"."OBJECT_NAME"

"OBJECT_NAME","T11"."SUBOBJECT_NAME"

"SUBOBJECT_NAME","T11"."OBJECT_ID"

"OBJECT_ID","T11"."DATA_OBJECT_ID"

"DATA_OBJECT_ID","T11"."OBJECT_TYPE" "OBJECT_TYPE","T11"."CREATED"

"CREATED","T11"."LAST_DDL_TIME" "LAST_DDL_TIME","T11"."TIMESTAMP"

"TIMESTAMP","T11"."STATUS" "STATUS","T11"."TEMPORARY"

"TEMPORARY","T11"."GENERATED" "GENERATED","T11"."SECONDARY"

"SECONDARY" FROM "SONG"."T11" "T11" WHERE

"T11"."OBJECT_NAME"='ICOL$' AND "T11"."OBJECT_ID"=20

kkoqbc-subheap (delete addr=0x2b1185b7be38, in-use=16056,

alloc=18400)

kkoqbc-end

: call(in-use=29840,

alloc=65448), compile(in-use=48624, alloc=50504)

apadrv-end: call(in-use=29840, alloc=65448),

compile(in-use=49408, alloc=50504)

sql_id=22bwf2zav4aha.

Current SQL statement for this session:

select * from t11 where object_name='ICOL$' and

object_id=20

============

Plan Table

============

-----------------------------------------------+-----------------------------------+

| Id  | Operation  | Name

| Rows  |

Bytes | Cost  | Time  |

-----------------------------------------------+-----------------------------------+

| 0  | SELECT STATEMENT  |  |

|

|

2 |  |

| 1  |  TABLE ACCESS BY

INDEX ROWID | T11  |

6 |  1062 |

2 |

00:00:01 |

| 2  |  INDEX RANGE SCAN

| IDX  |  1 |

|

1 |

00:00:01 |

-----------------------------------------------+-----------------------------------+

Predicate Information:

----------------------

2 - access("OBJECT_ID"=20 AND "OBJECT_NAME"='ICOL$')

Content of other_xml column

===========================

db_version  : 10.2.0.4

parse_schema  :

SONG

plan_hash  : 1540313942

Outline Data:

drop index idx;

create index idx_3 on

t11(object_id,object_name);

****************

QUERY BLOCK TEXT

****************

select * from t11 where object_name='ICOL$' and

object_id=20

*********************

QUERY BLOCK SIGNATURE

*********************

qb name was generated

signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0

fro(0): flg=0 objn=57349

hint_alias="T11"@"SEL$1"

*****************************

SYSTEM STATISTICS INFORMATION

*****************************

Using NOWORKLOAD Stats

CPUSPEED: 2563 millions

instruction/sec

IOTFRSPEED: 4096 bytes per millisecond

(default is 4096)

IOSEEKTIM: 10 milliseconds (default is

10)

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

Table: T11  Alias: T11

(NOT ANALYZED)

#Rows: 57993

#Blks:  710

AvgRowLen:  100.00

Index Stats::

Index: IDX_2  Col#: 2

4

LVLS: 1

#LB: 281  #DK: 50186

LB/K: 1.00  DB/K: 1.00

CLUF: 25485.00

Index: IDX_3  Col#: 4

2

LVLS: 1

#LB: 281  #DK: 50186

LB/K: 1.00  DB/K: 1.00

CLUF: 749.00

***************************************

SINGLE TABLE ACCESS PATH

-----------------------------------------

BEGIN Single Table Cardinality

Estimation

-----------------------------------------

*** 2014-05-22 00:00:45.072

** Performing dynamic sampling initial checks. **

** Not using dynamic sampling since sampling disabled or level

equals 0.

Column (#2): OBJECT_NAME(VARCHAR2)

NO STATISTICS (using defaults)

AvgLen: 66.00 NDV: 1812

Nulls: 0 Density: 5.5179e-04

Column (#4): OBJECT_ID(NUMBER)

NO STATISTICS (using defaults)

AvgLen: 13.00 NDV: 1812

Nulls: 0 Density: 5.5179e-04

Table: T11  Alias: T11

Card: Original: 57993

Rounded: 6  Computed: 5.80

Non Adjusted: 5.80

-----------------------------------------

END  Single Table

Cardinality Estimation

-----------------------------------------

Access Path: TableScan

Cost:

139.66  Resp: 139.66

Degree: 0

Cost_io: 139.00  Cost_cpu: 20164479

Resp_io: 139.00  Resp_cpu: 20164479

Access Path: index (AllEqRange)

Index: IDX_2

resc_io: 2.00

resc_cpu: 15683

ix_sel: 1.9926e-05

ix_sel_with_filters: 1.9926e-05

Cost: 2.00

Resp: 2.00  Degree: 1

Access Path: index (AllEqRange)

Index: IDX_3

resc_io: 2.00

resc_cpu: 15683

ix_sel: 1.9926e-05

ix_sel_with_filters: 1.9926e-05

Cost: 2.00

Resp: 2.00  Degree: 1

****** trying bitmap/domain indexes

******

****** finished trying bitmap/domain

indexes ******

Best:: AccessPath: IndexRange

Index: IDX_2

Cost: 2.00

Degree: 1  Resp: 2.00

Card: 5.80  Bytes: 0

***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS

***************************************

GENERAL PLANS

***************************************

Considering cardinality-based initial join order.

Permutations for Starting Table :0

***********************

Join order[1]:  T11[T11]#0

***********************

Best so far: Table#: 0  cost: 2.0005

card: 5.7993  bytes: 1062

(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.0005  Degree: 1

Card: 6.0000  Bytes: 1062

Resc: 2.0005  Resc_io:

2.0000  Resc_cpu: 15683

Resp: 2.0005  Resp_io:

2.0000  Resc_cpu: 15683

kkoipt: Query block SEL$1 (#0)

******* UNPARSED QUERY IS *******

SELECT "T11"."OWNER" "OWNER","T11"."OBJECT_NAME"

"OBJECT_NAME","T11"."SUBOBJECT_NAME"

"SUBOBJECT_NAME","T11"."OBJECT_ID"

"OBJECT_ID","T11"."DATA_OBJECT_ID"

"DATA_OBJECT_ID","T11"."OBJECT_TYPE" "OBJECT_TYPE","T11"."CREATED"

"CREATED","T11"."LAST_DDL_TIME" "LAST_DDL_TIME","T11"."TIMESTAMP"

"TIMESTAMP","T11"."STATUS" "STATUS","T11"."TEMPORARY"

"TEMPORARY","T11"."GENERATED" "GENERATED","T11"."SECONDARY"

"SECONDARY" FROM "SONG"."T11" "T11" WHERE "T11"."OBJECT_ID"=20 AND

"T11"."OBJECT_NAME"='ICOL$'

kkoqbc-subheap (delete addr=0x2b479b4abe38, in-use=16056,

alloc=18400)

kkoqbc-end

: call(in-use=29840,

alloc=65448), compile(in-use=48624, alloc=50504)

apadrv-end: call(in-use=29840, alloc=65448),

compile(in-use=49408, alloc=50504)

sql_id=22bwf2zav4aha.

Current SQL statement for this session:

select * from t11 where object_name='ICOL$' and

object_id=20

============

Plan Table

============

-----------------------------------------------+-----------------------------------+

| Id  | Operation  | Name

| Rows  |

Bytes | Cost  | Time  |

-----------------------------------------------+-----------------------------------+

| 0  | SELECT STATEMENT  |  |

|

|

2 |  |

| 1  |  TABLE ACCESS BY

INDEX ROWID | T11  |

6 |  1062 |

2 |

00:00:01 |

| 2  |  INDEX RANGE SCAN

| IDX_2  |

1 |  |  1 |  00:00:01 |

-----------------------------------------------+-----------------------------------+

Predicate Information:

----------------------

2 - access("OBJECT_NAME"='ICOL$' AND "OBJECT_ID"=20)

Content of other_xml column

===========================

db_version  : 10.2.0.4

parse_schema  :

SONG

plan_hash  : 4084186866

Outline Data:

董晶晖
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle复合索引列顺序,复合索引顺序选择性问题（一）
weixin_29759387的博客
04-04 3596
索引是我们经常选择的数据表检索优化方案之一。其中，复合索引是我们经常选择的策略。那么，构建索引列的顺序上，有何种差异和需要注意的方面呢？下面我们通过实验来进行说明。实验环境说明准备数据表和实验环境。索引列的差异，主要体现在选择性上，我们通过构建不同选择性的列来进行试验。SQL> conn scott/tiger@orcl;Connected to Oracle DatabaseEnterpr...
Github 星标 8K+ 这款国人开源的 Redis 可视化管理工具
m0_49704898的博客
10-23 1万+
做程序员就少不了与一些工具打交道，比如：监控工具、管理工具等，有些工具是命令行界面，有些工具是可视化界面，反正都是可以能够满足日常使用的功能需求。 对于redis管理工具来说，也有不少可能的产品，比如： 1、Redis Desktop Manager 2、RedisStudio 3、phpRedisAdmin 4、Go-Redis 5、RedisClient 有开源、免费的，也有商业收费的版本，基本上功能都还可以，不过开源、免费还是最大的需求点之一哈，老司机都懂得的。 所以，今天，民工哥就给大家安利一款由国
oracle 索引 两列,【TUNE_ORACLE】Oracle索引设计思想（二）索引过滤列概述
weixin_35625397的博客
04-03 168
什么是过滤列过滤列是一种存在于where子句中，同时上面也有索引，能起到过滤数据，但是不能在索引片中生效仅能增加索引片厚度的一种列。目的是减少回表。注：从过滤列的定义可知，并不是所有的列都能够在索引片中发挥作用，仅仅只是占位。如何判断是否是过滤列1.首先我们需要查看目标列是否在索引中，然后再查看索引中列的顺序，并且要从左到右查看，因为列在索引中越靠左对索引效率影响越大，比如索引idxname(a...
Join Elimination
cr13324309138的博客
07-19 236
1.定义 The purpose of join elimination is to remove redundant joins—in other words, to completely avoid execu...
oracle 索引 两列,在Oracle中,如何用varchar2列对索引进行分区?这是个坏主意吗?
weixin_42302384的博客
04-03 257
我正在使用Oracle 10g企业版。我们的Oracle数据库中的一个表存储另一个文本列的SOUNDEX值表示。我们使用的是自定义Soundex实现,其中Soundex值比传统Soundex算法(如Oracle使用的算法)生成的值长。这真是离题了。基本上,我有一个varchar2列,它的值包含一个字符,后跟一个动态数字值(例如“A12345”、“S382771”等)。表是由另一列分区的,但是我想向...
10053 事件浅析
道行尚浅（老道）的博客
12-06 2594
1 session 级别设置event SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1'; Session altered. 2 执行语句 SQL> select /*+10053 test*/*    2  from dao_objects1 t1 ,dao_objects2 t2
oracle复合索引介绍(多字段索引)
ysj5125094的专栏
12-10 2051
  oracle普通索引介绍(单字段索引) : http://ysj5125094.iteye.com/blog/1745354         首先,在大多数情况下,复合索引比单字段索引好.以税务系统的SB_ZSXX(申报类_征收信息表)为例,该表为税务系统最大的交易表.如果分别按纳税人识别号,税务机关代码,月份3个字段查询,每个字段在该表中的可选性或约束性都不强,如一个纳税人识别号有很多...
oracle 10053 not in,读懂Oracle 10053事件
weixin_32751961的博客
04-14 107
我们可以通过10046事件看到一个SQL的执行的统计信息，以及执行计划，但是我们只看到了CBO最终告诉我们的执行结果，却并不知道CBO为什么要这么选择，那么就可以通过10053事件来生成SQL分析的整个过程到trace文件中，通俗点讲10053跟踪选路过程，10046产生结果。关于10046事件参考我的blog：10053包含2个级别：Level 2：2级是1级的一个子集，它包含以下内容：Colu...
《Oracle高性能SQL引擎剖析：SQL优化与调优机制详解》一3.1　启发式查询转换
weixin_34061042的博客
08-01 234
3.1　启发式查询转换 所有的启发式查询转换都是基于一套优化器内建的规则。在查询转换阶段，转换器会逐个针对这些规则对查询进行检查，确定其是否满足转换规则，一旦满足，转换器就对其进行转换。 3.1.1　简单视图合并 我们知道，视图（View）的实质就是一条查询语句。在解析阶段，语句中的每个视图都会被展开至一个查询块中。如果未做视图合并，优化器则会单独分析每...
ORA-07445 [kkoipt()+3881] [SIGSEGV] [Address not mapped to object] 问题
David Dai -- Focus on Oracle
02-21 8471
<br /> <br />在DB的alert log里面发现一条错误日志：<br />       ORA-07445: [kkoipt()+3881] [SIGSEGV] [Address not mapped to object]<br /> <br />       下面是trace 里面提示的SQL，既导致这个错误的SQL语句，红色部分已经标出：<br />O/S info: user: root, term: unknown, ospid: 1234, machine: qs-xezf-web<b
ORACLE ORA-00600之[kkoipt:incorrect pwj], [0], [0], [0], [2], [1], [1]
clm20482的博客
12-19 117
近期的数据库巡检中，发现一oracle rac数据库的一个节点发生ORA-00600 [kkoipt:incorrect pwj], [0], [0], [0], [2], [1], [1]告警，从trc文件中定位...
11.2.0.4 Patch Set - List of Bug Fixes by Problem Type (Doc ID 1562142.1)
热门推荐
Zhong Weicheng Study Notes
09-20 6万+
Bugs fixed in the 11.2.0.4 Patch Set See Note:880782.1 for Support Status and Alerts affecting 11.2.0 releases. This note lists customer bugs fixed in the 11.2.0.4 Patch Set which are not alread
【Oracle】Oracle 11gR2发布11.2.0.3 Patchset补丁集
weixin_33694172的博客
04-13 267
11.2.0.3 Patch Set - Availability and Known Issues [ID 1348336.1] 修改时间07-MAR-2012类型README状态PUBLISHED Known Issues specific to the 11.2.0.3 Patch Set P...
10053诊断事件——深入分析CBO内容！
事后诸葛亮的程序人生（微信：zq9017197）
12-17 5771
我们在查看一条sql语句的执行计划时，只看到了CBO最终告诉我们的执行计划结果，但是却不知道CBO为什么要这么做。特别是当执行计划明显失真时，我们一定非常想搞清楚到底是什么地方导致CBO作出了这样一个错误的执行计划。10053事件给我们提供了一种这样的方式，它允许我们深入到CBO的内部，去看看CBO是如何工作的，它究竟是按照什么样的依据得出最终的执行计划的。 SQL> conn u1/u1 已
【DB笔试面试677】在Oracle中，对于一个NUMBER(1)的列，若WHERE条件是大于3和大于等于4，这二者是否等价？...
小麦苗DBA宝典
11-04 553
♣题目部分在Oracle中，对于一个NUMBER(1)的列，如果查询中的WHERE条件分别是大于3和大于等于4，那么这二者是否等价？ ♣答案部分首先对于查询结果而...
计算机系统结构(02325)自考真题(201704)
最新发布
07-28
计算机系统结构(02325)自考真题(201704) 计算机系统结构（Computer Architecture）也称为计算机体系结构，它是由计算机结构外特性，内特性，微外特性组成的。经典的计算机系统结构的定义是指计算机系统多级层次结构中机器语言机器级的结构，它是软件和硬件/固件的主要交界面，是由机器语言程序、汇编语言源程序和高级语言源程序翻译生成的机器语言目标程序能在机器上正确运行所应具有的界面结构和功能。 计算机系统结构指的是什么? 是一台计算机的外表? 还是是指一台计算机内部的一块块板卡安放结构? 都不是，那么它是什么? 计算机系统结构就是计算机的机器语言程序员或编译程序编写者所看到的外特性。所谓外特性，就是计算机的概念性结构和功能特性。用一个不恰当的比喻一，比如动物吧，它的"系统结构"是指什么呢? 它的概念性结构和功能特性，就相当于动物的器官组成及其功能特性，如鸡有胃，胃可以消化食物。至于鸡的胃是什么形状的、鸡的胃部由什么组成就不是"系统结构"研究的问题了。
国家电零售业中上市公司融资问题研究—以国美电器为例.docx
07-28
国家电零售业中上市公司融资问题研究—以国美电器为例.docx
Oracle数据库中110个常用函数详解：从ASCII到UPPER
如 `CHR(65)` 返回 'A'，因为65对应ASCII码中的'A'。 3. CONCAT 函数: 用于连接两个或多个字符串。在示例中，`CONCAT('010-', '88888888')` 结果是 '010-88888888'，可以用于构建复合字符串。 4. INITCAP 函数: 将...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值