Sql query is running very slow in Oracle database-CodePudding

I have an Oracle table with about 18 milion records. I have to read the whole table in one query because all of the records create in one date . So, firstly I define index on four columns that I have a query on them using this command:

CREATE INDEX test_etl_index ON test_table(date,c_num,obc,bu)

After creating index, I must use this query for every columns of c_num,obc,bu. The query is in the following:

Select date,c_num,pd,pds,uc,obc,t_id,da,ca,db,time,ibc,lc,lt,STS,wd,bu
from test_table
where date='20170628' 
and c_num in (select key from c_g where g_id=1)

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

Would you please guide me how I can change the query to get better performance?

Any help is really appreciated.

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

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.