Why SQL query is running very slow in Oracle Database?-CodePudding

I have a table with about 18 million records. I have to read the whole table in one query because all of the records were created on one date. So, firstly I defined an index on four columns that I have a query on them using the below command:

CREATE INDEX test_etl_index ON test_table(t_date,c_num,obc,bu);

After creating indexes, I must use this query for every columns of c_num,obc,bu. The query is below:

    SELECT t_date,
       c_num,
       pd,
       pds,
       uc,
       obc,
       t_id,
       da,
       ca,
       db,
       t_time,
       ibc,
       lc,
       lt,
       sts,
       wd,
       bu
FROM   test_table
WHERE  t_date = '20170628'
       AND c_num IN (SELECT KEY
                     FROM   c_g
                     WHERE  g_id = 1);

But, for each column, the query takes about 8 minutes which is very very slow!

Would you please guide me on how I can change the query to get better performance? Any help would be appreciated.

CodePudding user response：

Close to no advice can be done based on the information you provided.

Except - as commended - to fix the data type for the DATE column, as the storage of dates as strings can realy confuse the optimizer.

The expected setup dependes on your data, here are some hints.

The DATE colums is Selective

In case your predicate date='20170628' (or better col_date = date'2017-06-28) returns only very few records you'll profite from an index on this column.

create index test_table_idx on test_table(col_date);

You can expect an execution plan as follows

-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |     4 |   472 |     5   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI                      |                |     4 |   472 |     5   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TABLE     |    10 |  1120 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | TEST_TABLE_IDX |    10 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL                  | C_G            |     3 |    18 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("C_NUM"="KEY")
   3 - access("COL_DATE"=TO_DATE(' 2002-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   4 - filter("G_ID"=1)

Note that Oracle rewrite your in (subquery) in a hash join semi, so no manual query rewrite is required.

C_NUM is Selective

If on the contrary the predicate c_num in (... returns very few records, define an index on c_num column.

create index test_table_idx2 on test_table(c_num);

You can expect an execution plan as follows

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     4 |   480 |    28   (4)| 00:00:01 |
|   1 |  NESTED LOOPS                |                 |     4 |   480 |    28   (4)| 00:00:01 |
|   2 |   NESTED LOOPS               |                 |    20 |   480 |    28   (4)| 00:00:01 |
|   3 |    SORT UNIQUE               |                 |     3 |    18 |     3   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL        | C_G             |     3 |    18 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | TEST_TABLE_IDX2 |    10 |       |     2   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID| TEST_TABLE      |     1 |   114 |    12   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter("G_ID"=1)
   5 - access("C_NUM"="KEY")
   6 - filter("COL_DATE"=TO_DATE(' 2000-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

No Selectivity

If neither of the above hold, forgot indexes and you should see an HASH JOIN SEMI that should not take as much time on a 18M table

-----------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            | 27273 |  3142K|  4516   (1)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI|            | 27273 |  3142K|  4516   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | C_G        |     3 |    18 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL  | TEST_TABLE | 90909 |  9943K|  4512   (1)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("C_NUM"="KEY")
   2 - filter("G_ID"=1)
   3 - filter("COL_DATE"=TO_DATE(' 2000-01-02 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss'))

The important point is learn how to get the execution plan of the query, how to read it and how to understand where is the bottelneck.

CodePudding user response：

create index on date and c_num columns only rather than (date,c_num,obc,bu) Or create another index on date and c_num and call this idx2