Oracle used rowid to delete data and used indexes.

In the afternoon, a colleague executed a delete statement and executed 0.11 million records in an hour.

This statement is delete from table_name where rowid = 'xxxxaaa'

The execution plan is indeed indexed by a create_time-based index.

Later, many colleagues thought they had written rowid, so they should directly Delete the data in the table. Why should they access the index? Our DBA explained that this table has no analysis index or analysis.

 

But I went home and tried it.

SQL> Create Table t_test (ID number, name varchar2 (35), create_time date );

Table created

SQL> Create index idx_test_createtime on t_test (create_time );

Index created

SQL> insert into t_test select object_id, object_name, sysdate from dba_objects;

50333 rows inserted

SQL> commit;

Commit complete

No analysis:

Some default parameters

SQL> show parameter optimizer_index_cost_adj

Optimizer_index_cost_adj integer 100

Optimizer_index_caching integer 0

Db_block_buffers integer 0

Db_block_checking string false

Db_block_checksum string true

Db_block_size integer 8192

Db_file_multiblock_read_count integer 32

 

Execute the delete statement to view the execution plan

DeleteT_testWhere id> 500

Delete t_test where id =: 1

Delete t_test where rowed = 'aaam3oaaeaaaaoaaa'

The result execution plan is not indexed.

Delete statement, GOAL = ALL_ROWS Depth = 0 Operation = delete statement delete Depth = 1 Operation = DELETE Object name = T_TEST table access by user rowid Depth = 2 Operation = TABLE ACCESS

Under Analysis

SQL> analyze table t_test compute statistics;

Table analyzed

No index is found

 

Try partitioned table

Create TableT_test_2As select*FromT_test

SQL> update t_test_2 set create_time = sysdate-1 where id <= 10000;

9581 rows updated

SQL> update t_test_2 set create_time = sysdate-2 where id <= 20000 and id> 10000;

9974 rows updated

SQL> Update t_test_2 set create_time = sysdate-3 where ID <= 30000 and ID> 20000;

10000 rows updated

SQL> Update t_test_2 set create_time = sysdate-4 where ID <= 40000 and ID> 30000;

9997 rows updated

SQL> Update t_test_2 set create_time = sysdate-5 where ID <= 50000 and ID> 40000;

9089 rows updated

SQL> commit;

Commit complete

SQL> select to_char (create_time, 'yyyymmdd') as statedate, count (*) T from t_test_2 group by to_char (create_time, 'yyyymmdd ');

Statedate t

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

20120528 9974

20120527 10000

20120530 1692

20120529 9581

20120526 9997

20120525 9089

6 rows selected

 

Create time range partition table:

-- Create Table

Create Table t_test_3

(ID number,

Name varchar2 (35 ),

Create_time date

) Partition by range (create_time)

(Partition test_20120525 values less than (to_date ('2017-05-26 ', 'yyyy-mm-dd ')),

Partition test_20120526 values less than (to_date ('2017-05-27 ', 'yyyy-mm-dd ')),

Partition test_20120527 values less than (to_date ('2017-05-28 ', 'yyyy-mm-dd ')),

Partition test_20120528 values less than (to_date ('2017-05-29 ', 'yyyy-mm-dd ')),

Partition test_20120529 values less than (to_date ('2017-05-30 ', 'yyyy-mm-dd ')),

Partition test_20120530 values less than (to_date ('2017-05-31 ', 'yyyy-mm-dd '))

);

Create a local index and insert data

SQL> Create index idx_local_createtime on t_test_3 (create_time) local;

Index created

SQL> insert into t_test_3 select ID, name, create_time from t_test_2;

50333 rows inserted

SQL> commit;

Commit complete

 

Delete t_test where id =: 1

Delete t_test where rowed = 'aaam3oaaeaaaaoaaa'

The execution plan is still table access by user rowid

 

Set parameters respectively

SQL & gt; Alter system set optimizer_index_caching = 100;

System altered

SQL> alter system set optimizer_index_cost_adj = 1;

System altered

The result execution plan is still table access by user rowid.

 

Execution Analysis

SQL> EXEC dbms_stats.gather_table_stats

(Ownname => 'shark', tabname => 't_ TEST_3 ', estimate_percent => 100, cascade => true );

PL/SQL procedure successfully completed

The execution plan is still TABLE ACCESS BY USER ROWID

 

Create a subpartition

SQL> update t_test_2 set id = mod (id, 10 );

50333 rows updated

SQL>CREATE TABLESHARK. t_test_4
(
Id NUMBER,
Name VARCHAR2(35 ),
Create_timeDATE
)
PARTITION BY RANGE(Create_time)
SUBPARTITION BY LIST(Id)
SUBPARTITION TEMPLATE
(SUBPARTITION""VALUES(0 ),
SUBPARTITION"B"VALUES(1 ),
SUBPARTITION"C"VALUES(2 ),
SUBPARTITION"D"VALUES(3 ),
SUBPARTITION"E"VALUES(4 ),
SUBPARTITION"F"VALUES(5 ),
SUBPARTITION"G"VALUES(6 ),
SUBPARTITION"H"VALUES(7 ),
SUBPARTITION"J"VALUES(8 ),
SUBPARTITION"K"VALUES(9)
)
(
PARTITIONTEST_20120525VALUES LESS(To_date ('1970-05-26 ', 'yyyy-mm-dd ')),
PARTITIONTEST_20120526VALUES LESS(To_date ('1970-05-27 ', 'yyyy-mm-dd ')),
PARTITIONTEST_20120527VALUES LESS(To_date ('1970-05-28 ', 'yyyy-mm-dd ')),
PARTITIONTEST_20120528VALUES LESS(To_date ('1970-05-29 ', 'yyyy-mm-dd ')),
PARTITIONTEST_20120529VALUES LESS(To_date ('1970-05-30 ', 'yyyy-mm-dd ')),
PARTITIONTEST_20120530VALUES LESS(To_date ('1970-05-31 ', 'yyyy-mm-dd '))
);

 

SQL> insert into t_test_4 select * from t_test_2;

50333 rows inserted

SQL> commit;

Commit complete

SQL> create index idx_local_createtime_test4 on t_test_4 (create_time) local;

Index created

 

SQL> exec dbms_stats.gather_table_stats

(Ownname => 'shark', tabname => 't_ test_4 ', estimate_percent => 100, cascade => true );

PL/SQL procedure successfully completed

SQL> Create index idx_local_id_test4 on t_test_4 (ID) local;

Index created

 

Unfortunately, the execution plan is still table access by user rowid.

Only the following statements use the index.

DeleteT_test_4Where id= 4

Delete

Partition range all

Partition list single

Index range scan

 

SQL> 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

Should the company's database server be 10.2.0.4? All Tables involved are partitions or subpartitions. Local indexes, not analyzed!