pv_system.name column is issued from sys_def table
Actually there is no index on the "name" column of of sys_def table.
so a query with a where clause will scan all the table. And a query with with a "where like 'z%' will scan n times the table , n is the number of match of 'z%' in sys_def table.
Hopefully that helps.
Thank you Arif for your reply.
I was indeed researching to find out what tables/fields were indexed before posting my question. Since "name" is not indexed and the query times out, its just a matter of too large a table for this query to process efficiently? I guess I will have to break this down into different smaller defined queries then.
If your objective is to report file system utilisation for a specific day I would be more inclined to use the pv_sys_data_d view rather than the details view.
In the original query I suspect that using '%z' changes the sql optimiser path due to a larger number of potential matches, meaning that the date comparisons are performed first, which supports my theory that your date selection is sub-optimal and results in date conversions being applied to a massive number of rows.
WHERE to_char(pv_sys_data_detail.ts,'HH24:MI') = '23:00'
AND to_char(pv_sys_data_detail.ts,'MM-DD-YYYY') BETWEEN '10-30-2019' and '10-31-2019'
AND METRIC = 'BYFS_USED_SPACE_PCT'
AND pv_system.name like 'z%'
Extracting samples with a specific timestamp should be much faster as follows as no conversion is required on the actual data row :-
and pv_sys_data_detail.ts = to_date('30-OCT-2020 23:00:00','DD-MON-YYYY HH24:MI:SS')
Hope this helps...
Paul, thank you very much for the reply. This was the solution. Both going against the pv_sys_data_d instead of _detail, and using the specific date without conversion, worked like a charm. Every iteration going back for months completed 100% of the time and took less than a minute.