Topic: To construct view on a star
The show-window storing in numerical indexes some id - links to reference manuals is constructed. The show-window - big, now the order of 12 million records, after implementation will quickly grow. The customer initially asked a show-window with text boxes, my initiative to store and create in the form of a star. Reference manuals small, from 1000 records to 30 000.
Decided to create view - a sheaf. I play with and I try to kill some hares.
1) It would be desirable, that first two records returned quickly.
2) If a show-window will use for the full scanning and count of aggregates, it would be desirable that speed did not pump up.
Normal first_rows solves the first task and does access to all tables through nested loops. At first the big table, and for each read line nested loop under remaining tables is read. Somewhere there are indexes, somewhere are not present. Accordingly, tries to use them. It will seems to me for the full scanning such access long.
That was to achieve, but it does not turn out in any way. The server reads all reference manuals in storage, creating a hash function on connected fields. Then reads the big table and on a hash finds lines from all reference manuals. If tables two (big and small) - it turns out. More - in any way.
Impart experience. Whether it is real? And as you arrive in such standard situation.
And still. On the tool I Use PL/SQL developer - there are questions on scanning explain_plan operating request. It seems to me, the operating request not always goes under that plan which it beforehand shows. Whether there is a possibility to scan the plan of operating session.