In Oracle , there are few ways to estimate the size of the index , that you are planning to create.
Let us see an example .
Method 1:
SQL> drop table t purge ;
Table dropped.
SQL> create table t as select object_id , object_name , object_type from all_objects ;
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
SQL> explain plan for create index idx1_t on t(object_id);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
------------------------------
Plan hash value: 2847741357
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 77233 | 377K| 170 (1)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IDX1_T | | | | |
| 2 | SORT CREATE INDEX | | 77233 | 377K| | |
| 3 | TABLE ACCESS FULL | T | 77233 | 377K| 130 (1)| 00:00:01 |
---------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
------------------------------
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
- estimated index size: 2097K bytes
15 rows selected.
Method 2:
SQL> variable u_byte number;
SQL> variable a_byte number;
SQL> exec dbms_space.create_index_cost( 'create index idx1_t on t(object_id)', :u_byte, :a_byte );
PL/SQL procedure successfully completed.
SQL> print :u_byte;
U_BYTE
----------
386165
SQL> print :a_byte;
A_BYTE
----------
2097152
As you can see , with the second method , the estimated size are not rounded . Being this is an estimate , this shouldn't matter.
Let us see an example .
Method 1:
SQL> drop table t purge ;
Table dropped.
SQL> create table t as select object_id , object_name , object_type from all_objects ;
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
SQL> explain plan for create index idx1_t on t(object_id);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
------------------------------
Plan hash value: 2847741357
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 77233 | 377K| 170 (1)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IDX1_T | | | | |
| 2 | SORT CREATE INDEX | | 77233 | 377K| | |
| 3 | TABLE ACCESS FULL | T | 77233 | 377K| 130 (1)| 00:00:01 |
---------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
------------------------------
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
- estimated index size: 2097K bytes
15 rows selected.
Method 2:
SQL> variable u_byte number;
SQL> variable a_byte number;
SQL> exec dbms_space.create_index_cost( 'create index idx1_t on t(object_id)', :u_byte, :a_byte );
PL/SQL procedure successfully completed.
SQL> print :u_byte;
U_BYTE
----------
386165
SQL> print :a_byte;
A_BYTE
----------
2097152
As you can see , with the second method , the estimated size are not rounded . Being this is an estimate , this shouldn't matter.
No comments:
Post a Comment