Select query in Oracle taking a long time despite low cost and low cardinality

Question

Trying to execute a select query which basically turns multiple values within a column into multiple rows using regexp -

select  INCIDENTID, trim(regexp_substr(CASUALFACTORS,'[^,]+', 1, level) ) value  
FROM PSA.HSERS_INCIDENT_PSA WHERE ACTIVE_FLAG='1'
AND ISDELETED = 'F'
connect by regexp_substr(CASUALFACTORS, '[^,]+', 1, level) is not null
order by INCIDENTID

The query has a cost of 3206 and cardinality of 1,08,849 in the explain plan.

Still it takes a long time to execute(goes on even after 30 mins).

Please suggest.

Earlier thought of creating this SQL using an XML table, that was even more costly -

SELECT INCIDENTID,
       COLUMN_VALUE AS CASUALFACTORS
FROM   (
         SELECT INCIDENTID,
                CASUALFACTORS AS STR 
         FROM   PSA.HSERS_INCIDENT_PSA
         WHERE  ACTIVE_FLAG='1'
         AND    ISDELETED = 'F' 
       ) T,
       XMLTABLE ( ('"' || REPLACE (str, ',', '","') || '"'))


Sample data -

      select incidentid, dbms_lob.substr(CASUALFACTORS,3000)
   from PSA.HSERS_INCIDENT_PSA WHERE ACTIVE_FLAG='1'
AND ISDELETED = 'F' and incidentid = 526849

526849  8,7,26

There are no duplicates for incidentid column in the table 

DDL for the table -

ALTER TABLE PSA.HSERS_INCIDENT_PSA
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSA.HSERS_INCIDENT_PSA CASCADE CONSTRAINTS;

