All geek questions in one place

Ordering for two columns in a three-column composite index - Oracle

I have a composite index in three columns in one of my tables. It works if I have three columns at the end of my query. When my search query has only two out of three, everything doesn't look so fast!

Do you know how to get around this?

Thanks,

There

PS table

APPL_PERF_STATS

has a composite index on (current_appl_id, event_name and generic_method_name) This took 2 minutes (using all the columns in the composite index):

SELECT * FROM APPL_PERF_STATS WHERE (GENERIC_METHOD_NAME! = 'NULL' AND CURRENT_APPL_ID! = 'NULL' AND EVENT_NAME! = 'NULL') AND ROWNUM <502 And current_appl_id = 'OMS' And event_name = 'OMS-CeaseProduct AND CallForwardFixedCOProduct 'AND appl_perf_interval_typ_id = 1440 And cover_period_start_ts> = to_date ('20 -07-2008 14:36', 'dd-mm-yyyy HH24: mi') AND cover_period_start_ts <= to_date ('19 -08-2009 14: 36 ',' dd-mm-yyyy HH24: mi ') ORDER BY CURRENT_APPL_ID, EVENT_NAME, GENERIC_METHOD_NAME, CREATE_TS

This took 12 minutes to run (using only 2 of the three in the composite index):

SELECT * FROM APPL_PERF_STATS (GENERIC_METHOD_NAME!= 'NULL' AND CURRENT_APPL_ID!= 'NULL' EVENT_NAME!= 'NULL') ROWNUM < 502 current_appl_id = 'OMS' event_name = 'OMS-CeaseProduct' AND appl_perf_interval_typ_id = 1440 cover_period_start_ts >= to_date ('20 -07-2008 14:36 ​​',' dd-mm-yyyy HH24: mi ') AND cover_period_start_ts <= to_date ('19 -08-2009 14:36 ​​',' dd-mm-yyyy HH24: mi ') ORDER BY CURRENT_APPL_ID, EVENT_NAME, GENERIC_METHOD_NAME, CREATE_TS

+3
2
  • , ? Oracle , . (column_1, column_2, column_3) column_1 column_2, . , ( ) , column_2 column_3, .
  • Oracle ? Oracle Oracle " " , () . , , , .
  • , , ? , , .
+3

" , !" - ?

", " , 50 , 5 . "" , 500 , .

: , , / .

, cover_period_start_ts / appl_perf_interval_typ_id , . , , , , , .

PS. , , != 'NULL' where, , access_predicate filter_predicate

+3

Source: https://habr.com/ru/post/1715663/

More articles:


All Articles