{"id":14739,"date":"2020-09-24T10:07:23","date_gmt":"2020-09-24T08:07:23","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-dml-delete-and-the-index-clustering-factor\/"},"modified":"2020-09-24T10:07:23","modified_gmt":"2020-09-24T08:07:23","slug":"oracle-dml-delete-and-the-index-clustering-factor","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-dml-delete-and-the-index-clustering-factor\/","title":{"rendered":"Oracle DML (DELETE) and the Index Clustering Factor"},"content":{"rendered":"

As a consultant working for customers, I’m often in the situation that I have an answer to a problem, but the recommended solution cannot be implemented due to some restrictions. E.g. the recommendation would be to adjust the code, but that is not feasible. In such cases you are forced to try to help without code changes. <\/p>\n

Recently I was confronted with the following issue: A process takes too long. Digging deeper I could see that most of the time was spent on this SQL:<\/p>\n

\nDELETE FROM COM_TAB WHERE 1=1 \n<\/pre>\n
The execution plan looked as follows:<\/p>\n
\n--------------------------------------------------------------------------------------------\n| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     |\n--------------------------------------------------------------------------------------------\n|   0 | DELETE STATEMENT      |                    |       |       | 16126 (100)|          |\n|   1 |  DELETE               | COM_TAB            |       |       |            |          |\n|   2 |   INDEX FAST FULL SCAN| PK_COM_TAB         |    10M|   306M| 16126   (1)| 00:00:01 |\n--------------------------------------------------------------------------------------------\n<\/pre>\n
My initial reaction was of course to say that deleting all data in a table with a delete statement is not a good idea. Better is to turn the DML into DDL and use e.g. “truncate table”. All options for deleting lots of rows in a table fast are provided by Chris Saxon in his Blog here<\/a>.<\/p>\n
In this case changing the SQL was not possible, so what are the alternatives?<\/p>\n
As I was involved in this a long time after the issue happened I checked the ASH of AWR-History:<\/p>\n
\nSQL> select SQL_EXEC_START, session_state, event, count(*)*10 secs_in_state FROM dba_hist_active_sess_history where sql_id='53gwjb0gjn1np'\n  2  group by sql_exec_start, session_state, event order by 1,4 desc;\n\nSQL_EXEC_START      SESSION EVENT                                                            SECS_IN_STATE\n------------------- ------- ---------------------------------------------------------------- -------------\n19.06.2020 10:13:02 WAITING free buffer waits                                                          560\n19.06.2020 10:13:02 WAITING enq: CR - block range reuse ckpt                                           370\n19.06.2020 10:13:02 ON CPU                                                                             130\n19.06.2020 10:13:02 WAITING reliable message                                                            10\n19.06.2020 10:56:01 WAITING enq: CR - block range reuse ckpt                                           550\n19.06.2020 10:56:01 WAITING free buffer waits                                                          230\n19.06.2020 10:56:01 ON CPU                                                                             140\n19.06.2020 10:56:01 WAITING log file switch (checkpoint incomplete)                                     60\n19.06.2020 11:39:38 WAITING enq: CR - block range reuse ckpt                                           610\n19.06.2020 11:39:38 WAITING free buffer waits                                                          180\n19.06.2020 11:39:38 ON CPU                                                                             170\n19.06.2020 11:39:38 WAITING log file switch (checkpoint incomplete)                                     80\n19.06.2020 11:39:38 WAITING write complete waits                                                        40\n19.06.2020 12:23:47 WAITING enq: CR - block range reuse ckpt                                           450\n19.06.2020 12:23:47 WAITING free buffer waits                                                          280\n19.06.2020 12:23:47 ON CPU                                                                             150\n19.06.2020 12:23:47 WAITING log file switch (checkpoint incomplete)                                     90\n19.06.2020 12:23:47 WAITING write complete waits                                                        30\n19.06.2020 12:23:47 WAITING log buffer space                                                            10\n<\/pre>\n
So obviously the DBWR had a problem writing dirty blocks to disk and getting free space in the cache. When the issue happened above the following parameter were active:<\/p>\n
\nfilesystemio_options='ASYNCH'\n<\/pre>\n
Changing it to<\/p>\n
\nfilesystemio_options='SETALL'\n<\/pre>\n
improved the situation a lot, but caused waits on “db file sequential read”.<\/p>\n
I.e. with filesystemio_options=’ASYNCH’ we do cache lots of repeatedly touched blocks in the filesystem cache, but suffer from slower (non-direct) writes by the DB-writer. With filesystemio_options=’SETALL’ we gain by doing direct IO by the DB-writer, but have to read repeatedly touched blocks from disk more often.<\/p>\n
The table just had 1 index, the index for the primary key.<\/p>\n
So what to do here?<\/p>\n
Several recommendations came to mind:<\/p>\n
– With filesystemio_options=’ASYNCH’: Increase the redologs to not do a checkpoint while the statement is running
\n– With filesystemio_options=’SETALL’: Increase the buffer cache to keep blocks in memory for longer and avoid single block IOs<\/p>\n
The most interesting question was: Why is the optimizer deciding to go over the index here first? With a bad clustering factor it would make more sense to do a full table scan than to use the index. And this has actually been validated with a hint:<\/p>\n
\nDELETE \/*+ FULL(COM_TAB) *\/ FROM COM_TAB WHERE 1=1\n<\/pre>\n
improved the situation. <\/p>\n
An improvement should be achievable by using an Index Organized Table here as we only have a primary key index on the table, so that we just wipe out the data in the index and do not have to visit the same table block repeatedly again. The best however is to create a testcase and reproduce the issue. Here’s what I did:<\/p>\n
I created 2 tables <\/p>\n
TDEL_GOOD_CF
\nTDEL_BAD_CF<\/p>\n
which do have more blocks than I have in the db-cache. As the name suggests one table had an index with a better clustering factor and one an index with a bad clustering factor:<\/p>\n
\nSQL> select table_name, blocks, num_rows from tabs where table_name like 'TDEL_%';\n\nTABLE_NAME                           BLOCKS   NUM_ROWS\n-------------------------------- ---------- ----------\nTDEL_BAD_CF                          249280     544040\nTDEL_GOOD_CF                         248063     544040\n<\/pre>\n
Remark: To use lots of blocks I stored only 2 rows per block by using a high PCTFREE.<\/p>\n
\nSQL> select index_name, leaf_blocks, clustering_factor from ind where table_name like 'TDEL_%';\n\nINDEX_NAME                       LEAF_BLOCKS CLUSTERING_FACTOR\n-------------------------------- ----------- -----------------\nPK_TDEL_BAD_CF                          1135            532313\nPK_TDEL_GOOD_CF                         1135            247906\n<\/pre>\n
The database cache size was much smaller than the blocks in the table:<\/p>\n
\nSQL> select bytes\/8192 BLOCKS_IN_BUFFER_CACHE from v$sgastat where name='buffer_cache';\n\nBLOCKS_IN_BUFFER_CACHE\n----------------------\n                 77824\n<\/pre>\n
Test with filesystemio_options=’SETALL’:<\/p>\n
\nSQL> set autotrace trace timing on\nSQL> delete from TDEL_BAD_CF where 1=1;\n\n544040 rows deleted.\n\nElapsed: 00:01:08.76\n\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 2076794500\n\n----------------------------------------------------------------------------------------\n| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |\n----------------------------------------------------------------------------------------\n|   0 | DELETE STATEMENT      |                |   544K|  2656K|   315\t (2)| 00:00:01 |\n|   1 |  DELETE               | TDEL_BAD_CF    |       |       |            |          |\n|   2 |   INDEX FAST FULL SCAN| PK_TDEL_BAD_CF |   544K|  2656K|   315\t (2)| 00:00:01 |\n----------------------------------------------------------------------------------------\n\n\nStatistics\n----------------------------------------------------------\n         88  recursive calls\n    2500712  db block gets\n       1267  consistent gets\n     477213  physical reads\n  388185816  redo size\n        195  bytes sent via SQL*Net to client\n        384  bytes received via SQL*Net from client\n          1  SQL*Net roundtrips to\/from client\n          7  sorts (memory)\n          0  sorts (disk)\n     544040  rows processed\n<\/pre>\n
Please consider the 477213 physical reads (blocks read), i.e. almost 2 times the number of blocks in the table.
\nThe ASH-data looked as follows:<\/p>\n
\nselect sql_id, sql_plan_line_id, session_state, event, p1, count(*)\nfrom v$active_session_history\nwhere sql_id='ck5fw78yqh93g'\ngroup by sql_id,sql_plan_line_id, session_state, event, p1\norder by 6;\n\n\nSQL_ID\t      SQL_PLAN_LINE_ID SESSION EVENT                                    P1   COUNT(*)\n------------- ---------------- ------- -------------------------------- ---------- ----------\nck5fw78yqh93g                1 WAITING db file scattered read                    7          1\nck5fw78yqh93g                1 ON CPU                                            7         11\nck5fw78yqh93g                1 WAITING db file sequential read                   7         56\n<\/pre>\n
P1 is the file_id when doing IO. File ID 7 is the USERS-Tablepspace where my table and index are in.<\/p>\n
So obviously Oracle didn’t consider the clustering factor when building the plan with the index. The cost of 315 is just the cost for the INDEX FAST FULL SCAN:<\/p>\n
Fast Full Index Scan Cost ~ ((LEAF_BLOCKS\/MBRC) x MREADTIM)\/ SREADTIM + CPU<\/p>\n
REMARK: I do not have system statistics gathered.<\/p>\n
LEAF_BLOCKS=1135
\nMBRC=8
\nMREADTIM=26ms
\nSREADTIM=12ms<\/p>\n
Fast Index Scan Cost ~ ((1135\/8) x 26)\/ 12 + CPU = 307 + CPU = 315<\/p>\n
The costs for accessing the table are not considered at all. I.e. going through the index and from there to the table to delete the rows results in visiting the same table block several times.<\/p>\n
Here the test with the table having a better clustering factor on the index:<\/p>\n
\nSQL> delete from TDEL_GOOD_CF where 1=1;\n\n544040 rows deleted.\n\nElapsed: 00:00:30.48\n\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 4284904063\n\n-----------------------------------------------------------------------------------------\n| Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |\n-----------------------------------------------------------------------------------------\n|   0 | DELETE STATEMENT      |                 |   544K|  2656K|   315   (2)| 00:00:01 |\n|   1 |  DELETE               | TDEL_GOOD_CF    |       |       |            |          |\n|   2 |   INDEX FAST FULL SCAN| PK_TDEL_GOOD_CF |   544K|  2656K|   315   (2)| 00:00:01 |\n-----------------------------------------------------------------------------------------\n\n\nStatistics\n----------------------------------------------------------\n        115  recursive calls\n    2505121  db block gets\n       1311  consistent gets\n     249812  physical reads\n  411603188  redo size\n        195  bytes sent via SQL*Net to client\n        385  bytes received via SQL*Net from client\n          1  SQL*Net roundtrips to\/from client\n          9  sorts (memory)\n          0  sorts (disk)\n     544040  rows processed\n\n\nselect sql_id, sql_plan_line_id, session_state, event, p1, count(*)\nfrom v$active_session_history\nwhere sql_id='0nqk3fmcwrrzm'\ngroup by sql_id,sql_plan_line_id, session_state, event, p1\norder by 6;\n\nSQL_ID\t      SQL_PLAN_LINE_ID SESSION EVENT                                    P1   COUNT(*)\n------------- ---------------- ------- -------------------------------- ---------- ----------\n0nqk3fmcwrrzm                1 ON CPU                                            7          3\n0nqk3fmcwrrzm                1 WAITING db file sequential read                   7         26\n<\/pre>\n
I.e. it did run much faster with the better clustering factor and only had to do half the physical reads.\u00a0<\/p>\n
Here the test with the full table scan on the table with the index having a bad clustering factor:<\/p>\n
\ncbleile@orcl@orcl> delete \/*+ FULL(T) *\/ from TDEL_BAD_CF T where 1=1;\n\n544040 rows deleted.\n\nElapsed: 00:00:08.39\n\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 4058645893\n\n----------------------------------------------------------------------------------\n| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |\n----------------------------------------------------------------------------------\n|   0 | DELETE STATEMENT   |             |   544K|  2656K| 67670   (1)| 00:00:01 |\n|   1 |  DELETE            | TDEL_BAD_CF |       |       |            |          |\n|   2 |   TABLE ACCESS FULL| TDEL_BAD_CF |   544K|  2656K| 67670   (1)| 00:00:01 |\n----------------------------------------------------------------------------------\n\n\nStatistics\n----------------------------------------------------------\n        161  recursive calls\n    1940687  db block gets\n     248764  consistent gets\n     252879  physical reads\n  269882276  redo size\n        195  bytes sent via SQL*Net to client\n        401  bytes received via SQL*Net from client\n          1  SQL*Net roundtrips to\/from client\n          7  sorts (memory)\n          0  sorts (disk)\n     544040  rows processed\n\nselect sql_id, sql_plan_line_id, session_state, event, p1, count(*)\nfrom v$active_session_history\nwhere sql_id='4272c7xv86d0k'\ngroup by sql_id,sql_plan_line_id, session_state, event, p1\norder by 6;\n\nSQL_ID\t      SQL_PLAN_LINE_ID SESSION EVENT                                    P1   COUNT(*)\n------------- ---------------- ------- -------------------------------- ---------- ----------\n4272c7xv86d0k                2 ON CPU                                            7          2\n4272c7xv86d0k                1 ON CPU                                            7          3\n4272c7xv86d0k                2 WAITING db file scattered read                    7          3\n<\/pre>\n
I.e. if Oracle would consider the clustering factor here and do the delete with the full table scan then it would obviously run much faster.<\/p>\n
Last test with an IOT:<\/p>\n
\ncbleile@orcl@orcl> delete from TDEL_IOT where 1=1;\n\n544040 rows deleted.\n\nElapsed: 00:00:06.90\n\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 515699456\n\n-------------------------------------------------------------------------------------------\n| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time             |\n-------------------------------------------------------------------------------------------\n|   0 | DELETE STATEMENT      |                   |   544K|  2656K| 66065   (1)| 00:00:01 |\n|   1 |  DELETE               | TDEL_IOT          |       |       |            |          |\n|   2 |   INDEX FAST FULL SCAN| SYS_IOT_TOP_77456 |   544K|  2656K| 66065   (1)| 00:00:01 |\n-------------------------------------------------------------------------------------------\n\n\nStatistics\n----------------------------------------------------------\n        144  recursive calls\n     521556  db block gets\n     243200  consistent gets\n     243732  physical reads\n  241686612  redo size\n        194  bytes sent via SQL*Net to client\n        381  bytes received via SQL*Net from client\n          1  SQL*Net roundtrips to\/from client\n          7  sorts (memory)\n          0  sorts (disk)\n     544040  rows processed\n\nselect sql_id, sql_plan_line_id, session_state, event, p1, count(*)\nfrom v$active_session_history\nwhere sql_id='cf6nj64yybkpq'\ngroup by sql_id,sql_plan_line_id, session_state, event, p1\norder by 6;\n\nSQL_ID\t      SQL_PLAN_LINE_ID SESSION EVENT                                    P1   COUNT(*)\n------------- ---------------- ------- -------------------------------- ---------- ----------\ncf6nj64yybkpq                2 ON CPU                                            7          1\ncf6nj64yybkpq                1 ON CPU                                            7          1\ncf6nj64yybkpq                2 WAITING db file scattered read                    7          3\n<\/pre>\n
As we were not allowed to adjust the code or replace the table with an IOT the measures to improve this situation were to
\n– set filesystemio_options=’SETALL’
\n  REMARK: That change needs good testing as it may have negative effects on other SQL, which gain from the filesystem cache.
\n– add a hint with a SQL-Patch to force a full table scan<\/p>\n
REMARK: Creating a SQL-Patch to add the hint <\/p>\n
\nFULL(TDEL_BAD_CF)\n<\/pre>\n
to the statement was not easily possible, because Oracle does not consider this hint in DML:<\/p>\n
\nvar rv varchar2(32);\ndeclare\n   v_sql CLOB;\nbegin\n   select sql_text into v_sql from dba_hist_sqltext where sql_id='ck5fw78yqh93g';\n   :rv:=dbms_sqldiag.create_sql_patch(\n             sql_text  => v_sql,\n             hint_text=>'FULL(TDEL_BAD_CF)',\n             name=>'force_fts_when_del_all',\n             description=>'force fts when del all rows');\nend;\n\/\nprint rv\n\ndelete from TDEL_BAD_CF where 1=1;\n\n544040 rows deleted.\n\nElapsed: 00:01:01.79\n\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 2076794500\n\n----------------------------------------------------------------------------------------\n| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |\n----------------------------------------------------------------------------------------\n|   0 | DELETE STATEMENT      |                |   544K|  2656K|   315\t (2)| 00:00:01 |\n|   1 |  DELETE               | TDEL_BAD_CF    |       |       |            |          |\n|   2 |   INDEX FAST FULL SCAN| PK_TDEL_BAD_CF |   544K|  2656K|   315\t (2)| 00:00:01 |\n----------------------------------------------------------------------------------------\n\nHint Report (identified by operation id \/ Query Block Name \/ Object Alias):\nTotal hints for statement: 1 (N - Unresolved (1))\n---------------------------------------------------------------------------\n\n   1 -\tDEL$1\n\t N -  FULL(TDEL_BAD_CF)\n\nNote\n-----\n   - SQL patch \"force_fts_when_del_all\" used for this statement\n<\/pre>\n
I.e. according the Note the SQL patch was used, but the Hint report showed it to be “Unresolved”.<\/p>\n
So I had to use the full hint specification:<\/p>\n
\nFULL(@\"DEL$1\" \"TDEL_BAD_CF\"@\"DEL$1\")\n<\/pre>\n
To get this full specification you can do an explain plan of the hinted statement and look at the outline data:<\/p>\n
\nSQL> explain plan for\n  2  delete \/*+ FULL(TDEL_BAD_CF) *\/ from TDEL_BAD_CF where 1=1;\n\nExplained.\n\nSQL> select * from table(dbms_xplan.display(format=>'+OUTLINE'));\n\n...\n\nOutline Data\n-------------\n\n  \/*+\n      BEGIN_OUTLINE_DATA\n      FULL(@\"DEL$1\" \"TDEL_BAD_CF\"@\"DEL$1\")\n      OUTLINE_LEAF(@\"DEL$1\")\n      ALL_ROWS\n      DB_VERSION('19.1.0')\n      OPTIMIZER_FEATURES_ENABLE('19.1.0')\n      IGNORE_OPTIM_EMBEDDED_HINTS\n      END_OUTLINE_DATA\n  *\/\n<\/pre>\n
So here’s the script to create the SQL Patch correctly:<\/p>\n
\nvar rv varchar2(32);\ndeclare\n   v_sql CLOB;\nbegin\n   select sql_text into v_sql from dba_hist_sqltext where sql_id='ck5fw78yqh93g';\n   :rv:=dbms_sqldiag.create_sql_patch(\n             sql_text  => v_sql,\n             hint_text=>'FULL(@\"DEL$1\" \"TDEL_BAD_CF\"@\"DEL$1\")',\n             name=>'force_fts_when_del_all',\n             description=>'force fts when del all rows');\nend;\n\/\nprint rv\n\nSQL> delete from TDEL_BAD_CF where 1=1;\n\n544040 rows deleted.\n\nElapsed: 00:00:06.57\n\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 4058645893\n\n----------------------------------------------------------------------------------\n| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time\t |\n----------------------------------------------------------------------------------\n|   0 | DELETE STATEMENT   |             |   544K|  2656K| 67670   (1)| 00:00:01 |\n|   1 |  DELETE            | TDEL_BAD_CF |       |       |            |          |\n|   2 |   TABLE ACCESS FULL| TDEL_BAD_CF |   544K|  2656K| 67670   (1)| 00:00:01 |\n----------------------------------------------------------------------------------\n\nNote\n-----\n   - SQL patch \"force_fts_when_del_all\" used for this statement\n\nStatistics\n----------------------------------------------------------\n        207  recursive calls\n    1940517  db block gets\n     248759  consistent gets\n     252817  physical reads\n  272061432  redo size\n        195  bytes sent via SQL*Net to client\n        384  bytes received via SQL*Net from client\n          1  SQL*Net roundtrips to\/from client\n         16  sorts (memory)\n          0  sorts (disk)\n     544040  rows processed\n<\/pre>\n
Summary: This is a specific corner case where the Oracle optimizer should consider the clustering factor in DML when calculating plan costs but it doesn’t. The workaround in this case was to hint the statement or add a SQL-Patch to hint the statement without modifying it in the code.<\/p>\n","protected":false},"excerpt":{"rendered":"
As a consultant working for customers, I’m often in the situation that I have an answer to a problem, but the recommended solution cannot be implemented due to some restrictions. E.g. the recommendation would be to adjust the code, but that is not feasible. In such cases you are forced to try to help without […]<\/p>\n","protected":false},"author":35,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[59],"tags":[2123,605,96,2124,2125],"type_dbi":[],"class_list":["post-14739","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-dml","tag-hint","tag-oracle","tag-plan","tag-slow"],"acf":[],"yoast_head":"\nOracle DML (DELETE) and the Index Clustering Factor - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/oracle-dml-delete-and-the-index-clustering-factor\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle DML (DELETE) and the Index Clustering Factor\" \/>\n<meta property=\"og:description\" content=\"As a consultant working for customers, I’m often in the situation that I have an answer to a problem, but the recommended solution cannot be implemented due to some restrictions. E.g. the recommendation would be to adjust the code, but that is not feasible. In such cases you are forced to try to help without […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-dml-delete-and-the-index-clustering-factor\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-09-24T08:07:23+00:00\" \/>\n<meta name=\"author\" content=\"Clemens Bleile\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@ifgtxD2SrQ8r!YuXj\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Clemens Bleile\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"11 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-dml-delete-and-the-index-clustering-factor\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-dml-delete-and-the-index-clustering-factor\\\/\"},\"author\":{\"name\":\"Clemens Bleile\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0ac04011f60f2e93c115358d0789c2da\"},\"headline\":\"Oracle DML (DELETE) and the Index Clustering Factor\",\"datePublished\":\"2020-09-24T08:07:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-dml-delete-and-the-index-clustering-factor\\\/\"},\"wordCount\":963,\"commentCount\":0,\"keywords\":[\"dml\",\"hint\",\"Oracle\",\"plan\",\"slow\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-dml-delete-and-the-index-clustering-factor\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-dml-delete-and-the-index-clustering-factor\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-dml-delete-and-the-index-clustering-factor\\\/\",\"name\":\"Oracle DML (DELETE) and the Index Clustering Factor - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2020-09-24T08:07:23+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0ac04011f60f2e93c115358d0789c2da\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-dml-delete-and-the-index-clustering-factor\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-dml-delete-and-the-index-clustering-factor\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-dml-delete-and-the-index-clustering-factor\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle DML (DELETE) and the Index Clustering Factor\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0ac04011f60f2e93c115358d0789c2da\",\"name\":\"Clemens Bleile\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"caption\":\"Clemens Bleile\"},\"description\":\"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.\",\"sameAs\":[\"https:\\\/\\\/www.dbi-services.com\",\"https:\\\/\\\/x.com\\\/ifgtxD2SrQ8r!YuXj\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/clemens-bleile\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle DML (DELETE) and the Index Clustering Factor - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/oracle-dml-delete-and-the-index-clustering-factor\/","og_locale":"en_US","og_type":"article","og_title":"Oracle DML (DELETE) and the Index Clustering Factor","og_description":"As a consultant working for customers, I’m often in the situation that I have an answer to a problem, but the recommended solution cannot be implemented due to some restrictions. E.g. the recommendation would be to adjust the code, but that is not feasible. In such cases you are forced to try to help without […]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-dml-delete-and-the-index-clustering-factor\/","og_site_name":"dbi Blog","article_published_time":"2020-09-24T08:07:23+00:00","author":"Clemens Bleile","twitter_card":"summary_large_image","twitter_creator":"@ifgtxD2SrQ8r!YuXj","twitter_misc":{"Written by":"Clemens Bleile","Est. reading time":"11 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-dml-delete-and-the-index-clustering-factor\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-dml-delete-and-the-index-clustering-factor\/"},"author":{"name":"Clemens Bleile","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"headline":"Oracle DML (DELETE) and the Index Clustering Factor","datePublished":"2020-09-24T08:07:23+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-dml-delete-and-the-index-clustering-factor\/"},"wordCount":963,"commentCount":0,"keywords":["dml","hint","Oracle","plan","slow"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-dml-delete-and-the-index-clustering-factor\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-dml-delete-and-the-index-clustering-factor\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-dml-delete-and-the-index-clustering-factor\/","name":"Oracle DML (DELETE) and the Index Clustering Factor - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-09-24T08:07:23+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-dml-delete-and-the-index-clustering-factor\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-dml-delete-and-the-index-clustering-factor\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-dml-delete-and-the-index-clustering-factor\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle DML (DELETE) and the Index Clustering Factor"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da","name":"Clemens Bleile","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","caption":"Clemens Bleile"},"description":"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.","sameAs":["https:\/\/www.dbi-services.com","https:\/\/x.com\/ifgtxD2SrQ8r!YuXj"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/clemens-bleile\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14739","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/35"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=14739"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14739\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=14739"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=14739"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=14739"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=14739"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}