CREATE TABLE PSA.HSERS_INCIDENT_PSA
(
  INCIDENTID                      INTEGER,
  INCIDENTTYPE                    INTEGER,
  SUPPLEMENTALINCIDENTTYPE        INTEGER,
  PENDINGREVIEW                   VARCHAR2(1 BYTE),
  NEARMISSTYPE                    INTEGER,
  NEARMISSSUBCONTRACTOR           INTEGER,
  RELEASEDATE                     TIMESTAMP(6),
  NEARMISSIDENTIFICATION          INTEGER,
  NEARMISSCATEGORY                INTEGER,
  PROFITCENTER                    VARCHAR2(10 BYTE),
  INCIDENTNUMBER                  NUMBER(19),
  INCIDENTCODE                    VARCHAR2(15 BYTE),
  HIRNUMBER                       VARCHAR2(8 BYTE),
  REDBORDERALERTSENT              VARCHAR2(1 BYTE),
  TASKORDER                       VARCHAR2(3 BYTE),
  LOGCAPAREA                      INTEGER,
  TEAMCONNECTNUMBER               VARCHAR2(20 BYTE),
  INCIDENTDATE                    TIMESTAMP(6),
  INCIDENTTIME                    VARCHAR2(10 BYTE),
  REPORTDATE                      TIMESTAMP(6),
  REPORTTIME                      VARCHAR2(5 BYTE),
  INSERTDATETIME                  TIMESTAMP(6),
  REPORTEDBY                      VARCHAR2(40 BYTE),
  SUPERVISOR1                     VARCHAR2(40 BYTE),
  SUPERVISOR2                     VARCHAR2(40 BYTE),
  CLIENT                          INTEGER,
  PROJECTLOCATION                 INTEGER,
  INCIDENTAREA                    INTEGER,
  INCIDENTLOCATION                INTEGER,
  INCIDENTAREADESCRIPTION         VARCHAR2(80 BYTE),
  DRUGALCOHOLTEST                 INTEGER,
  NODRUGTESTRESPONSE              INTEGER,
  NODRUGTESTCOMMENTS              CLOB,
  FACTS                           CLOB,
  POTENTIALCONSEQUENCES           INTEGER,
  LIKELIHOODRATING                INTEGER,
  RISKASSESSMENTSEVERITY          INTEGER,
  COVEREDBYTSTI_JSA               VARCHAR2(1 BYTE),
  INJURINGEVENTDISCUSSEDTSTI_JSA  VARCHAR2(1 BYTE),
  EXTERNALASSESSMENTFINEASSESED   NUMBER(15,2),
  REGULATORYAGENCY                INTEGER,
  INSPECTIONNUMBER                VARCHAR2(12 BYTE),
  INSPECTIONRESULTS               INTEGER,
  INSPECTIONCLOSEDATE             TIMESTAMP(6),
  EXTERNALASSESSMENTSEVERITY      INTEGER,
  CORRECTIVEMEASURES              CLOB,
  WITNESSDETAILS                  CLOB,
  EXTERNALINVESTIGATORCOMMENTS    CLOB,
  CREATEDBY                       VARCHAR2(50 BYTE),
  CREATEDON                       TIMESTAMP(6),
  UPDATEDBY                       VARCHAR2(50 BYTE),
  UPDATEDON                       TIMESTAMP(6),
  ISDELETED                       VARCHAR2(1 BYTE),
  INCIDENTSTATUS                  VARCHAR2(50 BYTE),
  PARENTINCIDENTID                NUMBER(19),
  KEYSTOLIFE                      CLOB,
  OFFICEBEHAVIORS                 CLOB,
  CASUALFACTORS                   CLOB,
  STANDARDSVIOLATED               CLOB,
  SUBCONTRACTORADDRESS            CLOB,
  CONFIDENTIALFACTS               CLOB,
  LASTVISITEDTAB                  VARCHAR2(50 BYTE),
  LAST_RQST_ID                    VARCHAR2(60 BYTE),
  DATA_SYS_SK                     VARCHAR2(3 BYTE),
  CREATED_BY                      VARCHAR2(50 BYTE),
  CREATED_DDTM                    DATE,
  LAST_MODIFIED_BY                VARCHAR2(50 BYTE),
  LAST_MODIFIED_DDTM              DATE,
  ACTIVE_FLAG                     VARCHAR2(1 BYTE)
)
LOB (NODRUGTESTCOMMENTS) STORE AS (
  TABLESPACE  PSA_DATA
  ENABLE      STORAGE IN ROW
  CHUNK       16384
  RETENTION
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          80K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 ))
LOB (FACTS) STORE AS (
  TABLESPACE  PSA_DATA
  ENABLE      STORAGE IN ROW
  CHUNK       16384
  RETENTION
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          80K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 ))
LOB (CORRECTIVEMEASURES) STORE AS (
  TABLESPACE  PSA_DATA
  ENABLE      STORAGE IN ROW
  CHUNK       16384
  RETENTION
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          80K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 ))
LOB (WITNESSDETAILS) STORE AS (
  TABLESPACE  PSA_DATA
  ENABLE      STORAGE IN ROW
  CHUNK       16384
  RETENTION
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          80K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 ))
LOB (EXTERNALINVESTIGATORCOMMENTS) STORE AS (
  TABLESPACE  PSA_DATA
  ENABLE      STORAGE IN ROW
  CHUNK       16384
  RETENTION
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          80K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 ))
LOB (KEYSTOLIFE) STORE AS (
  TABLESPACE  PSA_DATA
  ENABLE      STORAGE IN ROW
  CHUNK       16384
  RETENTION
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          80K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 ))
LOB (OFFICEBEHAVIORS) STORE AS (
  TABLESPACE  PSA_DATA
  ENABLE      STORAGE IN ROW
  CHUNK       16384
  RETENTION
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          80K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 ))
LOB (CASUALFACTORS) STORE AS (
  TABLESPACE  PSA_DATA
  ENABLE      STORAGE IN ROW
  CHUNK       16384
  RETENTION
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          80K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 ))
LOB (STANDARDSVIOLATED) STORE AS (
  TABLESPACE  PSA_DATA
  ENABLE      STORAGE IN ROW
  CHUNK       16384
  RETENTION
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          80K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 ))
LOB (SUBCONTRACTORADDRESS) STORE AS (
  TABLESPACE  PSA_DATA
  ENABLE      STORAGE IN ROW
  CHUNK       16384
  RETENTION
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          80K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 ))
LOB (CONFIDENTIALFACTS) STORE AS (
  TABLESPACE  PSA_DATA
  ENABLE      STORAGE IN ROW
  CHUNK       16384
  RETENTION
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          80K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 ))
TABLESPACE PSA_DATA
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


--  There is no statement for index PSA.SYS_C002335882.
--  The object is created when the parent object is created.

CREATE OR REPLACE SYNONYM BI_REPORTS_USER.HSERS_INCIDENT_PSA FOR PSA.HSERS_INCIDENT_PSA;


ALTER TABLE PSA.HSERS_INCIDENT_PSA ADD (
  PRIMARY KEY
  (INCIDENTID, LAST_RQST_ID)
  USING INDEX
    TABLESPACE PSA_DATA
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          80K
                NEXT             1M
                MAXSIZE          UNLIMITED
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
  ENABLE VALIDATE);

