I have one query in main menu that uses DB link to computert stats and slowing down the navigation to main menu.
I am thinking of creating an MV for main menu and storing derived numbers there and judt do simple select without
running all calculations real time.
WOuld this be a complex MV that needs to be refreshed on demand (i.e nightly)?
SELECT count(*) into l_title_cnt from books@bkp_link
where (status_flag='W' and contract_no is not null and batch_date is not null) or (status_flag-'S' and shipment_status='F');
I am also thinking of running a job whenever a shipment takes place so that the count stored in MV gets updated.
The reason i want to schedule a job is that sometime you cant update the number stored in the MV by adding or subtracting to it and I had to complete refresh or create a second OVERRIDE table to select the updated number from.