2 1mil tables join takes 2 sec
I am trying to speed up the following SQL
1 SELECT
2 count(1)
3 FROM
4 smsg.SVC_MSG_CHAN SMC
5 JOIN smsg.SVC_MSG SM ON SM.SVC_MSG_ID = SMC.SVC_MSG_ID
6 AND
7 SM.EFF_DT <= SYSDATE
8 AND
9 SMC.EFF_DT <= SYSDATE
Both tables have 1mil - 1.3 mil rows and the SQL completes in 2-3 seconds which to me seems to be too much.
SVC_MSG_ID is the first column in the primary key in both of the tables. Oracle uses a hash join to join the tables
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1295K| 98M| | 10341 (3)| 00:02:05 |
2 count(1)
3 FROM
4 smsg.SVC_MSG_CHAN SMC
5 JOIN smsg.SVC_MSG SM ON SM.SVC_MSG_ID = SMC.SVC_MSG_ID
6 AND
7 SM.EFF_DT <= SYSDATE
8 AND
9 SMC.EFF_DT <= SYSDATE
Both tables have 1mil - 1.3 mil rows and the SQL completes in 2-3 seconds which to me seems to be too much.
SVC_MSG_ID is the first column in the primary key in both of the tables. Oracle uses a hash join to join the tables
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1295K| 98M| | 10341 (3)| 00:02:05 |
0