Oracle 用一些值的组合检索最后插入行的id，并将其存储在当前行中

我有一张表'raw_traffic大约有35列标题

身份证，
状态，
插入日期，
更改日期，
设备名称，
设备端口，
班级，
prev_身份证，
col_1，
col_2，
col_3，
。
。
。
col_N

Whenever I receive fresh traffic data, I need to find the previous row id with a specific combination of 设备名称， 设备端口， 班级， status and some other columns
The approach that we currently have is to run the following query to get the last inserted id。

SELECT MAX(id) FROM raw_traffic WHERE device_name='___', device_port='___', class='___', col_1='___', col_2='___' and status='___'
复制

After retrieving this id we are storing it in the prev_id column of the new row and doing an insert。

It is important to mention that this table at any given point of time has around half a million records and around 10 indexes (one on each of these searchable columns)。 Consequently the select and the insert operations have become intolerably slow and deteriorating by the day。

In order to improve it I was thinking of putting the retrieve last id function in a PL/SQL block or even a before_insert trigger in the db。
What would the best approach for handling such a scenario。 Moreover what should be the most efficient index strategy for such a table ?

如果要为等于其他列中的某些值的所有行找到列中的最大值，则可以在以下位置创建索引:

-你所在的所有列
-后跟您想要从中获得最大值的列

然后，数据库可以对索引进行良好的高效最小/最大扫描。

例如，下面在表中搜索前三列 = 0的所有行。并获取最后一个的最大值:

create table t as 
  select mod ( level, 2 ) c1,
         mod ( level, 4 ) c2,
         mod ( level, 8 ) c3,
         level c4
  from   dual
  connect by level <= 100;
  
create index i on t ( c1, c2, c3, c4 );
  
set serveroutput off

select /*+ gather_plan_statistics */ 
       max ( c4 )
from   t
where  c1 = 0 
and    c2 = 0 
and    c3 = 0;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                 
SQL_ID  1t8zzb3s0r1fr, child number 0                                                             
-------------------------------------                                                             
select /*+ gather_plan_statistics */         max ( c4 ) from   t where                            
c1 = 0  and    c2 = 0  and    c3 = 0                                                              
                                                                                                  
Plan hash value: 3623458378                                                                       
                                                                                                  
-----------------------------------------------------------------------------------------------   
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |   
-----------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT             |      |      1 |        |      1 |00:00:00.01 |       1 |   
|   1 |  SORT AGGREGATE              |      |      1 |      1 |      1 |00:00:00.01 |       1 |   
|   2 |   FIRST ROW                  |      |      1 |      1 |      1 |00:00:00.01 |       1 |   
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| I    |      1 |      1 |      1 |00:00:00.01 |       1 |   
-----------------------------------------------------------------------------------------------   
                                                                                                  
Predicate Information (identified by operation id):                                               
---------------------------------------------------                                               
                                                                                                  
   3 - access("C1"=0 AND "C2"=0 AND "C3"=0)  
复制

这只访问索引中的一个条目。所以是尽可能高效的。

这仅在索引列的所有前导列上都具有相等 (=) 时才有效。如果你有任何不等式 (<，> = 等)，你不会得到这个扫描。

例如，下面将c2上的谓词更改为 <1。而不是 = 0。

尽管对于这些数据的效果是相同的，但数据库会对索引进行正常范围扫描。它读取12个条目，而不是1个:

select /*+ gather_plan_statistics */ 
       max ( c4 )
from   t
where  c1 = 0 
and    c2 < 1 
and    c3 = 0;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                      
SQL_ID  4sd3havabynyu, child number 1                                                  
-------------------------------------                                                  
select /*+ gather_plan_statistics */         max ( c4 ) from   t where                 
c1 = 0  and    c2 < 1  and    c3 = 0                                                   
                                                                                       
Plan hash value: 163676535                                                             
                                                                                       
------------------------------------------------------------------------------------   
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |   
------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |       1 |   
|   1 |  SORT AGGREGATE   |      |      1 |      1 |      1 |00:00:00.01 |       1 |   
|*  2 |   INDEX RANGE SCAN| I    |      1 |     12 |     12 |00:00:00.01 |       1 |   
------------------------------------------------------------------------------------   
                                                                                       
Predicate Information (identified by operation id):                                    
---------------------------------------------------                                    
                                                                                       
   2 - access("C1"=0 AND "C3"=0 AND "C2"<1)                                            
       filter("C3"=0)  
复制

What would the best approach for handling such a scenario

不理解就很难说why您想要获取这些值的先前id。这个专栏的目的是什么？您如何计算id值？