global temporary table index usage

最新推荐文章于 2024-10-16 17:36:30 发布

aicon

最新推荐文章于 2024-10-16 17:36:30 发布

阅读量519

点赞数

分类专栏： Oracle Oracle EBS 文章标签： table sql access 2010 insert null

本文链接：https://blog.csdn.net/aicon/article/details/5512533

版权

Oracle 同时被 2 个专栏收录

523 篇文章 0 订阅

订阅专栏

Oracle EBS

416 篇文章 0 订阅

订阅专栏

SQL> select index_name,index_type,table_name,table_type,temporary,global_stats,dropped from user_index
es;

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
TABLE_NAME                     TABLE_TYPE T GLO DRO
------------------------------ ----------- - --- ---
TMP_IND_D1                     NORMAL
T_FORM4_POL_CHG_TMP            TABLE       Y NO NO

PK_EMP NORMAL
EMP TABLE N YES NO

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
TABLE_NAME                     TABLE_TYPE T GLO DRO
------------------------------ ----------- - --- ---
PK_DEPT                        NORMAL
DEPT                           TABLE       N YES NO

SYS_IL0000052546C00036$$ LOB
PLAN_TABLE TABLE N NO NO

SQL>

SQL> select count(*) from t_form4_pol_chg_tmp;

COUNT(*)
----------
0

SQL> begin
2 for i in 1..5000000 loop
3 insert into t_form4_pol_chg_tmp values(i,i,i,null,i,null,'TW');
4 end loop;
5 end;
6 /

PL/SQL 过程已成功完成。

SQL> select count(*) from t_form4_pol_chg_tmp;

COUNT(*)
----------
5000000

SQL> explain plan for select count(*) from t_form4_pol_chg_tmp where policy_id between 1000 and 2010;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3038987979

--------------------------------------------------------------------------------
| Id | Operation         | Name       | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |     1 |    13 |     0   (0)| 00:00:01 |
|   1 | SORT AGGREGATE   |            |     1 |    13 |            |          |
|* 2 |   INDEX RANGE SCAN| TMP_IND_D1 | 1011 | 13143 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

2 - access("POLICY_ID">=1000 AND "POLICY_ID"<=2010)

Note
-----
- dynamic sampling used for this statement

已选择18行。

SQL>

SQL> explain plan for select /*+ full(t_form4_pol_chg_tmp) */count(*) from t_form4_pol_chg_tmp where p
olicy_id between 1000 and 2010;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3355564457

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 5209 (5)|
00:01:03 |

| 1 | SORT AGGREGATE | | 1 | 13 | |
|

|* 2 | TABLE ACCESS FULL| T_FORM4_POL_CHG_TMP | 1011 | 13143 | 5209 (5)|
00:01:03 |

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("POLICY_ID">=1000 AND "POLICY_ID"<=2010)

Note
-----
- dynamic sampling used for this statement

已选择18行。

SQL> select count(*) from t_form4_pol_chg_tmp;

COUNT(*)
----------
5000000

SQL>