Btukfyl

Big table

record (

    id number primary key,



    city_id number not null,

    organization_id number not null,

    department_id number not null, -- extra context, can be 0



    renew_date date not null -- frequently updated

)

Two kind of query are used:

1.

WITH cte (city_id, organization_id, -- etc...) AS (

    -- table join routine...

)

SELECT r.*

FROM record r

INNER JOIN cte t ON r.city_id = t.city_id AND r.organization_id = t.organization_id AND r.department_id = 0

WHERE -- some condition on renew_date... whatever

2.

WITH cte (city_id, organization_id, department_id, -- etc...) AS (

    -- same

)

SELECT r.*

FROM record r

INNER JOIN cte t ON r.city_id = t.city_id AND r.organization_id = t.organization_id AND r.department_id = t.department_id

WHERE -- some condition on renew_date or smth else

There are rumors that partial multicolumn (compozite) index can be emulated in Oracle.
https://community.oracle.com/ideas/18213

https://blog.jooq.org/2017/01/18/how-to-emulate-partial-indexes-in-oracle/

Do i need to create a pair of some kind of hash function

CREATE OR REPLACE FUNCTION get_record_index (city_id IN NUMBER, organization_id IN NUMBER, department_id IN NUMBER)

    RETURN NUMBER

    DETERMINISTIC

AS

BEGIN

    IF department_id <> 0 THEN RETURN NULL

    ELSE RETURN no_idea_how_to_compute(city_id, organization_id);

    END IF;

END;



CREATE OR REPLACE FUNCTION get_record_department_index (city_id IN NUMBER, organization_id IN NUMBER, department_id IN NUMBER)

    RETURN NUMBER

    DETERMINISTIC

AS

BEGIN

    IF department_id = 0 THEN RETURN NULL

    ELSE RETURN no_idea_how_to_compute2(city_id, organization_id, department_id);

    END IF;

END;

and create two function based index?

upd:
I need smth like that

CREATE INDEX record_main_index       ON record (rayon_id, organization_id) WHERE department_id = 0;



CREATE INDEX record_department_index ON record (rayon_id, organization_id, department_id) WHERE department_id <> 0;

You could add a virtual column to the table and then include that column in an index:

ALTER TABLE record ADD zero_dept_id AS (CASE department_id WHEN 0 THEN 0 END);



CREATE INDEX record_paritial_idx ON record (city_id, organization_id, zero_dept_id);

On second thought you may be wanting only the single column index below instead of the multi-column index above.

CREATE INDEX record_paritial_idx ON record (zero_dept_id);

Then in your code instead of using r.department_id = 0 you would use r.zero_dept_id = 0 to use the new index.

No guarantees that this will improve your query performance, but you can certainly give it a try.

For a multi column index that truly prunes out all the non zero department_id entries you may need more virtual columns:

ALTER TABLE record ADD zero_dept_id AS (CASE department_id WHEN 0 THEN 0 END);

ALTER TABLE record ADD zero_dept_city_id AS (CASE department_id WHEN 0 THEN city_id END);

ALTER TABLE record ADD zero_dept_org_id AS (CASE department_id WHEN 0 THEN organization_id END);



CREATE INDEX record_paritial_idx ON record (zero_dept_city_id, zero_dept_org_id, zero_dept_id);

Then in your code make the appropriate substitutions to get this:

r.zero_dept_city_id = t.city_id AND r.zero_dept_org_id = t.organization_id AND r.zero_dept_id = 0

In the last option above, you could possibly drop the zero_dept_id column since the other two virtual columns will only have values in the index when the department id is zero in which case the index would become this:

CREATE INDEX record_paritial_idx ON record (zero_dept_city_id, zero_dept_org_id);

and the query predicate would be:

r.zero_dept_city_id = t.city_id AND r.zero_dept_org_id = t.organization_id

with the department_id = 0 predicate implied by the zero_dept* virtual columns.