0

I have a query that I need it to be execute fast as possible. Even though I have indexes, it is executing very slowly and I can't figure out why.

This is the query, I am trying to optimize.

SELECT d.*, t.*, 
                   ... 
 

                    FROM descriptions d
                    JOIN descriptions_items t ON d.id = t.description_id

                    LEFT JOIN facts f ON d.fact = f.id AND f.deleted_at IS NULL  

                    LEFT JOIN descriptions_entities dr ON dr.description_id = d.id
                    LEFT JOIN descriptions_reasons re ON re.description_id = d.id

                    LEFT JOIN entities r ON dr.entity_id = r.id
                    LEFT JOIN entities_containers c ON r.container_id = c.id

                    LEFT JOIN users u ON d.assigned_to = u.id AND u.deleted_at IS NULL  
                    LEFT JOIN users u2 ON d.created_by = u2.id AND u2.deleted_at IS NULL  
                    LEFT JOIN users u3 ON d.updated_by = u3.id AND u3.deleted_at IS NULL  

                    LEFT JOIN users u4 ON r.created_by = u4.id AND u4.deleted_at IS NULL  
                    LEFT JOIN users u5 ON r.updated_by = u5.id AND u5.deleted_at IS NULL  

                    LEFT JOIN users u6 ON c.created_by = u6.id AND u6.deleted_at IS NULL  
                    LEFT JOIN users u7 ON c.updated_by = u7.id AND u7.deleted_at IS NULL  

                    LEFT JOIN users u8 ON f.created_by = u8.id AND u8.deleted_at IS NULL  
                    LEFT JOIN users u9 ON f.updated_by = u9.id AND u9.deleted_at IS NULL  

                    LEFT JOIN authorities a ON u.id = a.user_id  
                    LEFT JOIN authorities a2 ON u2.id = a2.user_id  
                    LEFT JOIN authorities a3 ON u3.id = a3.user_id  

                    LEFT JOIN authorities a4 ON u4.id = a4.user_id  
                    LEFT JOIN authorities a5 ON u5.id = a5.user_id  

                    LEFT JOIN authorities a6 ON u6.id = a6.user_id  
                    LEFT JOIN authorities a7 ON u7.id = a7.user_id  

                    LEFT JOIN authorities a8 ON u8.id = a8.user_id  
                    LEFT JOIN authorities a9 ON u9.id = a9.user_id  

                    LEFT JOIN acl_sid s ON a.sid_id = s.id  
                    LEFT JOIN acl_sid s2 ON a2.sid_id = s2.id  
                    LEFT JOIN acl_sid s3 ON a3.sid_id = s3.id  

                    LEFT JOIN acl_sid s4 ON a4.sid_id = s4.id  
                    LEFT JOIN acl_sid s5 ON a5.sid_id = s5.id  

                    LEFT JOIN acl_sid s6 ON a6.sid_id = s6.id  
                    LEFT JOIN acl_sid s7 ON a7.sid_id = s7.id  

                    LEFT JOIN acl_sid s8 ON a8.sid_id = s8.id  
                    LEFT JOIN acl_sid s9 ON a9.sid_id = s9.id

                    JOIN (SELECT ROW_NUMBER() OVER (ORDER BY item_date DESC, id DESC ) AS r, id
                    FROM (SELECT t.id, t.item_date,
                    FROM descriptions_items_mv t
                    -- I do filtering in here if there is any
                    )) descriptions_table ON d.id = descriptions_table .id
                    WHERE r <= 10
                    ORDER BY t.item_date DESC, t.description_id DESC;

Here are the tables


