I am running a query in some big tables, and although it runs fine even tough is a lot of data, I'd like to understand what part of it weighs on the execution. Unfortunately I am not too good with explain plans so I call for help.
Here is some data about these tables:
history_state_table7.424.65 rows (of which only 13.412 are left after
t1.alarm_type = 'AT1')
This would be the query (don't mind the logic, is just for the reference):
SELECT t1.id_alarm, t2.load_id, t2.reference_date FROM history_state_table t1, (SELECT op_code, contract_num, COUNT (DISTINCT id_ponto) AS num_pontos, COUNT (DISTINCT CASE WHEN vlr > 0 THEN id_ponto ELSE NULL END ) AS bigger_than_zero, MAX (load_id) AS load_id, MAX (reference_date) AS reference_date FROM costumer_price_history WHERE load_id IN (42232, 42234, 42236, 42238, 42240, 42242, 42244) /* arbitrary IDs depending on execution*/ AND sistema = 'F1' /* Hardcoded filters */ AND rec_type = 'F3' /* Hardcoded filters */ AND description = 'F3' /* Hardcoded filters */ AND extract_type IN ('T1', 'T2', 'T3') GROUP BY op_code, contract_num) t2 WHERE t1.op_code = t2.op_code AND t1.contract_num = t2.contract_num AND t1.alarm_type = 'AT1' AND t1.alarm_status = 'DONE' AND ( ( t1.prod_type = 'COMBO' AND t2.bigger_than_zero = t2.num_pontos - 1 ) OR ( t1.prod_type != 'COMBO' AND t2.bigger_than_zero = t2.num_pontos ) ) /* arbitrary filter depending on execution*/ AND t1.data_tratado BETWEEN (SELECT data_inicio FROM cycle_table WHERE id_ciclo = 160) AND (SELECT data_fim FROM cycle_table WHERE id_ciclo = 160)
And finally the explain plan:
Plan SELECT STATEMENT ALL_ROWSCost: 5,485 13 NESTED LOOPS 7 NESTED LOOPS Cost: 5,483 Bytes: 115 Cardinality: 1 5 VIEW Cost: 12 Bytes: 59 Cardinality: 1 4 SORT GROUP BY Cost: 12 Bytes: 85 Cardinality: 1 3 INLIST ITERATOR 2 TABLE ACCESS BY INDEX ROWID TABLE RAIDPIDAT.COSTUMER_PRICE_HISTORY Cost: 11 Bytes: 85 Cardinality: 1 1 INDEX RANGE SCAN INDEX RAIDPIDAT.IDX_COSTUMER_PRICE_HISTORY_2 Cost: 10 Cardinality: 3 6 INDEX RANGE SCAN INDEX RAIDPIDAT.IDX_HISTORY_STATE_TABLE_1TPALM Cost: 662 Cardinality: 102,068 12 TABLE ACCESS BY INDEX ROWID TABLE RAIDPIDAT.HISTORY_STATE_TABLE Cost: 5,471 Bytes: 56 Cardinality: 1 9 TABLE ACCESS BY INDEX ROWID TABLE RAIDPIDAT.CYCLE_TABLE Cost: 1 Bytes: 12 Cardinality: 1 8 INDEX UNIQUE SCAN INDEX (UNIQUE) RAIDPIDAT.PK_CYCLE_TABLE Cost: 0 Cardinality: 1 11 TABLE ACCESS BY INDEX ROWID TABLE RAIDPIDAT.CYCLE_TABLE Cost: 1 Bytes: 12 Cardinality: 1 10 INDEX UNIQUE SCAN INDEX (UNIQUE) RAIDPIDAT.PK_CYCLE_TABLE Cost: 0 Cardinality: 1
Mind that I am not asking "how to rewrite it more efficiently", but rather how do I find witht the explain plan what the most costly operation there. Meanwhile I am reading about it, but I'd appreciate some help.