Please [edit] your question to add some sample data. The first query you have given cannot correlate between rows in different levels of the hierarchy so if there are multiple input rows it will create exponentially more duplicate rows as the level of the hierarchy increases; this is why it is important to know what you are inputting as a single row will "work" but multiple rows will potentially create millions of duplicate rows that you are not expecting. — MT0, Sep 03 '19 at 09:24
Oracle performance tuning is tricky because there are so many factors in play. Please read [this answer in another SO thread](https://stackoverflow.com/a/34975420/146325) which explains the information we need in order to answer your question. It may also give you sufficient cluses about tuning that you can solve it for yourself. — APC, Sep 03 '19 at 09:25

score 3 · Accepted Answer · answered Sep 03 '19 at 09:34

You can use a recursive sub-query factoring clause and simple string functions (rather than slow regular expressions):

Oracle Setup:

CREATE TABLE HSERS_INCIDENT_PSA ( incidentid, casualfactors, active_flag, isdeleted ) AS
  SELECT 1, 'a,b,c,d,e,f', '1', 'F' FROM DUAL UNION ALL
  SELECT 2, 'g,h,i,j,k',   '1', 'F' FROM DUAL UNION ALL
  SELECT 3, 'l',           '1', 'F' FROM DUAL;

Query:

WITH casualfactors_bounds ( incidentid, casualfactors, startidx, endidx ) AS (
  SELECT incidentid,
         casualfactors,
         1,
         INSTR( casualfactors, ',', 1 )
  FROM   HSERS_INCIDENT_PSA
  WHERE  ACTIVE_FLAG = '1'
  AND    ISDELETED   = 'F'
UNION ALL
  SELECT incidentid,
         casualfactors,
         endidx + 1,
         INSTR( casualfactors, ',', endidx + 1 )
  FROM   casualfactors_bounds
  WHERE  endidx > 0
)
SELECT incidentid,
       CASE
       WHEN endidx = 0
       THEN SUBSTR( casualfactors, startidx )
       ELSE SUBSTR( casualfactors, startidx, endidx - startidx )
       END AS casualfactor
FROM   casualfactors_bounds
ORDER BY incidentid, startidx

Output:

INCIDENTID | CASUALFACTOR
---------: | :-----------
         1 | a           
         1 | b           
         1 | c           
         1 | d           
         1 | e           
         1 | f           
         2 | g           
         2 | h           
         2 | i           
         2 | j           
         2 | k           
         3 | l

Explain Plan:

| PLAN_TABLE_OUTPUT                                                                                                 |
| :---------------------------------------------------------------------------------------------------------------- |
| Plan hash value: 2740663158                                                                                       |
|                                                                                                                   |
| ----------------------------------------------------------------------------------------------------------------- |
| | Id  | Operation                                  | Name               | Rows  | Bytes | Cost (%CPU)| Time     | |
| ----------------------------------------------------------------------------------------------------------------- |
| |   0 | SELECT STATEMENT                           |                    |     6 |   276 |     7  (15)| 00:00:01 | |
| |   1 |  SORT ORDER BY                             |                    |     6 |   276 |     7  (15)| 00:00:01 | |
| |   2 |   VIEW                                     |                    |     6 |   276 |     6   (0)| 00:00:01 | |
| |   3 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST|                    |       |       |            |          | |
| |*  4 |     TABLE ACCESS FULL                      | HSERS_INCIDENT_PSA |     3 |    78 |     3   (0)| 00:00:01 | |
| |*  5 |     RECURSIVE WITH PUMP                    |                    |       |       |            |          | |
| ----------------------------------------------------------------------------------------------------------------- |
|                                                                                                                   |
| Predicate Information (identified by operation id):                                                               |
| ---------------------------------------------------                                                               |
|                                                                                                                   |
|    4 - filter("ACTIVE_FLAG"='1' AND "ISDELETED"='F')                                                              |
|    5 - filter("ENDIDX">0)                                                                                         |
|                                                                                                                   |
| Note                                                                                                              |
| -----                                                                                                             |
|    - dynamic sampling used for this statement (level=2)                                                           |

db<>fiddle here

Thank you very much for the help! the query returns rows in a matter of 10 secs! — Vasudevan, Sep 03 '19 at 09:57

score 1 · Answer 2 · answered Sep 03 '19 at 09:32

Does FROM PSA.HSERS_INCIDENT_PSA WHERE ACTIVE_FLAG='1' AND ISDELETED = 'F' return more than one row? If so the CONNECT BY clause will generate a Cartesian product of all the rows. That will blow your cardinality and lead to extreme run times.

You can avoid this with a simple trick:

select  INCIDENTID, trim(regexp_substr(hip.CASUALFACTORS,'[^,]+', 1, level) ) value  
FROM PSA.HSERS_INCIDENT_PSA hip
WHERE hip.ACTIVE_FLAG='1'
AND   hip.ISDELETED = 'F'
connect by regexp_substr(CASUALFACTORS, '[^,]+', 1, level) is not null
            and hip.rowid = prior hip.rowid
            and prior sys_guid() is not null
order by hip.INCIDENTID

Thank you! The query is still taking time to fetch the results(more than 15 mins and still executing), I guess I am doing something wrong from my end. — Vasudevan, Sep 03 '19 at 09:59

score 0 · Answer 3 · answered Sep 03 '19 at 11:48

If the database is generating a bad plan it may be because the statistics on this table are out of date. You can execute SELECT LAST_ANALYZED FROM ALL_TABLES WHERE OWNER = 'PSA' AND TABLE_NAME = 'HSERS_INCIDENT_PSA' to find out when statistics were last gathered on this table, but you might just as well go ahead and gather statistics by using

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'PSA',
                                TABNAME => 'HSERS_INCIDENT_PSA',
                                CASCADE => TRUE);
END;

Then re-evaluate the plan.

Select query in Oracle taking a long time despite low cost and low cardinality

3 Answers3