CREATE TABLE users
(
    id                     NUMBER(20, 0)                           NOT NULL PRIMARY KEY,
    email                  VARCHAR(255)                            NOT NULL,
    ...
    created_by             NUMBER(20, 0) DEFAULT NULL              NULL,
    updated_by             NUMBER(20, 0) DEFAULT NULL              NULL,
    deleted_by             NUMBER(20, 0) DEFAULT NULL              NULL,
    CONSTRAINT fk_users_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_users_updated_by FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_users_deleted_by FOREIGN KEY (deleted_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX email_ix ON users (email);
CREATE INDEX users_created_by_idx ON users (created_by);
CREATE INDEX users_updated_by_idx ON users (updated_by);
CREATE INDEX users_deleted_by_idx ON users (deleted_by);

CREATE TABLE facts
(
    id         NUMBER(20, 0)                           NOT NULL PRIMARY KEY,
    ...
    created_by NUMBER(20, 0) DEFAULT NULL              NULL,
    updated_by NUMBER(20, 0) DEFAULT NULL              NULL,
    deleted_by NUMBER(20, 0) DEFAULT NULL              NULL,
    CONSTRAINT fk_facts_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_facts_updated_by FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_facts_deleted_by FOREIGN KEY (deleted_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX facts_created_by_idx ON facts(created_by);
CREATE INDEX facts_updated_by_idx ON facts(updated_by);
CREATE INDEX facts_deleted_at_idx ON facts(deleted_at, id);

CREATE TABLE descriptions
(
    id            NUMBER(20, 0)                           NOT NULL PRIMARY KEY,
    fact NUMBER(20, 0) DEFAULT NULL              NULL,
    ...
    assigned_to   NUMBER(20, 0) DEFAULT NULL              NULL,
    created_by    NUMBER(20, 0) DEFAULT NULL              NULL,
    updated_by    NUMBER(20, 0) DEFAULT NULL              NULL,
    CONSTRAINT fk_descriptions_fact FOREIGN KEY (fact) REFERENCES facts (id) ON DELETE SET NULL,
    CONSTRAINT fk_descriptions_assigned_to FOREIGN KEY (assigned_to) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_descriptions_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_descriptions_updated_by FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX descriptions_assigned_to_idx ON descriptions (assigned_to);
CREATE INDEX descriptions_facts_idx ON descriptions (facts);
CREATE INDEX descriptions_created_by_idx ON descriptions (created_by);
CREATE INDEX descriptions_updated_by_idx ON descriptions (updated_by);

CREATE TABLE descriptions_items
(
    description_id NUMBER(20, 0)              NOT NULL PRIMARY KEY,
    ...
    CONSTRAINT fk_items_descriptions FOREIGN KEY (description_id ) REFERENCES descriptions(id) ON DELETE CASCADE
);

CREATE INDEX descriptions_items_1_idx ON descriptions_items(__, item_date, description_id);
CREATE INDEX descriptions_items_2_idx ON descriptions_items(__, item_date, description_id);

CREATE TABLE descriptions_entities
(
    primary_key  NUMBER(20, 0) NOT NULL PRIMARY KEY,
    description_id NUMBER(20, 0) NOT NULL,
    entity_id NUMBER(20, 0) NOT NULL,
    CONSTRAINT fk_descriptions_entities_descriptions FOREIGN KEY (description_id) REFERENCES descriptions (id) ON DELETE CASCADE,
    CONSTRAINT fk_descriptions_entities_entities FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE
);

CREATE INDEX des_entity_desc_id_idx ON descriptions_entities (description_id );
CREATE INDEX dec_entity_entity_id_idx ON descriptions_entities (entity_id );

CREATE TABLE descriptions_reasons
(
    description_id NUMBER(20, 0)     NOT NULL,
    reason       CLOB DEFAULT NULL NULL,
    CONSTRAINT fk_reasons_descriptions FOREIGN KEY (description_id ) REFERENCES descriptions(id) ON DELETE CASCADE
);

CREATE INDEX des_res_description_id_idx ON descriptions_reasons(description_id );

CREATE TABLE entities
(
    id           NUMBER(20, 0)                           NOT NULL PRIMARY KEY,
    ...
    created_by   NUMBER(20, 0) DEFAULT NULL              NULL,
    updated_by   NUMBER(20, 0) DEFAULT NULL              NULL,
    deleted_by   NUMBER(20, 0) DEFAULT NULL              NULL,
    CONSTRAINT fk_entities_containers FOREIGN KEY (container_id) REFERENCES entities_containers(id) ON DELETE CASCADE,
    CONSTRAINT fk_entities_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_entities_updated_by FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_entities_deleted_by FOREIGN KEY (deleted_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX entities_container_id_idx ON entities(container_id);
CREATE INDEX entities_created_by_idx ON entities (created_by);
CREATE INDEX entities_updated_by_idx ON entities (updated_by);
CREATE INDEX entities_deleted_by_idx ON entities (deleted_by);

CREATE TABLE entity_containers
(
    id          NUMBER(20, 0)                           NOT NULL PRIMARY KEY,
    ...
    created_by  NUMBER(20, 0) DEFAULT NULL              NULL,
    updated_by  NUMBER(20, 0) DEFAULT NULL              NULL,
    deleted_by  NUMBER(20, 0) DEFAULT NULL              NULL,
    CONSTRAINT fk_entity_containers_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_entity_containers_updated_by FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_entity_containers_deleted_by FOREIGN KEY (deleted_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX entity_containers_cre_by_idx ON entity_containers(created_by);
CREATE INDEX entity_containers_upd_by_idx ON entity_containers(updated_by);
CREATE INDEX entity_containers_del_by_idx ON entity_containers(deleted_by);

CREATE TABLE acl_sid (
    id NUMBER(38) NOT NULL PRIMARY KEY,
    principal NUMBER(1) NOT NULL CHECK (principal in (0, 1)),
    internal NUMBER(1) NOT NULL CHECK (internal in (0, 1)),
    sid NVARCHAR2(100) NOT NULL,
    CONSTRAINT unique_acl_sid UNIQUE (sid, principal)
);

CREATE TABLE authorities
(
    user_id NUMBER(20, 0) NOT NULL,
    sid_id  NUMBER(38)    NOT NULL,
    CONSTRAINT fk_authorities_users FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_authorities_sids FOREIGN KEY (sid_id) REFERENCES acl_sid (id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX ix_auth_username ON authorities (user_id, sid_id);

Execution Plan of left joins

 -----------------------------------------------------------------------------------------------------------------------------------------------------
 | Id  | Operation                                                    | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT                                             |                             |    23 |   362K|       |   143  (11)| 00:00:02 |
 |   1 |  SORT ORDER BY                                               |                             |    23 |   362K|   192K|   143  (11)| 00:00:02 |
 |*  2 |   HASH JOIN                                                  |                             |    23 |   362K|       |    64  (22)| 00:00:01 |
 |*  3 |    VIEW                                                      |                             |    23 |   598 |       |     3  (34)| 00:00:01 |
 |*  4 |     WINDOW SORT PUSHED RANK                                  |                             |    23 |   598 |       |     3  (34)| 00:00:01 |
 |   5 |      MAT_VIEW ACCESS FULL                                    | DESCRIPTIONS_ITEMS_MV       |    23 |   598 |       |     2   (0)| 00:00:01 |
 |*  6 |    HASH JOIN                                                 |                             |    23 |   361K|       |    61  (22)| 00:00:01 |
 |   7 |     TABLE ACCESS FULL                                        | DESCRIPTIONS_ITEMS          |    23 | 11523 |       |     2   (0)| 00:00:01 |
 |*  8 |     HASH JOIN RIGHT OUTER                                    |                             |    23 |   350K|       |    58  (21)| 00:00:01 |
 |   9 |      TABLE ACCESS FULL                                       | ACL_SID                     |     3 |   345 |       |     2   (0)| 00:00:01 |
 |* 10 |      HASH JOIN RIGHT OUTER                                   |                             |    23 |   347K|       |    56  (22)| 00:00:01 |
 |  11 |       TABLE ACCESS FULL                                      | ACL_SID                     |     3 |   345 |       |     2   (0)| 00:00:01 |
 |* 12 |       HASH JOIN RIGHT OUTER                                  |                             |    23 |   345K|       |    53  (21)| 00:00:01 |
 |  13 |        TABLE ACCESS FULL                                     | ACL_SID                     |     3 |   345 |       |     2   (0)| 00:00:01 |
 |* 14 |        HASH JOIN RIGHT OUTER                                 |                             |    23 |   342K|       |    51  (22)| 00:00:01 |
 |  15 |         TABLE ACCESS FULL                                    | ACL_SID                     |     3 |   345 |       |     2   (0)| 00:00:01 |
 |* 16 |         HASH JOIN RIGHT OUTER                                |                             |    23 |   339K|       |    48  (21)| 00:00:01 |
 |  17 |          TABLE ACCESS FULL                                   | ACL_SID                     |     3 |   345 |       |     2   (0)| 00:00:01 |
 |* 18 |          HASH JOIN RIGHT OUTER                               |                             |    23 |   337K|       |    46  (22)| 00:00:01 |
 |  19 |           TABLE ACCESS FULL                                  | ACL_SID                     |     3 |   345 |       |     2   (0)| 00:00:01 |
 |* 20 |           HASH JOIN RIGHT OUTER                              |                             |    23 |   334K|       |    43  (21)| 00:00:01 |
 |  21 |            TABLE ACCESS FULL                                 | ACL_SID                     |     3 |   345 |       |     2   (0)| 00:00:01 |
 |* 22 |            HASH JOIN RIGHT OUTER                             |                             |    23 |   332K|       |    41  (22)| 00:00:01 |
 |  23 |             TABLE ACCESS FULL                                | ACL_SID                     |     3 |   345 |       |     2   (0)| 00:00:01 |
 |* 24 |             HASH JOIN RIGHT OUTER                            |                             |    23 |   329K|       |    38  (22)| 00:00:01 |
 |  25 |              TABLE ACCESS FULL                               | ACL_SID                     |     3 |   345 |       |     2   (0)| 00:00:01 |
 |* 26 |              HASH JOIN RIGHT OUTER                           |                             |    23 |   326K|       |    36  (23)| 00:00:01 |
 |  27 |               INDEX FULL SCAN                                | IX_AUTH_USERNAME            |     1 |    26 |       |     1   (0)| 00:00:01 |
 |* 28 |               HASH JOIN RIGHT OUTER                          |                             |    23 |   326K|       |    34  (21)| 00:00:01 |
 |  29 |                INDEX FULL SCAN                               | IX_AUTH_USERNAME            |     1 |    26 |       |     1   (0)| 00:00:01 |
 |* 30 |                HASH JOIN RIGHT OUTER                         |                             |    23 |   325K|       |    33  (22)| 00:00:01 |
 |  31 |                 INDEX FULL SCAN                              | IX_AUTH_USERNAME            |     1 |    26 |       |     1   (0)| 00:00:01 |
 |* 32 |                 HASH JOIN RIGHT OUTER                        |                             |    23 |   325K|       |    31  (20)| 00:00:01 |
 |  33 |                  INDEX FULL SCAN                             | IX_AUTH_USERNAME            |     1 |    26 |       |     1   (0)| 00:00:01 |
 |  34 |                  NESTED LOOPS OUTER                          |                             |    23 |   324K|       |    30  (20)| 00:00:01 |
 |* 35 |                   HASH JOIN RIGHT OUTER                      |                             |    23 |   311K|       |    29  (21)| 00:00:01 |
 |* 36 |                    TABLE ACCESS FULL                         | USERS                       |     1 |   601 |       |     2   (0)| 00:00:01 |
 |  37 |                    NESTED LOOPS OUTER                        |                             |    23 |   297K|       |    26  (20)| 00:00:01 |
 |* 38 |                     HASH JOIN RIGHT OUTER                    |                             |    23 |   284K|       |    25  (20)| 00:00:01 |
 |* 39 |                      TABLE ACCESS FULL                       | USERS                       |     1 |   601 |       |     2   (0)| 00:00:01 |
 |* 40 |                      HASH JOIN RIGHT OUTER                   |                             |    23 |   270K|       |    23  (22)| 00:00:01 |
 |  41 |                       TABLE ACCESS FULL                      | ENTITYCONTAINERS            |     1 |  2261 |       |     2   (0)| 00:00:01 |
 |* 42 |                       HASH JOIN RIGHT OUTER                  |                             |    23 |   219K|       |    20  (20)| 00:00:01 |
 |  43 |                        TABLE ACCESS FULL                     | ENTITIES                    |     2 |  4522 |       |     2   (0)| 00:00:01 |
 |  44 |                        NESTED LOOPS OUTER                    |                             |    23 |   169K|       |    18  (23)| 00:00:01 |
 |* 45 |                         HASH JOIN RIGHT OUTER                |                             |    23 |   168K|       |    18  (23)| 00:00:01 |
 |  46 |                          INDEX FULL SCAN                     | IX_AUTH_USERNAME            |     1 |    26 |       |     1   (0)| 00:00:01 |
 |* 47 |                          HASH JOIN RIGHT OUTER               |                             |    23 |   167K|       |    16  (19)| 00:00:01 |
 |  48 |                           INDEX FULL SCAN                    | IX_AUTH_USERNAME            |     1 |    26 |       |     1   (0)| 00:00:01 |
 |* 49 |                           HASH JOIN RIGHT OUTER              |                             |    23 |   167K|       |    15  (20)| 00:00:01 |
 |  50 |                            INDEX FULL SCAN                   | IX_AUTH_USERNAME            |     1 |    26 |       |     1   (0)| 00:00:01 |
 |* 51 |                            HASH JOIN RIGHT OUTER             |                             |    23 |   166K|       |    13  (16)| 00:00:01 |
 |  52 |                             INDEX FULL SCAN                  | IX_AUTH_USERNAME            |     1 |    26 |       |     1   (0)| 00:00:01 |
 |* 53 |                             HASH JOIN RIGHT OUTER            |                             |    23 |   166K|       |    12  (17)| 00:00:01 |
 |  54 |                              INDEX FULL SCAN                 | IX_AUTH_USERNAME            |     1 |    26 |       |     1   (0)| 00:00:01 |
 |* 55 |                              HASH JOIN RIGHT OUTER           |                             |    23 |   165K|       |    10  (10)| 00:00:01 |
 |* 56 |                               TABLE ACCESS FULL              | USERS                       |     1 |   601 |       |     2   (0)| 00:00:01 |
 |* 57 |                               HASH JOIN RIGHT OUTER          |                             |    23 |   152K|       |     8  (13)| 00:00:01 |
 |* 58 |                                TABLE ACCESS FULL             | USERS                       |     1 |   601 |       |     2   (0)| 00:00:01 |
 |  59 |                                NESTED LOOPS OUTER            |                             |    23 |   138K|       |     5   (0)| 00:00:01 |
 |  60 |                                 NESTED LOOPS OUTER           |                             |    23 |   125K|       |     4   (0)| 00:00:01 |
 |  61 |                                  NESTED LOOPS OUTER          |                             |    23 |   111K|       |     3   (0)| 00:00:01 |
 |  62 |                                   NESTED LOOPS OUTER         |                             |    23 |    98K|       |     2   (0)| 00:00:01 |
 |  63 |                                    NESTED LOOPS OUTER        |                             |    23 | 54096 |       |     2   (0)| 00:00:01 |
 |  64 |                                     TABLE ACCESS FULL        | DESCRIPTIONS                |    23 | 49335 |       |     2   (0)| 00:00:01 |
 |  65 | WID                                 TABLE ACCESS BY INDEX RO | FACTS                       |     1 |   207 |       |     0   (0)| 00:00:01 |
 |* 66 |                                      INDEX RANGE SCAN        | FACTS_DELETED_AT_IDX         |     1 |       |       |     0   (0)| 00:00:01 |
 |  67 | ID                                 TABLE ACCESS BY INDEX ROW | DESCRIPTIONS_REASONS        |     1 |  2015 |       |     0   (0)| 00:00:01 |
 |* 68 |                                     INDEX RANGE SCAN         | DEC_RES_DESCRIPTION_ID_IDX  |     1 |       |       |     0   (0)| 00:00:01 |
 |* 69 | D                                 TABLE ACCESS BY INDEX ROWI | USERS                       |     1 |   601 |       |     1   (0)| 00:00:01 |
 |* 70 |                                    INDEX UNIQUE SCAN         | SYS_C007033                 |     1 |       |       |     0   (0)| 00:00:01 |
 |* 71 |                                  TABLE ACCESS BY INDEX ROWID | USERS                       |     1 |   601 |       |     1   (0)| 00:00:01 |
 |* 72 |                                   INDEX UNIQUE SCAN          | SYS_C007033                 |     1 |       |       |     0   (0)| 00:00:01 |
 |* 73 |                                 TABLE ACCESS BY INDEX ROWID  | USERS                       |     1 |   601 |       |     1   (0)| 00:00:01 |
 |* 74 |                                  INDEX UNIQUE SCAN           | SYS_C007033                 |     1 |       |       |     0   (0)| 00:00:01 |
 |  75 |                         TABLE ACCESS BY INDEX ROWID          | DESCRIPTIONS_ENTITIES       |     1 |    26 |       |     0   (0)| 00:00:01 |
 |* 76 |                          INDEX RANGE SCAN                    | DEC_ENTITY_DESC_ID_IDX      |     2 |       |       |     0   (0)| 00:00:01 |
 |* 77 |                     TABLE ACCESS BY INDEX ROWID              | USERS                       |     1 |   601 |       |     1   (0)| 00:00:01 |
 |* 78 |                      INDEX UNIQUE SCAN                       | SYS_C007033                 |     1 |       |       |     0   (0)| 00:00:01 |
 |* 79 |                   TABLE ACCESS BY INDEX ROWID                | USERS                       |     1 |   601 |       |     1   (0)| 00:00:01 |
 |* 80 |                    INDEX UNIQUE SCAN                         | SYS_C007033                 |     1 |       |       |     0   (0)| 00:00:01 |

In some cases it doesn't use indexes which can be seen in execution plan. Why is this? Are my indexes wrong?

14
  • Define "slowly". Does the cardinality of the Plan reflect reality? Do you actually need ALL those columns? Are there any LOB columns? Nov 18 '21 at 12:57
  • The query executes around 3.5 seconds. Cardinality reflects reality. Yes I need most of the columns. Nov 18 '21 at 13:02
  • 1
    Expected speed might unrealistic for the SQL as written. If Oracle throws IO requests serially, that's 30+ separate IO requests (1 per table) each with potential 15ms access time == 450ms. Let me work on answer for HOWTO fix. Nov 18 '21 at 15:37
  • 1
    The explain doesn't seem to agree with the 3.5 seconds. You're sure the 3.5 seconds isn't the time needed to transfer the results to the client? Nov 18 '21 at 17:29
  • 1
    I've worked on a lot of batch systems where indexes are mostly a hazard, so my first thought is that indexes would only slow things down if you want all the rows for all 35 joins with no selective filters, and I am a bit surprised the optimiser is choosing them at all. But maybe that's because you don't use most of them so it only needs to do existence checks. But then I'm wondering why it even needs to existence-check FK parents when (assuming the FKs are validated) it already knows the parent row exists. A 35-way join is a lot to take in though so I could be missing something. Nov 18 '21 at 23:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.